Date range output of inner query as input for outer query

[apoliticalpoindexter]

I have been searching high and low for the last three days to this situation. I am a n00b to Access and SQL but feel I've put forth due diligence here.

I can't quite figure out how to link these two queries correctly.

SELECT

table1.fields1, table1.fields2, table1.fields3

FROM

table1

WHERE (((table1.date_time) Between [Start date:] And [End date:]) AND ((table1.line_ID Like [Machine1] or table1.line_ID Like [Machine2])

(SELECT
table2.Part_Number,
table3.machine,
table2.serial_number,
table3.date_time,
table3.event

FROM

table2

INNER JOIN

table3

ON

table2.serial_number = table3.part_number

WHERE (((table3.serial_number) Like [SerialNumber]) AND ((table3.event)="EventStart" Or (table3.event)="EventStart")));

Sorry, some of the stuff is private so I had to just make generic names.

So here's the deal... the subquery would return a value from a Serial Number of when an event started and ended. Gives the date and time.

We then take that date and time and use it as inputs for the next query which then pulls a bunch of other data.

I'd like for us to just put in a serial number once, it go find that start and end date, then use that output to execute the next query and it just display the dataset for that serial number between the dates of the inner query.

As of right now, it tells me: "You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field"

Which... I don't want one field. I need the StartEvent date and time and the EndEvent date and time to be used as the Start Time and End Time of the outer query.

I've seen using WHERE value IN but that throws the same error. When I just do WHERE EXISTS

Sorry if any of this is not clear. I got that mountain-of-knowledge situation where I don't really know what to start searching for. I've read a lot of about queries, subqueries, the different expressions, comparisons, and sqlstatements, but there's a nuance I'm just overlooking somewhere.

[ranman256]

Make Q1 with the date range,
then make Q2 by joining Q1 with your other table.

[ssanfu]

I'm not the best with writing SQL, but I do see 2 glaring errors.

1st - commas are missing after field names in select statement

This

(SELECT
table2.Part_Number
table3.machine
table2.serial_number
table3.date_time
table3.event.event

should be

(SELECT
table2.Part_Number,   '<<-- comma
table3.machine,       '<<-- comma
table2.serial_number, '<<-- comma
table3.date_time,     '<<-- comma
table3.event.event

--------------------------------------------------------------------------------

2nd - criteria must be explicit when using multiple items and brackets misplaced
this

WHERE (table1.date_time Between [Start date:] And [End date:]) AND (table1.line_ID Like [Machine1 or Machine2])

should be

WHERE (table1.date_time Between [Start date:] And [End date:]) AND (table1.line_ID Like [Machine1] or table1.line_ID Like [Machine2])

[apoliticalpoindexter]

Thanks! I will give it a shot. I was also told to try and get Q1 display the start and end date in two different columns so I can then reference those columns as criteria in Q2. So, as a side quest, I'm trying to learn how to pivot my data over using joins.

[apoliticalpoindexter]

I will edit my original post. I was just sloppily transliterating what I had from my Access screen over to a forum post and having to change the names of certain things. Sorry about the confusion.

[ArviLaanemets]

You have a query which gets data from table1. You have another query which gets data from table2 and table3. I suspect it is the second query you want to use somewhere. But where?

About using query as subquery - try syntax like

SELECT ... FROM Table1 AS t1 LEFT OUTER JOIN (SELECT ... FROM Table2 t2 INNER JOIN (SELECT ... FROM Table3) AS ij ON ij.Key2 = t2.Key2) AS oj ON oj.Key1 = t1.Key1

[apoliticalpoindexter]

I actually have a slightly more refined approach... but still can't quite bring it together.

[SN_Event is the Query]

SELECT

Database1.SerialNumber,
Database2.Date_Time AS START_TIME,
Database2.Event,
Database2_1.Date_Time AS END_TIME,
Database2_1.Event

FROM 
(Database1 INNER JOIN Database2 ON Database1.SerialNumber = Database2.PartNumber)

INNER JOIN

Database2 AS Database2_1

ON

Database1.SerialNumber = Database2_1.PartNumber

WHERE 

(((Database1.SerialNumber) Like [Serial Number]) AND (( Database2.Event)="Event_Start") AND ((Database2_1.Event)="Event_End"));

So this puts my Event Start and Event End on two separate columns. I had someone try to explain to me how to make the next query reference it by bringing that Query in design view along with the other query...

And somehow have the "Start Date" and "End Date" criteria reference the other query using the Expression Builder.

Something like:

Between [SN_Query]!Database2.Date_Time AND [SN_Query]!Database2_1.Date_Time

Not really sure where to go from here...