Averaging Time from mulitple tables

  • Thread starter Thread starter Phoenix2520
  • Start date Start date
P

Phoenix2520

im currently making a database that logs time for how long people are waiting
i want to make a query that averages all that infomation i have 1 table that
logs when people arrive and another one when people leave i also need to be
able to use it when there is more data added to it. i know nearly nothing
about queries in databases so can you type in plain english, thanks in
advance.
 
You will not be able to do this unless you have a way to tie your tables
together.

Presumably you have a table of clients, with a ClientID primary key. Or
perhaps you just assign a ticket number when the person arrives, and use the
same ticket number when they leave. However you do it, you must have some
way to match the arrival record in one table with the departure record in
the other table--a common ClientID or TicketID number.

Step 1: Create a query that shows the arrival time.
You can do this with the query designer.
When you switch to SQL View (View menu), it will look something like this:
SELECT Arrival.ClientID,
Arrival.ArrivalTime
FROM Arrival;

Step 2: Add a subquery to get the matching departure time.
You will have to type the new bit into SQL View.
It will be something like this:
SELECT Arrival.ClientID,
Arrival.ArrivalTime,
(SELECT Min(Depart.DepartTime) AS MinDepartTime
FROM Depart
WHERE Depart.ClientID = Arrival.ClientID
AND Depart.DepartTime > Arrival.ArrivalTime) AS DepartTime
FROM Arrival;

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

Step 3: Calculate the difference.
Once you have the subquery returning the correct date time value, use
DateDiff() to calculate the difference in minutes. It will be something like
this:

SELECT Arrival.ClientID,
Arrival.ArrivalTime,
DateDiff("n", Arrival.ArrivalTime,
(SELECT Min(Depart.DepartTime) AS MinDepartTime
FROM Depart
WHERE Depart.ClientID = Arrival.ClientID
AND Depart.DepartTime > Arrival.ArrivalTime)) AS Minutes
FROM Arrival;

In the query, it's best to keep the duration as a number, i.e. in minutes or
seconds, or divide by 60 for hours and fractions of an hour. If you need to
display it as hours:minutes in a report, this article explains how:
http://allenbrowne.com/casu-13.html
 
Thanks For The Help,i just wanted to check if my code was right

SELECT Entry_Card.[Card Number],
Entry_Card.[Arrival Time]
FROM Entry_Card;

(SELECT Entry_Card.[Card Number], Entry_Card.[Arrival Time]
FROM Entry_Card;
(SELECT Min(Exit_Card.Time Exited) AS MinDepartTime
FROM Exit_Card
WHERE Exit_Card.Card Number = Entry_Card.Card Number
AND Exit_Card.Exit Time > Entry_Card.Arrival Time) AS Time Exited
FROM Entry_Card;)

SELECT Entry_Card.Card Number,
Entry_Card.Arrival Time,
DateDiff("n", Entry_Card.Arrival Time,
(SELECT Min(Exit_Card.Time Exited) AS MinDepartTime
FROM Exit_Card
WHERE Exit_Card.Card Number = Entry_Card.Card Number
AND Exit_Card.Time Exited > Card Entry.Arrival Time)) AS Minutes
FROM Card Entry;


Allen said:
You will not be able to do this unless you have a way to tie your tables
together.

Presumably you have a table of clients, with a ClientID primary key. Or
perhaps you just assign a ticket number when the person arrives, and use the
same ticket number when they leave. However you do it, you must have some
way to match the arrival record in one table with the departure record in
the other table--a common ClientID or TicketID number.

Step 1: Create a query that shows the arrival time.
You can do this with the query designer.
When you switch to SQL View (View menu), it will look something like this:
SELECT Arrival.ClientID,
Arrival.ArrivalTime
FROM Arrival;

Step 2: Add a subquery to get the matching departure time.
You will have to type the new bit into SQL View.
It will be something like this:
SELECT Arrival.ClientID,
Arrival.ArrivalTime,
(SELECT Min(Depart.DepartTime) AS MinDepartTime
FROM Depart
WHERE Depart.ClientID = Arrival.ClientID
AND Depart.DepartTime > Arrival.ArrivalTime) AS DepartTime
FROM Arrival;

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

Step 3: Calculate the difference.
Once you have the subquery returning the correct date time value, use
DateDiff() to calculate the difference in minutes. It will be something like
this:

SELECT Arrival.ClientID,
Arrival.ArrivalTime,
DateDiff("n", Arrival.ArrivalTime,
(SELECT Min(Depart.DepartTime) AS MinDepartTime
FROM Depart
WHERE Depart.ClientID = Arrival.ClientID
AND Depart.DepartTime > Arrival.ArrivalTime)) AS Minutes
FROM Arrival;

In the query, it's best to keep the duration as a number, i.e. in minutes or
seconds, or divide by 60 for hours and fractions of an hour. If you need to
display it as hours:minutes in a report, this article explains how:
http://allenbrowne.com/casu-13.html
im currently making a database that logs time for how long people are
waiting
Click to expand...
[quoted text clipped - 5 lines]
about queries in databases so can you type in plain english, thanks in
advance.
Click to expand...
Click to expand...
 
Sorry for double post but i also need a way to average the entire table not
just one entire does this code do this?
Thanks For The Help,i just wanted to check if my code was right

SELECT Entry_Card.[Card Number],
Entry_Card.[Arrival Time]
FROM Entry_Card;

(SELECT Entry_Card.[Card Number], Entry_Card.[Arrival Time]
FROM Entry_Card;
(SELECT Min(Exit_Card.Time Exited) AS MinDepartTime
FROM Exit_Card
WHERE Exit_Card.Card Number = Entry_Card.Card Number
AND Exit_Card.Exit Time > Entry_Card.Arrival Time) AS Time Exited
FROM Entry_Card;)

SELECT Entry_Card.Card Number,
Entry_Card.Arrival Time,
DateDiff("n", Entry_Card.Arrival Time,
(SELECT Min(Exit_Card.Time Exited) AS MinDepartTime
FROM Exit_Card
WHERE Exit_Card.Card Number = Entry_Card.Card Number
AND Exit_Card.Time Exited > Card Entry.Arrival Time)) AS Minutes
FROM Card Entry;
You will not be able to do this unless you have a way to tie your tables
together.
Click to expand...
[quoted text clipped - 50 lines]
Click to expand...
 
1. Use square bracket around names that contain spaces, e.g. [Exit Time]

2. In the Exit_Card table, is the field named [Exit Time] or [Time Exited]?

3. Your middle query won't show the TimeExited correctly.
The subquery is part of the SELECT clause, so before the FROM clause.

Try:
SELECT Entry_Card.[Card Number],
Entry_Card.[Arrival Time]
(SELECT Min(Exit_Card.[Exit Time) AS MinExitTime
FROM Exit_Card
WHERE (Exit_Card.[Card Number] = Entry_Card.[Card Number])
AND (Exit_Card.[Exit Time] > Entry_Card.[Arrival Time])) AS TimeExited
FROM Entry_Card;

Once that's working, you can work on the DateDiff() part.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Phoenix2520 via AccessMonster.com said:
Thanks For The Help,i just wanted to check if my code was right

SELECT Entry_Card.[Card Number],
Entry_Card.[Arrival Time]
FROM Entry_Card;

(SELECT Entry_Card.[Card Number], Entry_Card.[Arrival Time]
FROM Entry_Card;
(SELECT Min(Exit_Card.Time Exited) AS MinDepartTime
FROM Exit_Card
WHERE Exit_Card.Card Number = Entry_Card.Card Number
AND Exit_Card.Exit Time > Entry_Card.Arrival Time) AS Time Exited
FROM Entry_Card;)

SELECT Entry_Card.Card Number,
Entry_Card.Arrival Time,
DateDiff("n", Entry_Card.Arrival Time,
(SELECT Min(Exit_Card.Time Exited) AS MinDepartTime
FROM Exit_Card
WHERE Exit_Card.Card Number = Entry_Card.Card Number
AND Exit_Card.Time Exited > Card Entry.Arrival Time)) AS Minutes
FROM Card Entry;


Allen said:
You will not be able to do this unless you have a way to tie your tables
together.

Presumably you have a table of clients, with a ClientID primary key. Or
perhaps you just assign a ticket number when the person arrives, and use
the
same ticket number when they leave. However you do it, you must have some
way to match the arrival record in one table with the departure record in
the other table--a common ClientID or TicketID number.

Step 1: Create a query that shows the arrival time.
You can do this with the query designer.
When you switch to SQL View (View menu), it will look something like this:
SELECT Arrival.ClientID,
Arrival.ArrivalTime
FROM Arrival;

Step 2: Add a subquery to get the matching departure time.
You will have to type the new bit into SQL View.
It will be something like this:
SELECT Arrival.ClientID,
Arrival.ArrivalTime,
(SELECT Min(Depart.DepartTime) AS MinDepartTime
FROM Depart
WHERE Depart.ClientID = Arrival.ClientID
AND Depart.DepartTime > Arrival.ArrivalTime) AS DepartTime
FROM Arrival;

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

Step 3: Calculate the difference.
Once you have the subquery returning the correct date time value, use
DateDiff() to calculate the difference in minutes. It will be something
like
this:

SELECT Arrival.ClientID,
Arrival.ArrivalTime,
DateDiff("n", Arrival.ArrivalTime,
(SELECT Min(Depart.DepartTime) AS MinDepartTime
FROM Depart
WHERE Depart.ClientID = Arrival.ClientID
AND Depart.DepartTime > Arrival.ArrivalTime)) AS Minutes
FROM Arrival;

In the query, it's best to keep the duration as a number, i.e. in minutes
or
seconds, or divide by 60 for hours and fractions of an hour. If you need
to
display it as hours:minutes in a report, this article explains how:
http://allenbrowne.com/casu-13.html
im currently making a database that logs time for how long people are
waiting
Click to expand...
[quoted text clipped - 5 lines]
about queries in databases so can you type in plain english, thanks in
advance.
Click to expand...
Click to expand...
Click to expand...
 
ok my Exit_Card Table is Time Exited

and my new code is

SELECT Entry_Card.[Card Number],
Entry_Card.[Arrival Time]
FROM Entry_Card;

( SELECT Entry_Card.[Card Number],
Entry_Card.[Arrival Time]
(SELECT Min(Exit_Card.[Exit Time) AS MinExitTime
FROM Exit_Card
WHERE (Exit_Card.[Card Number] = Entry_Card.[Card Number])
AND (Exit_Card.[Exit Time] > Entry_Card.[Arrival Time])) AS TimeExited
FROM Entry_Card; )

SELECT Entry_Card.[Card Number],
Entry_Card.[Arrival Time],
DateDiff("n", Entry_Card.[Arrival Time],
(SELECT Min(Exit_Card.[Time Exited]) AS MinDepartTime
FROM Exit_Card
WHERE Exit_Card.[Card Number] = Entry_Card.[Card Number]
AND Exit_Card.[Time Exited] > [Card Entry].Arrival Time)) AS Minutes
FROM [Card Entry];

so is that all i need and what do i do with it just paste it into one SQL
view Queries page

Allen said:
1. Use square bracket around names that contain spaces, e.g. [Exit Time]

2. In the Exit_Card table, is the field named [Exit Time] or [Time Exited]?

3. Your middle query won't show the TimeExited correctly.
The subquery is part of the SELECT clause, so before the FROM clause.

Try:
SELECT Entry_Card.[Card Number],
Entry_Card.[Arrival Time]
(SELECT Min(Exit_Card.[Exit Time) AS MinExitTime
FROM Exit_Card
WHERE (Exit_Card.[Card Number] = Entry_Card.[Card Number])
AND (Exit_Card.[Exit Time] > Entry_Card.[Arrival Time])) AS TimeExited
FROM Entry_Card;

Once that's working, you can work on the DateDiff() part.
Thanks For The Help,i just wanted to check if my code was right
Click to expand...
[quoted text clipped - 77 lines]
Click to expand...
 
Yep: looks like time to start testing it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Phoenix2520 via AccessMonster.com said:
ok my Exit_Card Table is Time Exited

and my new code is

SELECT Entry_Card.[Card Number],
Entry_Card.[Arrival Time]
FROM Entry_Card;

( SELECT Entry_Card.[Card Number],
Entry_Card.[Arrival Time]
(SELECT Min(Exit_Card.[Exit Time) AS MinExitTime
FROM Exit_Card
WHERE (Exit_Card.[Card Number] = Entry_Card.[Card Number])
AND (Exit_Card.[Exit Time] > Entry_Card.[Arrival Time])) AS TimeExited
FROM Entry_Card; )

SELECT Entry_Card.[Card Number],
Entry_Card.[Arrival Time],
DateDiff("n", Entry_Card.[Arrival Time],
(SELECT Min(Exit_Card.[Time Exited]) AS MinDepartTime
FROM Exit_Card
WHERE Exit_Card.[Card Number] = Entry_Card.[Card Number]
AND Exit_Card.[Time Exited] > [Card Entry].Arrival Time)) AS Minutes
FROM [Card Entry];

so is that all i need and what do i do with it just paste it into one SQL
view Queries page

Allen said:
1. Use square bracket around names that contain spaces, e.g. [Exit Time]

2. In the Exit_Card table, is the field named [Exit Time] or [Time
Exited]?

3. Your middle query won't show the TimeExited correctly.
The subquery is part of the SELECT clause, so before the FROM clause.

Try:
SELECT Entry_Card.[Card Number],
Entry_Card.[Arrival Time]
(SELECT Min(Exit_Card.[Exit Time) AS MinExitTime
FROM Exit_Card
WHERE (Exit_Card.[Card Number] = Entry_Card.[Card Number])
AND (Exit_Card.[Exit Time] > Entry_Card.[Arrival Time])) AS TimeExited
FROM Entry_Card;

Once that's working, you can work on the DateDiff() part.
Thanks For The Help,i just wanted to check if my code was right
Click to expand...
[quoted text clipped - 77 lines]
about queries in databases so can you type in plain english, thanks in
advance.
Click to expand...
Click to expand...
Click to expand...
 
Okay: back up and start building the query a bit at a time, testing each
part, instead of trying to build it all together.

I'm not sure if you realised that I did change your SQL statement in the
post before last.

You need to do your own debugging here. We don't have your tables, so can
only offer examples.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Phoenix2520 via AccessMonster.com said:
when i try to save it it said Characters Found after end of SQL statement?

Allen said:
Yep: looks like time to start testing it.
ok my Exit_Card Table is Time Exited
Click to expand...
[quoted text clipped - 48 lines]
about queries in databases so can you type in plain english, thanks
in
advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top