Combine Columns From Two Tables Into One AND Add Prompt to Return Records OLDER Than A Specific Year

Question

I've been at this for almost two days and I keep getting all sorts of errors in Access. So I am asking for your help. Not sure if this is even possible. THANKS

I have three tables.

The Ask
I would like to build a query to combine the data in ContractData and InvoiceData tables (see Query 1 Table).

When I run the query, Access should prompt me to enter the DocumentDate YEAR (ie. 2018) and return all records that are older than said YEAR (ie. 2018).

If ties exists for the same ID, then there is no preference as to which record is returned.

If there are no records for an ID with a DocumentDate, then Access should remove duplicates and return only one (no preference as to which record is returned). (See The Ultimate Result Table)

Answer 1

I think I was able to produce what you’re looking for, though it’s a multi-step process.

But first, Name is a reserved word in Access. I changed the Name field to SupplierName in all 3 of your example tables.

Field names will need to be the same in the ContractData and InvoiceData tables for this to work as described below.

I also setup a unique primary key (AutoNumber) in each table. Will probably still work without, but I didn’t try it, and would not recommend going forward without each record uniquely identified.

Now for the fun parts…

Step 1: create a Union Query.

Create ->

Query Design ->

Close the Show Table window without adding anything.

Top left of the screen click SQL View.

Delete what’s there, and paste the following:

SELECT ID, SupplierName, DocumentDate, DocumentNumber, DocumentStatus
FROM ContractData

UNION 

SELECT ID, SupplierName, DocumentDate, DocumentNumber, DocumentStatus
FROM InvoiceData;

Click Run.

This should now be the data shown in your Query 1 Table example.

Close this query and save as Query1Table (or whatever name you want).

Step 2, create a second query, using the query we just crated (Query1Table):

Create ->

Query Design ->

Close the Show Table window without adding anything

Top left of the screen click SQL View

Delete what’s there, and paste the following:

SELECT Query1Table.ID, Query1Table.SupplierName, First(Query1Table.DocumentDate)
 AS FirstOfDocumentDate, First(Query1Table.DocumentNumber)
 AS FirstOfDocumentNumber, First(Query1Table.DocumentStatus)
 AS FirstOfDocumentStatus
FROM Query1Table
WHERE (((Year([DocumentDate]))>[Enter the DocumentDate YEAR]))
GROUP BY Query1Table.ID, Query1Table.SupplierName;

If you didn’t call the first query we made Query1Table, then you’ll need to adjust the above SQL statement appropriately.

Close query and save it as TheUltimateResult (or whatever you want).

When you open this query, TheUltimateResult, you’ll be prompted to enter a Year.

Enter the desired year and click OK.

All done!

Hopefully this will fulfill what you’re looking to do.