Trouble with DLast/Last Check number

[SDelgado]

My query is using two tables. tblBatchExport and tblCheckReg
BatchNum, BatchRunDate come from the first, CheckNo comes from the last.

I want to make a single row table with these three pieces of data. (the new table is used by Crystal Enterprise to run a series of reports)

If I use the 'totals' button and do group by for the batchnum and batchrundate it works fine but for the CheckNo when I use Last or Max I get odd results like a random check 100 or 200 checks before the last one.

I hope I've given enough to go on, I appreciate any help.

 

[lespaul]

How do the two tables related? Is BatchNum a FK in tblCheckReg?

What check number do you want? the highest number one from each batchNum?

What is the SQL for the query you have now that's not quite right?

Leslie

 

[SDelgado]

Yes to first question.
I want the very last check number issued
The problem is, no matter how I try to get that last check number I get a number seemingly at random.

 

[dhookom]

Last is generally not reliable. I can only assume you want to use Max or DMax().

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[lespaul]

So, you have:

tblBatchExport
BatchNum
BatchRunDate


tblCheckReg
CheckNo
BatchNum (FK)

tblBatchExport has data like this:

BatchNum BatchRunDate
1 7/10/04
2 8/15/04
3 9/10/04

tblCheckReg has data like this:
CheckNo BatchNum
12345 1
12346 1
12347 1
13448 2
13449 2
13450 2
15553 3
15554 3
15555 3


What would you want to return from this data?

Again, what is the SQL for the query you are trying to run that isn't working?




Leslie

 

[SDelgado]

THe tblbatchexport holds only one batch at a time, so I just want one instance of it's batch number and run date.

So from your example it would be batch number 3, batch run date 9/10/04 and check number 13450 because no checks would have yet been issued for batch 3 so none in register.

The sql view is currently like this (though I've tried a few different ways):
SELECT tblBatchExport.BatchNumber, tblBatchExport.BatchRunDate, Max(tblCheckRegister.CheckNo) AS MaxOfCheckNo
FROM tblBatchExport INNER JOIN tblCheckRegister ON tblBatchExport.BatchNumber = tblCheckRegister.BatchNumber
GROUP BY tblBatchExport.BatchNumber, tblBatchExport.BatchRunDate;

 

[lespaul]

Ok, so you want the last check number in tblCheckReg no matter which batch it comes from?

OK, i know this syntax works in other SQL, it may have issues in Access, but try this:

SELECT tblBatchExport.BatchNumber, tblBatchExport.BatchRunDate, (SELECT Max(CheckNo) FROM tblCheckRegister) As MaxOfCheckNo
FROM tblBatchExport

Leslie

 

[SDelgado]

I have it in there as you show above but I keep getting a 'type mismatch' now

 

[lespaul]

What version of Access are you running?

I think the problem in your existing query is that you are joining on the BatchNumber in both tables, but you just said there are no batchNumber '3' in the CheckReg table.

Have you tried:

SELECT tblBatchExport.BatchNumber, tblBatchExport.BatchRunDate, CheckNo AS MaxOfCheckNo
FROM tblBatchExport INNER JOIN tblCheckRegister ON tblBatchExport.BatchNumber - 1 = tblCheckRegister.BatchNumber
WHERE CheckNo = (SELECT Max(CheckNo) From tblCheckRegister) 
GROUP BY tblBatchExport.BatchNumber, tblBatchExport.BatchRunDate;

Now this assumes that the batchNumbers are sequential and that if you have 3 in your batch table that the last one in the CheckReg table will be a 2.

Leslie

 

[SDelgado]

I did a copy/paste of your code then tried to go to query view and got this message: database can't represent the join expression tblBatchExport.BatchNumber - 1 = tblCheckRegister.BatchNumber in design view

I tried running it anyway and got this message after running the code: you tried to execute a query that does not include the specified expression 'MaxOfCheckNo' as part of an aggregate function

I tried changing [CheckNo] as maxofcheckno to [tblcheckregister].[checkno] with the same results.

I'm beginning to feel like this isn't ment to happen....sigh....

 

[lespaul]

change it to:

SELECT tblBatchExport.BatchNumber, tblBatchExport.BatchRunDate, CheckNo 
FROM tblBatchExport INNER JOIN tblCheckRegister ON tblBatchExport.BatchNumber - 1 = tblCheckRegister.BatchNumber
WHERE CheckNo = (SELECT Max(CheckNo) From tblCheckRegister);

and it doesn't matter that it can't represent it graphically. When you start doing more and more complex SQL, that just happens. You don't need to switch to design view, you can just run it from the SQL view.

Leslie

 

[SDelgado]

Sorry for the delayed feedback. I just gave it a try. It works but I get 350 instances of the same answer (one for each entry in the tblbatchexport). Is there any way to get just one response? You are definately on the right track and I appreciate the help.

 

[lespaul]

yep, you add DISTINCT:

SELECT DISTINCT tblBatchExport.BatchNumber, tblBatchExport.BatchRunDate, CheckNo 
FROM tblBatchExport INNER JOIN tblCheckRegister ON tblBatchExport.BatchNumber - 1 = tblCheckRegister.BatchNumber
WHERE CheckNo = (SELECT Max(CheckNo) From tblCheckRegister);

Leslie

 

[SDelgado]

Thanks!!! It worked! I really appreciate your sticking with me and working it out.

 

[lespaul]

cool beans! glad you got it!

les