Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Uploading data (Access 2000)

    I'm a novice at Access. I need help on uploading data to access.

    I've a table 1 with contract no and an account number per contract number and table 2 with a list of valid account numbers and names for this account numbers. The account no in table1 is a lookup to table 2.

    In table 1 only the id field for table 2 is shown that referance to table 2. How do I load the data into table 1 if I'm not showing the account number but an ID to table 2. I've tried to make a new table with idfield and acc no from table to and running a query to update table 1 with the id fields but that doesn't seem to work.

    Am I trying to put the cart before the horses or what.

    Thanks

    Mario

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Uploading data (Access 2000)

    I'm not quite sure what you're trying to do. The whole point of a relational database is that data only lives in one location. In this case, it sounds like your data is scrambled between the two tables. Are the account numbers unique in either of the two tables? If not, you need a table that holds unique account. It isn't clear whether the names you mentioned are names of the accounts or names associated with the accounts, such as contact persons.

    The unique account numbers should live only in one table, and the unique key that identifies that particular account should be what is inserted into the other tables to relate their records to a particular account. Any lookups should be from the many side of the relationship to the one side. So the table that holds records where many records may use the same account number would look up its values in the table that holds only unique account numbers.

    Does that clarify it for you? If you need more help, you'll need to explain the actual structure of the individual tables, what their fields are, and how you're trying to relate them.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Uploading data (Access 2000)

    Sorry that I sound confusing. I've attached a sample file. My problem is if I want to load contracts on with the relevant acc numbers it doesn't work because it is storing an id field in table1 for the value from table 2. How do I get around it.

    Thanks

    Mario
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Uploading data (Access 2000)

    OK, the table designs are correct, but I don't know what you mean by "loading" contracts. Are you talking about data entering contracts or importing data or what?
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Uploading data (Access 2000)

    I've been using Excel to keep record of the contracts. I've got about 4000 contracts to load. No if I load/import say contract no 1 acc no a1 then in table1 I can't load it as a1 since the acc no field is an id field. I can't manually enter each contract manually. So how will I load it from Excel and still keep the ref integrity sound.

    Thanks Mario

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Uploading data (Access 2000)

    OK, I see what you're trying to do, and you can't. What you need to do is import the Excel data into a new table. Then add a field to the new table to hold the ID number for the account. Now create a query that links your new table and the account number table on the account number fields in both tables. Change your query to an update query and update the field in the new table with the ID for that account number. Then you can append records from the table imported from Excel to your contract table in Access.
    Charlotte

  7. #7
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Uploading data (Access 2000)

    Thanks for the help. I will try it

Posting Permissions

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