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.