Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Lookup? (Access97 sr2)

    I'm a novice Access user. I have imported two csv files into tables which each contain customer account numbers as a field. One table (35k records) contains customers from Jan & Feb 2003. The larger table contains customers from all of 2002 (335k records). In either table, a customer account number will repeat for each transaction.

    I'd like to create a field in the Jan/Feb file (target file) that looks into the 2002 file (source file) to determine if the account number exists in that file (source file). If it does, the field should return a 1, if not then 0. I could do this in Excel but for the size of the 2002 file. In Excel the lookup() or vlookup() would be employed, but Access "Help" directs me to complete a field that allows the user to lookup an then select from a list (presumably from the source file).

    What is this function called that in Excel would be called Lookup? How can I build an expression in a table or query that does tells me if this customer had activity last year?

    Thanks in Advance!

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

    Re: Lookup? (Access97 sr2)

    It seems to me that you want to link the 2003 table to the 2002 table, aren't they both transaction files? If they are both transaction files, I would put them in the one table called Transactions.
    If I'm not getting this please explain.
    Pat

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

    Re: Lookup? (Access97 sr2)

    You don't need to create a field in the table, unless you want to "freeze" the situation

    Create a new query.
    Add both tables.
    Join them by dragging the customer account number field from the Jan/Feb 2003 table to the corresponding field in the 2002 table.
    Double click the join line.
    Sepecify that you want to see *all* records from the Jan/Feb 2003 table.
    Add the customer account number from the Jan/Feb 2003 table to the query grid, then do the same for the customer account number from the 2002 table.
    Make the query into a Totals query by selecting View/Totals or by clicking the Totals button (the greek capital sigma).
    Both fields will by default have Group By as Totals option.
    Change that for the field from the 2002 table to Count.
    Look at the result of the query. For each customer account number from the Jan/Feb 2003 table, you will see the number of corresponding records in the 2002 table.
    Perhaps you can use this. If you really want 0 or 1, post back.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Lookup? (Access97 sr2)

    Thanks Hans! I just printed out your instructions and voila! Works perfectly! The Count even tells me how many transactions from the previous year.

Posting Permissions

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