Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing Data (97)

    Hello again

    My magnificent database holds Customer information in one table, address information in another and contact information in another.

    A user has his data in a spreadsheet which I have tried to import in the following stages:

    1 - Created a Query from the three tables and included the relevant fields.

    2 - Checked the spreadsheet heading names matched.

    3 - Imported the spreadsheet data into a new table

    4 - Copied the records in the new table

    5 - Pasted the copied records into the Query

    Only the first field pasted.

    Hints, tips, advise gratefully received.

    Regards

    Graeme

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing Data (97)

    I don't know enough about your database to know if what you want is possible, but I doubt it. Did you select the entire empty new record in the query before pasting?

    I would create three append queries to append records to each of the three tables.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Data (97)

    I've tried selecting the record in the Query with the record selector, selecting all fields in the record and selecting just the first field, all with the same result.

    The database hold records of customer details in one table, details of those customers addresses in another table and details of the contacts at those addresses in a third table.

    The spreadsheet lists all this information on each row and hence there is a great deal of repetition in the case of many addresses per customer and many contacts per address.

    I could split the spreadsheet into three and import each part into each of the three database tables but then I would need to manually lookup and type in all the CustomerID, AddressID and ContactID numbers in order for the information to link up and display correctly on the form.

    I'll read up on append queries!

    Regards

    Graeme

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing Data (97)

    Since one customer can have several addresses and one address several contacts, you can't use one query based on the three tables to add new records to all three tables at once.

    Does your spreadsheet contain unique identifiers for customers,addresses and contacts, or do you need to generate them in the database? In the latter case, some manual intervention may be inevitable. There is no need to split the spreadsheet into three parts.

    Start by creating a query based on the spreadsheet; add only customer details and set Unique Values to Yes to select unique customers. Use this query to add records to the customers table.

    Then, create a query based on the customers table and the spreadsheet. Add CustomerID plus address data, and set Unique Values to Yes. You will have to experiment to find out on which fields to join them in order to match the records correctly. Use this query to populate the adress table.

    Finally, create a query based on the address table and the spreadsheet. Add AddressID plus contact data, and set Unique Values to Yes. You will have to experiment to find out on which fields to join them to match the records correctly. Use this query to populate the contacts table.

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Importing Data (97)

    I could split the spreadsheet into three and import each part into each of the three database tables but then I would need to manually lookup and type in all the CustomerID, AddressID and ContactID numbers in order for the information to link up and display correctly on the form....

    I think that's exactly what you will have to do - although I don't think it will have to be (entirely) manual.

    Presumably, each record in the Address File has a foreign key field for "CustomerID" (it may have its own primary key for "AddressID" or not). Similarly, each record in the Contact File has a FK with CustomerID, and may or may not have a "ContactID" PK.

    If the CustomerID is an autonumber field, you can either import just the customer names into Access, and then re-export the relevant names and IDs so they are available again in XL, or you can change the CustomerID to be input by the user (and then set it back after you have finished) - see below. If CustomerID is some sort of concatenated field (JONESAPP for Jones' Apparel, for instance) you can probably determine them in the XL s/sheet itself (if they are not already there). The simplest solution is if you can determine (or input) the appropriate CustIDs in XL - (ie they are not an autonumber or incrementing field). If so:

    <UL><LI> Make three copies of the spreadsheet - ensure that each line includes the required CustomerID
    <LI> On one copy, delete everything (ie, all columns) but the customer name information that will be included in the Customer File, and the Customer ID. Do a filter operation with data copied to a new location below the existing data range, set to copy "Unique records only." Delete the original information, leaving only the unique records and column headings - save this file to be imported into the Customer File in Access
    <LI> On the second copy, delete everything (all columns) but address information that will be saved in the address file and the CustomerID column. Add AddressIDs if required. As for the customer file, filter the data to obtain the unique records then save the file for import into the Address file
    <LI> On the third copy, repeat this procedure, deleting all the columns except for the contact information, add ContactIDs if needed, filter to get unique records, and then save the file
    <LI> To import the file, open Access and click on File | Get External Data | Import - this will bring up the standard file dialog. Set it to look for Excel files and then highlight the "customer" XL file you created above - follow the import instructions to import the file.
    <LI> Follow the same procedure with the other two files[/list]If the CustID is an autonumber field, you have two choices: either reset the Access properties to make it non-autonumbering, create your own CustID numbers as above, do the steps to import the three files, and then re-set CustID to be an autonumbering field, or import customer names to Access, then export Customer name / CustID pairs to Excel. <font color=red>I do not recommend the first option</font color=red> - it requires you to delete all the relationships that CustID participates in, and then re-establish them after the job is done; that's fiddly and error-prone work. The better alternative is to re-export the data to excel (you can get XL to reference this data while it is still in Access, but it presents another opportunity for invisible errors) :

    <UL><LI> Do the first step above - separate out the unique customer file information into a separate XL file
    <LI> Import that file into Access
    <LI> Write a query in Access that outputs CustomerID and Customer Name, and export that query to XL (from datasheet view, "Tools | Office Links | Analyze it with XL"
    <LI> In XL, use this file as a lookup table to generate the required CustomerID for each line of the original file, given the value of Customer Name - copy those values onto themselves, and then create the Address and Contact excel files and import them into Access as described above.[/list]

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Importing Data (97)

    Why don't you post your zipped spreadsheet for all to see, then we can make much more informed suggestions.

  7. #7
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Data (97)

    Dean

    Thanks for the answer. I'll work through it.

    The spread sheet and database are attached, as suggested by Patt. However, the Contacts table is in fact a Contracts table (I went with the flow on the original error to avoid introducing further confusion!) So each Address has many Contracts. Each Contract has many Documents and each Document has many stages, information held in a History Table.

    The data is greatly reduced to shrink the zip file and largely fictitious. If anybody wants to give it a go feel free!

    In the meantime I'll go play with the actual.

    By the way, what's the next step from a 'new lounger' and what has to happen to to become one?!!!

    Regards

    Graeme
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing Data (97)

    >> what's the next step from a 'new lounger' and what has to happen to to become one?

    Your User Title is determined by the number of your posts. Currently, you have 19 posts; when you reach 25, you'll become a Lounger.

    See <!help=16>Help 16<!/help> for an overview.

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Importing Data (97)

    The spreadsheet is not a representative one as far as data is concerned, could you provide a better sample please. Maybe part of the live one if that's not too sensitive in nature.
    From the spreadsheet, what do you want to put in what tables. I cannot find a Customer table, only a tblAddress and tblContract.

  10. #10
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Data (97)

    (Edited by HansV to activate link to post - see <!help=19>Help 19<!/help>)

    Pat

    The database tables are:
    TblCompany
    TblAddress
    TblContract
    TblDocument
    TblHistory
    TblInvoice
    TblRevenue

    I've re-attached the spreadsheet showing which fields are to go in which table. The data is fictitious partly for confidentiality and partly because it has never existed. The original spreadsheet supplied by my database user did not include all the information required by the database (eg. no address) so I made it up (you may have guessed!)just to ensure the import works.

    The database actually contains information with respect to Company Names and Addresses on over two and a half thousand companies because at it's inception it was based on my other magnificent Contacts and Quotes database (<post#=253306>post 253306</post#>)

    This database is to record information regarding the contract documentation archiving which up until now has been held on the spreadsheet. So now when the MD comes steaming out of his office shouting, "Where's the ****ing Contract Documentation for the Bla Bla Bla job?" I can have an accurate response in ten seconds!

    Regards

    Graeme
    Attached Files Attached Files

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Importing Data (97)

    Thanks for that, what we need now is for you to tell me:
    1. what fields in Address are to be used to identify distinct records to join to the Contract data. Is it Address1, Address2, Town, County, Postcode?
    2. what fields in Contract data is to be used identify distinct records to join the Document data. Is it JobNo?
    3. what fields in Document data is to be used identify distinct records to join History. Is it DocTitle?
    4. what fields in History data is to be used to identify distinct records of History. Is it DocLocation?

    These fields are then used in Append queries to populate the various tables (tblAddress, tblContract, etc.).

    What you should be able to do is to import the spreadsheet into a table called tblImport. This table will also have additional fields that will hold the ID for Company, Address, Contract, Document and History.
    Then as Hans has suggested you need to run:
    1. an Append query that puts distinct records based upon Name from tblImport into the table tblCompany. This will generate an AutoNumber ID that will be used as the CompanyID.
    2. an Append query that puts distinct records based upon Name, Address1, Address2, Town, County and Postcode into the table tblAddress. This will generate an AutoNumber ID that will be used as the AddressID.
    3. an Append query that puts distinct records based upon Name, Address1, Address2, Town, County, Postcode and JobNo into the table tblContract. This will generate an AutoNumber ID that will be used as the ContractID.
    4. an Append query that puts distinct records based upon Name, Address1, Address2, Town, County, Postcode, JobNo and DocTitle into the table tblDocument. This will generate an AutoNumber ID that will be used as the DocumentID.

    After these tables have been populated it should be fairly straightforward to use Update queries to populate field CustomerID in table tblAddress, field AddressID in table tblContract, etc. All of this should be able to be done automatically, not manually as has been mentioned, however, I may be corrected on this point.

    I hope I have not confused you too much (I think I have confused myself) with all this.

    nb. In your spreadsheet you have shown CostCentre in table tblAddress whereas your database shows CostCentre in table tblContract. There may be other anomolies, I have not checked.

  12. #12
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Data (97)

    Pat

    Things are really hotting up now!

    The TblCustomer table has a Primary key CustomerID
    The TblAddress table has a Primary key AddressID and contains the Foriegn Key CustomerID
    The TblContract table has a Primary key ContractID and contains the Foriegn Key AddressID
    The TblDocument table has a Primary key DocumentID and contains the Foriegn Key ContractID
    The TblInvoice table has a Primary key InvoiceID and contains the Foriegn Key ContractID
    The TblRevenue table has a Primary key RevenueID and contains the Foriegn Key ContractID
    The TblHistory table has a Primary key HistoryID and contains the Foriegn Key DocumentID

    All this can be seen in the Relationships screen in the usual way

    I have a few questions on the procedure you propose, Append Queries looseing the relationships between the existing Companies and Addresses etc, but I'll go away and try it first! Thanks for your help.

    It's tempting to pay someone to type all the information from the spread sheet into the database but there are 425 rows! And anyway how would I learn anything without these sorts of puzzles!

    Cost Centre information is held in the Contract table, well spotted!

    Regards

    Graeme

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Importing Data (97)

    I have already taken a look at the relationships diagram.
    What I want to know is:
    What marries the Address records together for ultimately tieing together records further down the chain (eg. Contract, etc)?
    What marries the Contract records together to tie together the Document records together?
    What marries the Document records together to tie together the History records?

    It would really help if you could supply even a few valid records in a spreadsheet (it's no use to me, I live in Australia, so email to me privately, my email is in my profile), this would then be a big help to me, by way of saving time, in trying to solve this problem.

    Your comment about paying somebody to key them all in takes the thrill out of doing this by program. You would also have to ensure that your form/subforms would be spot on to key them in in the first place. So the amount of time spent on this should be a worthwhile exercise, especially if you are going to do this more than once (although it seems unlikely).

  14. #14
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Importing Data (97)

    What's happening on this subject?

  15. #15
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Data (97)

    Pat

    Thanks for your continued interest in my dilemma. And Dean and Hans.

    I had to attend meetings most of yesterday and today is the big announcement of our company change of name. The new name being the worlds worst kept secret!

    I've attached a more representative spreadsheet as requested. I've noticed some things in the database that need sorting (field names (but it was the first draft after all!)) but that's that
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •