Use a range from a different table in Builder (1 Viewer)

Hi All,

I'm not sure this is possible but I thought I'd float the idea here to see if anyone can think of a solution.

I have a Batch Table which lists the batch numbers for each invoice period, as below:

Period Batch Month
Dec-09 363273 0
Jan-10 367229 1
Feb-10 373376 2
Mar-10 379019 3
Apr-10 382467 4
May-10 386305 5
Jun-10 391724 6
Jul-10 396687 7
Aug-10 0 8
Sep-10 0 9
Oct-10 0 10
Nov-10 0 11
Dec-10 0 12

I then have another table which lists the 000s of transactions in the current year, and includes the batch number if they have been invoiced (Transaction Table).

I want to create a Query with a builder that looks at the batch number in the Transaction Table, and returns the 'Month' that the batch is in from the Batch Table. i.e. If it was batch number 394000, then bring back 7, as per the table above.

Does anyone know if that is possible at all?

Thank you!
Nick

There are a couple of different ways to pull the month number. One would be to include the batch table and the transaction table in the query and join them via the batch number field. Then, select the fields you want from either table.

Another way is to use the DLookup() function in your query

DLookup("month","batchtable","batchnumber=" & transaction.batchnumber)

A third way would be to use a nested query.

The first way is probably your best option.