Query with dates order (1 Viewer)

[Jayce72]

I have 3 tables. A person table, a checking in table and a checking out table

I want to build a query that brings back one line for a person thats checked in and out for each stay

E.g "Person a" - "01.01/2000" - "02/01/2001"
"Person a" - "01.02/2000" - "02/02/2001"
"Person b" - "01.02/2002" - "02/02/2002"

 

[CJ_London]

So why can't you? Not sure what your question is or whether the data you have provided is the data you want to query or the result you require.

Post your table design (table and fieldnames plus datatype) for the three tables, some sample data and the result you want to get from the that sample data.

 

[Jayce72]

So why can't you? Not sure what your question is or whether the data you have provided is the data you want to query or the result you require.

Post your table design (table and fieldnames plus datatype) for the three tables, some sample data and the result you want to get from the that sample data.

If I create a query linking the ID field on Person Table to the ID on the other two tables with dates, it'll duplicate the lines as the query won't know how to say.

• The earliest date on table 1 - then find the date on table two which is greater than the first date but less than the next date on table 1 (which will be the next check in date.

Therefore, this will show on one line -rather than show multiple lines for each date if finds.

 

[CJ_London]

it can be done, but without the information I have requested, I can't suggest how

 

[Jayce72]

So why can't you? Not sure what your question is or whether the data you have provided is the data you want to query or the result you require.

Post your table design (table and fieldnames plus datatype) for the three tables, some sample data and the result you want to get from the that sample data.

Example - database attached

 

[CJ_London]

and what result do you want from that data?

 

[Jayce72]

and what result do you want from that data?

If you look at Query1, It shows all data and Dups on each date.

What I want to show is (sorry about the spacing etc):

Query1 CustomerName DateIn DateOut Jim 01/01/2000 14/01/2000 Jim 01/06/2000 14/06/2000
Jim 01/01/2001 14/01/2001
and carrying on for other people. So It finds the first date in 'Table_In' then follows with the next date in 'Table_Out' then a new line for the next start date for each person. Therefore, it shows a line for in and out for each person etc

 

[Jayce72]

If you look at Query1, It shows all data and Dups on each date.

What I want to show is (sorry about the spacing etc):

Query1
CustomerName DateIn DateOut
Jim 01/01/2000 14/01/2000
Jim 01/06/2000 14/06/2000
Jim 01/01/2001 14/01/2001

and carrying on for other people. So It finds the first date in 'Table_In' then follows with the next date in 'Table_Out' then a new line for the next start date for each person. Therefore, it shows a line for in and out for each person etc

Please see new format above

 

[CJ_London]

try this as a query, just copy and paste the code into a new query

SELECT Person.CustomerName, Table_In.DateIn, (Select min(dateout) from table_out WHERE JoinID=person.ID AND DateOut>table_in.DateIn) AS DateOut
FROM Person INNER JOIN Table_In ON Person.ID = Table_In.JoinID
ORDER BY Person.CustomerName, Table_In.DateIn;

Note that if you have two in dates followed by an out date, both in dates will have the same out date

 

[Jayce72]

try this as a query, just copy and paste the code into a new query

SELECT Person.CustomerName, Table_In.DateIn, (Select min(dateout) from table_out WHERE JoinID=person.ID AND DateOut>table_in.DateIn) AS DateOut
FROM Person INNER JOIN Table_In ON Person.ID = Table_In.JoinID
ORDER BY Person.CustomerName, Table_In.DateIn;

Note that if you have two in dates followed by an out date, both in dates will have the same out date

PERFECT - But one more thing please - If there was another field on TableOut called TYPE (text only) how would I also include this next to DateOut which will have data next to each date

 

[CJ_London]

You'll need to add back the table_out as you had in the original query then make the subquery a criteria for the dateout field in that table.

Note that since this becomes a criteria, you will need to alias Table_out, either in the subquery or in the main query - e.g.

(Select min(dateout) from table_out T WHERE.....

 

[Jayce72]

You'll need to add back the table_out as you had in the original query then make the subquery a criteria for the dateout field in that table.

Note that since this becomes a criteria, you will need to alias Table_out, either in the subquery or in the main query - e.g.

(Select min(dateout) from table_out T WHERE.....

Due to other factors, I've had to re-do my query. Therefore, now two new queries One query with the Person and TableIn and then Created another new Query adding this Query and then adding in TableOut. Therefore, Im not sure how to do your latest suggestion. Could you please elaborate on my new design

 

[CJ_London]

not without seeing it