Results 1 to 3 of 3
  1. #1
    fetster is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2016
    Posts
    24

    Query to join 2 similar structured tables that are external data links & perform calculation

    I'm quite new to Access / databases so sorry if I sound like a novice.



    I have 2 sets of data - another access database containing 1 table and a spreadsheet containing a similar table (each table has over 200,000 rows of data so they're quite big).

    I want to combine the 2 tables together into Access and run an IF function on them to provide a new column of data.



    Table 1 - Spreadsheet External Data link (Table called EZ Charge Table)

    Data: TransactionNo, TransDate, TransTime, PLUCode, Quantity, UnitPrice, TotalPrice, Till, PaymentMethod, DateAdded, ChargeID, RemoteNo, HeadMemberID, HeadGuestID, PersonID, DOB, Age, PostCode, DiscountID, Discount, CALCULATION RESULT

    Table 2 External Data Link to another Access Database (TransItems Table)

    Data: TransNo, Date, Time, ItemNo, Qty, Val, BLANK, ECR, BLANK, BLANK, BLANK, BLANK, BLANK, BLANK, BLANK, BLANK, BLANK, BLANK, BLANK, BLANK, BLANK


    Both sets of data are independent of one another and I need to add a prefix to each of the TransactionNo (EZ) and TransNo (TI) to keep them independent.

    Firstly how do I go about joining them in this way to create a new table that I can then use within the database.


    The last column in the EZ Charge Table results needs to be a calculation whereby:
    if PLUCode is blank -> do nothing and keep it blank
    if PLUCode = 2 then do:
    Calculate age in months from DOB - TransDate. If age in months is between 0 - 6 then PLUCode "1000", If age in months is between 7 - 12 then PLUCode "1001", If age in months is between 13 - 24 then PLU Code "1002", If age in months is between 36 - 132 then PLU code "1003", If age in months >133 then PLUCode "1004".
    I managed to do the above in Excel using this function =IF(E20="","",IF(E20=2,LOOKUP(AE20,{0,7,13,36,133} ,{1000,1001,1002,1003,1004}),E20)) using AE20 as the age in months calculation field.

    Table TransItem does not have any PLU's that need changing so I do not need to run when importing that table.


    Sorry for such a long winded question - thanks for taking the time to read.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make your 1st query a 'make table' query to create the new table w dataset 1
    then the 2nd query is an append of dataset2 to this new table.

    the codes can be done in excel, or in the query using concat method.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Not really sure what you are trying to do, but I have a few comments.

    Firstly how do I go about joining them in this way to create a new table that I can then use within the database.
    To put records from both data sets, create a table, then use append queries to add the records.

    The Excel calculated field should be calculated in Excel before the append.
    At the time of the append, add a constant column in the append query, either "EZ" for the "EZ Charge Table" records or "TI" for the "TransItems Table". Be sure to add a field to the table to hold the "EZ/TI" data. What do you what to name this field?

    As far as field names, you should NOT use spaces, punctuation or special characters (exception is the underscore).
    Cannot have a table design with field names like: "BLANK, BLANK, BLANK, BLANK, BLANK".
    The table filed names would be like the suggested field names for "EZ Charge Table".

    Be aware of reserved words as object names. "Date", Time", "Name" and "Description" are often used. Very bad idea.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Join the data of two tables using query
    By Esmatullaharifi in forum Queries
    Replies: 1
    Last Post: 05-03-2015, 08:15 AM
  2. Replies: 2
    Last Post: 02-02-2014, 07:19 PM
  3. Replies: 18
    Last Post: 01-02-2014, 10:06 AM
  4. Tricky calculation to perform
    By leeli67 in forum Access
    Replies: 122
    Last Post: 04-15-2012, 05:06 PM
  5. SQL expression to perform a calculation
    By springboardjg in forum Queries
    Replies: 1
    Last Post: 05-20-2011, 06:57 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums