If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Import 1 record to many tables
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. |
#2
|
|||
|
|||
Import 1 record to many tables
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. "Graeme at Raptup" wrote: 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. |
#3
|
|||
|
|||
Import 1 record to many tables
Graeme
A couple observations (see below in-line comments)... "Graeme at Raptup" wrote in message news 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 |
#4
|
|||
|
|||
Import 1 record to many tables
Hi Jeff,
please see my comments below yours....... "Jeff Boyce" wrote: Graeme A couple observations (see below in-line comments)... "Graeme at Raptup" wrote in message news 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. 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 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. 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?! 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. 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. 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? 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. 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". 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. Any help appreciated as always. Good luck! Regards Jeff Boyce Microsoft Office/Access MVP |
#5
|
|||
|
|||
Import 1 record to many tables
Comments in-line with comments on comments g
"Graeme at Raptup" wrote in message ... Hi Jeff, please see my comments below yours....... "Jeff Boyce" wrote: Graeme A couple observations (see below in-line comments)... "Graeme at Raptup" wrote in message news 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. 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 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. 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. 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 I see a need for more tables. Where's your Retailer table? Why put RetailerName in multiple lines, when you only need the RetailerID? 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. 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"? One Company has many Products that can be approved for many Retailers. Is this possible? 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. 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. Until you understand the underlying data relationships, it doesn't make much sense to be building tables. 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". 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 |
#6
|
|||
|
|||
Import 1 record to many tables
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. 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. Yes! Well, I was thinking that it could make the importing process simpler! 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 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) 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. 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. 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". 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 |
#7
|
|||
|
|||
Import 1 record to many tables
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) 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 |
#8
|
|||
|
|||
Import 1 record to many tables
Thanks Jeff,
sorry - made a bit of a meal of that. But that solution seems to work. "Jeff Boyce" wrote: 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) 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 |
Thread Tools | |
Display Modes | |
|
|