Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Auto Numbering 2 fields in an append query

    I would like to import excel data and append it to an existing DB. In the past all data has been entered from the keyboard. Now a lot of what I get is in a form i can convert to an excel spreadsheet. The difficulty I have is that there are 2 tables involved. One creates a auto batch number for a group of entries the other an auto number for each of the group entries. There is of course common data between the two tables and the link is an operator number. Searching the lounge and the web the best I can work out is not to include the auto number fields in the append query is there anything else I should know or do.

    thanks

    Peter

  2. #2
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    It's a bit difficult to know if this answer will be useful without knowing about more about what you're going to do with the data, and exactly where the auto numbers are coming from, but here goes.

    Are the auto batch numbers coming from the external forms, or are you adding a primary key field when you import or link to the data? Assuming the table(s) you're appending to after import/linking have their own autonumber primary keys, and neither of the existing autonumbers are needed for linking, I think you're probably right and the best thing is to leave the autonumber fields out of the append query.

    If they are being created when you import the data, you can adjust your import specifications to say that you don't need to add a primary key field - that way they won't be there to bother you at all.

    Likewise, if they are being created in the original forms or Excel before you even import the tables into Access, you can again adjust your import specifications to skip those fields.

    Cheers

    Alison C

  3. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Data not in Excel Worksheet

    Thanks for the response. The Batch number nor the group number's are in the excel worksheet. The worksheet has the Operator number the amount account designation, and date. I can import them one group at a time so each batch needs to get the same batch number and each entry a separate number. Both fields in the table I will import to are auto numbers. the query is based on a Batch Table and an expense table, the link is normally the batch number which of course has not been generated until the append table is run .Hope that all a makes sense.

    thanks

    peter

Posting Permissions

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