Go Back   Access World Forums > Microsoft Access Discussion > Queries

Thread Tools Rate Thread Display Modes
Old 08-13-2010, 04:41 AM   #1
Newly Registered User
Join Date: Mar 2010
Posts: 110
Thanks: 9
Thanked 0 Times in 0 Posts
Nevsky78 is on a distinguished road
Use a range from a different table in Builder

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!

Nevsky78 is offline   Reply With Quote
Old 08-15-2010, 07:04 PM   #2
Access Hobbyist
Join Date: Mar 2008
Posts: 2,629
Thanks: 0
Thanked 311 Times in 308 Posts
jzwp22 has a spectacular aura about jzwp22 has a spectacular aura about jzwp22 has a spectacular aura about
Re: Use a range from a different table in Builder

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.
jzwp22 is offline   Reply With Quote

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
need help in automatic updating database from xml pushkar1986 Modules & VBA 6 05-27-2013 12:13 PM
TransferDatabase - Creating a DB to Transfer info from 1 DB into another CNx Modules & VBA 1 10-24-2008 09:21 PM
way too many tables qwertyjjj Theory and practice of database design 3 08-01-2007 01:04 PM
[SOLVED] Multiple Updates to a table from another table databasedave Modules & VBA 0 01-31-2006 12:18 PM
Query Same Table Twice for Date Range Comparisons of Same Data cft Queries 0 03-03-2003 01:04 PM

All times are GMT -8. The time now is 07:32 PM.

Microsoft Access Help
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post

Sponsored Links

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World