Import 1 record to many tables

[Graeme at Raptup]

Hi,
I have a set-up where I import an Excel spreadsheet (usually only 1 record)
into a temporary table that is appended to one table (Artwork approval) in my
database.
That table is linked to a table called 'Company' in a many (Artwork
approval) to one relationship. All is fine.
Now we want to import this data into a number of tables. The reason for this
is that the data comes from a form. Previously the form allowed one Retailer
only. The new form is to have up to 3 Retailers each. I therefore see (at
least) 3 different entities:
Company: CompanyID, Company name
Product: ProID, Manufacturer, Style, Size, Description
Approval: ApprovalID, RetailerID, Retailer name, units, price, in-store-date

One Company has many Products that can be approved for many Retailers.

Is this possible?

Can the data perhaps be imported into a query?

Any help appreciated as always.

 

[Damian S]

Hi Graeme,

Of course you can import data into multiple tables assuming you have the
data available. Simply write a piece of code (or import spec or query or
whatever) to import the data into your first table, another for the second
table and so on and so forth...

Is there a specific issue you are having?

Damian.

 

[Jeff Boyce]

Graeme

A couple observations (see below in-line comments)...

Hi,
I have a set-up where I import an Excel spreadsheet (usually only 1
record)
into a temporary table that is appended to one table (Artwork approval) in
my
database.
That table is linked to a table called 'Company' in a many (Artwork
approval) to one relationship. All is fine.

I'm not quite visualizing the relationship.

Now we want to import this data into a number of tables.

Are you saying you wish to import the same data into multiple tables? This
would not be a very good idea in a well-normalized relational database.
Please provide more information about how/why you believe the same data
belongs in more than one table.

The reason for this
is that the data comes from a form. Previously the form allowed one
Retailer
only. The new form is to have up to 3 Retailers each. I therefore see (at
least) 3 different entities:
Company: CompanyID, Company name
Product: ProID, Manufacturer, Style, Size, Description
Approval: ApprovalID, RetailerID, Retailer name, units, price,
in-store-date

Again, I'm having trouble visualizing how three Retailers relate to Company,
Product and Approval.

One Company has many Products that can be approved for many Retailers.

Is this possible?

What are the relationships among Company, Products, Retailers, and
Approvals?

Can the data perhaps be imported into a query?

In Access, queries display (i.e., find) data. You wouldn't "import data
into a query".

Any help appreciated as always.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Graeme at Raptup]

Hi Jeff,
please see my comments below yours.......

Graeme

A couple observations (see below in-line comments)...



I'm not quite visualizing the relationship.

The Company table has a primary key as 'CompanyID' that exists as the
relationship in the table 'Artwork Approval' (Many approvals to one company).
The data that is imported from the spreadsheet holds the 'CompanyID' reference

Are you saying you wish to import the same data into multiple tables? This
would not be a very good idea in a well-normalized relational database.
Please provide more information about how/why you believe the same data
belongs in more than one table.

Well, this is what I am trying to get my head around. Until now, each
'Artwork approval' has only had 1 retailer (which was just a field within
that table). Now we want each approval (derived from the spreadsheet) to
allow for 3 retailers and each retailer has 'units' and 'price' fields.
What I was thinking was to create an additional table that would then allow
for additional retailers.

I suppose another way is simply to create additional fields such as
'Retailer2', 'Units2' and 'Price2'. But that makes for a messy query when I
want to see what the totals are by retailer?!

Again, I'm having trouble visualizing how three Retailers relate to Company,
Product and Approval.

In practical terms what is happening here is that a company has to provide
artwork and a sales forecast for a particular item (say t-shirts). They
submit an approval that includes their own details (Company), details of the
product item (Product) and then the forecast details (Approval) that could be
a different number of units and price to each retail group.

What are the relationships among Company, Products, Retailers, and
Approvals?

In my 'proposed' table layout the Retailers are located in the 'Approvals'
table. Sorry, I have not fully worked this out yet so that was confusing. In
my mind I see these as all being potential entities.

In Access, queries display (i.e., find) data. You wouldn't "import data
into a query".

Ok, but what about Damians suggestion? As I am importing into a temporary
table and then appending from there, is it possible to append to different
tables?

Thanks for the help.

 

[Jeff Boyce]

Comments in-line with comments on comments <g>

Hi Jeff,
please see my comments below yours.......



The Company table has a primary key as 'CompanyID' that exists as the
relationship in the table 'Artwork Approval' (Many approvals to one
company).
The data that is imported from the spreadsheet holds the 'CompanyID'
reference


