Create a 2 step query into 1

[KingOf206]

Ok, I am going to do the best I can in explaining this... Here it goes:

-I have two table that list information for ID numbers (ID #'s are primary key)
-The first table includes the fields; Tray1 and Seq1.


-The second table includes Tray2 and Seq2.
-The first step is that I would like to create a single query that would allow me to enter an ID, & The query would then look in Table1 to find Tray1 and Seq1 for that ID
-The second step (this would be done at the same time) I would also like it to do, would be to find the ID that corresponds to the same Tray1, but one less than Seq1 (i.e. Seq1 minus 1). The query would then look up the resulting ID in Table 2, and return the Tray2 and Seq2 for that ID.


For example, in the following tables if I enter ID 106, which is in Tray1=2 and Seq1=2, it would then locate Tray1=2 and Seq1=1, which is ID=104. The query would then look up ID=104 in Table 2, and return Tray2=29 and Seq2=18.

TABLE 1				TABLE 2
ID	Tray1	Seq1		ID	Tray2	Seq2
101	6	14		101	31	3
102	2	3		102	16	2
103	1	4		103	18	21
104	2	1		104	29	18
105	5	6		105	4	16
106	2	2		106	8	49
107	6	8		107	16	20
	enter ID (this is entered in the query) =		106
	Then the results would be this: ID= 104 Return Tray2 = 29
	Return Seq2 =		18

I am hoping that this is possible to do all in 1 step. Currenty I have 2 queries I use to make this happen and I would like to make this an easy 1 step qry. Any (simplified) help would be very much appreciated!!

[June7]

Only options I can see call for a custom function.

[KingOf206]

Only options I can see call for a custom function.

What does that entail

[June7]

VBA programming.

What's wrong with using two query objects? If you must have one object, can probably nest them. Post the sql statements for analysis.

[KingOf206]

VBA programming.

What's wrong with using two query objects? If you must have one object, can probably nest them. Post the sql statements for analysis.

Here are the current 2 queries that I run:

The SQL code for the first query is:
SELECT dbo_kcpb_mailpiece.ApplicationID, dbo_kcpb_mailpiece.Current_TrayID, dbo_kcpb_mailpiece.Last_TrayID, dbo_kcpb_event.SeqNo, dbo_kcpb_pass.JobName, [SeqNo]-1 AS Expr1
FROM (dbo_kcpb_event INNER JOIN dbo_kcpb_mailpiece ON dbo_kcpb_event.MailpieceID = dbo_kcpb_mailpiece.ID) INNER JOIN dbo_kcpb_pass ON dbo_kcpb_event.PassID = dbo_kcpb_pass.ID
WHERE (((dbo_kcpb_mailpiece.ApplicationID)=[enter ApplicationID]));

And the SQL code for the second query is:
SELECT dbo_kcpb_mailpiece.ApplicationID, dbo_kcpb_mailpiece.Audit_TrayNo, dbo_kcpb_mailpiece.Audit_TraySeq, dbo_voter.name_first, dbo_voter.name_middle, dbo_voter.name_last, dbo_kcpb_mailpiece.SigVer_TrayID, dbo_kcpb_event.SeqNo
FROM ((dbo_kcpb_event INNER JOIN dbo_kcpb_mailpiece ON dbo_kcpb_event.MailpieceID = dbo_kcpb_mailpiece.ID) INNER JOIN dbo_kcpb_tray ON dbo_kcpb_event.TrayID = dbo_kcpb_tray.ID) INNER JOIN (dbo_av_election INNER JOIN dbo_voter ON dbo_av_election.voter_id = dbo_voter.voter_id) ON dbo_kcpb_mailpiece.ApplicationID = dbo_av_election.av_election_id
WHERE (((dbo_kcpb_mailpiece.SigVer_TrayID)=[enter TrayID]) AND ((dbo_kcpb_event.SeqNo)=[enter SeqNo]));

[June7]

What is name of the first query? I assume it is referenced somewhere in the second. I can see 3 dataset names in the SELECT clause. Reference to db_kcpb_tray is in the JOIN clause but no fields in the SELECT.

[KingOf206]

What is name of the first query? I assume it is referenced somewhere in the second. I can see 3 dataset names in the SELECT clause. Reference to db_kcpb_tray is in the JOIN clause but no fields in the SELECT.

Here are the current 2 queries that I run:

The SQL code for the first query is. Named:“QRY_Find an Orphan”
SELECT dbo_kcpb_mailpiece.ApplicationID, dbo_kcpb_mailpiece.Current_TrayID, dbo_kcpb_mailpiece.Last_TrayID, dbo_kcpb_event.SeqNo, dbo_kcpb_pass.JobName, [SeqNo]-1 AS Expr1
FROM (dbo_kcpb_event INNER JOIN dbo_kcpb_mailpiece ON dbo_kcpb_event.MailpieceID = dbo_kcpb_mailpiece.ID) INNER JOIN dbo_kcpb_pass ON dbo_kcpb_event.PassID = dbo_kcpb_pass.ID
WHERE (((dbo_kcpb_mailpiece.ApplicationID)=[enter ApplicationID]));

And the SQL code for the second query is, Named :“QRY_Find an Orphan, part 2”
SELECT dbo_kcpb_mailpiece.ApplicationID, dbo_kcpb_mailpiece.Audit_TrayNo, dbo_kcpb_mailpiece.Audit_TraySeq, dbo_voter.name_first, dbo_voter.name_middle, dbo_voter.name_last, dbo_kcpb_mailpiece.SigVer_TrayID, dbo_kcpb_event.SeqNo
FROM ((dbo_kcpb_event INNER JOIN dbo_kcpb_mailpiece ON dbo_kcpb_event.MailpieceID = dbo_kcpb_mailpiece.ID) INNER JOIN dbo_kcpb_tray ON dbo_kcpb_event.TrayID = dbo_kcpb_tray.ID) INNER JOIN (dbo_av_election INNER JOIN dbo_voter ON dbo_av_election.voter_id = dbo_voter.voter_id) ON dbo_kcpb_mailpiece.ApplicationID = dbo_av_election.av_election_id
WHERE (((dbo_kcpb_mailpiece.SigVer_TrayID)=[enter TrayID]) AND ((dbo_kcpb_event.SeqNo)=[enter SeqNo]));


I am about to leave the office so I wont get back to this thread untill Monday morning. Again, I thank you for your help and I look forward to completing this "custom function" or nesting that you are trying to help me with.

[June7]

I don't see the first query referenced by the second so don't see how they can be combined.

So how do they work together to get the desired results?

[KingOf206]

I don't see the first query referenced by the second so don't see how they can be combined.

So how do they work together to get the desired results?

I run the first query, and when prompted I enter the ID number 106. It returns the Tray1 number (2), the Seq1 number (2), and the Seq1 number minus 1 (1).

I then write those numbers down...

I then run the second query, where I enter the Tray1 number (2), and the Seq1 number (1) that I got from the first query. It then returns the ID number that corresponds to that Tray and Seq, in this case 104. It also returns the Tray2 and Seq2 for 104, which is 29 and 18.

They are not connected data-wise...

[June7]

Sorry, not seeing a solution other than VBA.

Maybe some complicated nested DLookup() could work some magic but my brain starts spinning when I try to envision this.

[hapm]

No need to do dlookups or VBA code here. Build a query on the first table that filters by your criteria. Add calculated fields based on the found records of table one, that represent the values of table two. Create a second query that joins your first query with the second table using the calculated fields. Et viola