Well, this is what I am trying to get my head around. Until now, each
'Artwork approval' has only had 1 retailer (which was just a field within
that table). Now we want each approval (derived from the spreadsheet) to
allow for 3 retailers and each retailer has 'units' and 'price' fields.
What I was thinking was to create an additional table that would then
allow
for additional retailers.

It sounds as if you are describing a one-to-many relationship (one Artwork
can have one or more Retailer). That requires a table that holds valid
combinations of Artwork and Retailer. If a particular Artwork can have two
Retailers, this new table has two records to hold that info.

I suppose another way is simply to create additional fields such as
'Retailer2', 'Units2' and 'Price2'. But that makes for a messy query when
I
want to see what the totals are by retailer?!

If you simply add more fields, you have ... a spreadsheet again! Look at
the relationships and use relational tables accordingly.

I see a need for more tables. Where's your Retailer table? Why put
RetailerName in multiple lines, when you only need the RetailerID?

In practical terms what is happening here is that a company has to provide
artwork and a sales forecast for a particular item (say t-shirts). They
submit an approval that includes their own details (Company), details of
the
product item (Product) and then the forecast details (Approval) that could
be
a different number of units and price to each retail group.

I'm not following... the Company submits an "Approval" with details, and an
"Approval" with forecast details?
So the relationship is one Company can submit multiple "Approvals"?

Whoa?! "approved for many Retailers"?

Please consider describing the relationships as follows (feel free to
correct my example...):

One Company has many Products.
One Company can submit multiple "Approval"s per Product.
One Product can be provided by many Companies.
One Approval is only submitted by one Company.
One Approval only applies to one Product.
One Product is available to many Retailers.
One Retailer can obtain many Products.

In my 'proposed' table layout the Retailers are located in the 'Approvals'
table. Sorry, I have not fully worked this out yet so that was confusing.
In
my mind I see these as all being potential entities.

Until you understand the underlying data relationships, it doesn't make much
sense to be building tables.

Ok, but what about Damians suggestion? As I am importing into a temporary
table and then appending from there, is it possible to append to different
tables?

You can build multple queries, each of which read the import table and
append (portions thereof) to different Access tables.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Graeme at Raptup]

Thanks Jeff,
I'm going to edit out some of the previous correspondence to make this
easier to read.
I think the easiest is if I insert the fields on the spreadsheet (Approval)
that are imported into Access.

CompanyID Company Age Approve
ArtGuide Category Character Date InstoreDate Phone ProdDescription Retailer SellingPrice Units

This is as we currently import the spreadsheet.

This data is imported to a table called Approvals.

So the relationship is One Company to Many Approvals.
There was no requirement (at the time) to set up a separate table for
Retailer.
But now they want to (a) run queries on Retailer and (b) allow many
Retailers per Approval.
If we were not considering the imported spreadsheet my table set up would
now change to include Retailer (With a RetailerID primary key). The
relationship would be one Approval to many Retailers.
The introduction of a 'Product' was really speculative. Whilst not a
requirement I can see that it could be a future requirement. (We can probably
ignore for this exercise)

In summary, I don't have a problem setting up the relationships between the
tables, without having to factor in this import issue.
My real problem lies in the spreadsheet import. The data comes from a single
form where we want to have an option to include up to 3 retailers (and
respective units + price). The form generates a single row of data so my
question is what do I do with the field names (and how do we pull the data
into the database)?

It sounds as if you are describing a one-to-many relationship (one Artwork
can have one or more Retailer). That requires a table that holds valid
combinations of Artwork and Retailer. If a particular Artwork can have two
Retailers, this new table has two records to hold that info.

That would be correct. Previously we did not need any detail on the Reatiler
and as such it was ok just to have it as a text field.

If you simply add more fields, you have ... a spreadsheet again! Look at
the relationships and use relational tables accordingly.

Yes! Well, I was thinking that it could make the importing process simpler!

I see a need for more tables. Where's your Retailer table? Why put
RetailerName in multiple lines, when you only need the RetailerID?

From a plain database design view, this clearly makes the most sense (i.e.
setting up a Retailer table)

I'm not following... the Company submits an "Approval" with details, and an
"Approval" with forecast details?
So the relationship is one Company can submit multiple "Approvals"?

Yes, each Company can submit many approvals.

 

[Jeff Boyce]

Graeme

<snipped for brevity...>

The way you get CompanyID Retailer1, and CompanyID Retailer2, and CompanyID
Retailer3 each parsed into a table as rows is to run three queries. You
could decide to create a UNION query (which just consists of multiple
queries) to gather any/all Retailers, then use that query as the source for
appending to your more permanent tables. (see snipped portion below)

 

[Graeme at Raptup]

Thanks Jeff,
sorry - made a bit of a meal of that.
But that solution seems to work.