Create a query to compute time

[Brian T]

I am trying to create a query that will determine the elapsed time from a
table. The table may have up to 4 timeframes.

 

[John W. Vinson]

I am trying to create a query that will determine the elapsed time from a
table. The table may have up to 4 timeframes.

Use the DateDiff function.

Since we cannot see your table, and since "timeframes" are meaningful in your
application but not in Access in general, I can't be any more specific than
that. For a more detailed answer please post a more detailed question.

 

[Jeff Boyce]

Brian

We're not there. We can't see what you're looking at.

We have no idea what "the table may have up to 4 timeframes" means to you,
or what it looks like, or what kind of data you might have.

It all starts with the data. Please describe your table structure, the
field data types, and provide an example.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Brian T]

Here's the table set-up

Member Location1 Start1 End1 Location2 Start2
End2
Blow, Joe VT 2/17/1987 6/22/2001 NY
6/22/01
Doe, Jane DE 3/14/1990 3/14/2000 VT
5/15/01 4/10/08



I want to find the length of time for each location for each member.

 

[Jeff Boyce]

Brian

The table set-up you provided looks a lot like ... a spreadsheet! Access is
not a spreadsheet. If you want to get good use of Access'
relationally-oriented features and functions, you can't feed it 'sheet data.

Is there a reason you aren't just doing this in Excel (or some other
spreadsheet)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Brian T]

Yes, I am also relating these values to awards that are given based upon
time. I slao looked at the spreadsheet option and the formulas that I need
to use got, well quite complicated.

 

[John W. Vinson]

Here's the table set-up

Member Location1 Start1 End1 Location2 Start2
End2
Blow, Joe VT 2/17/1987 6/22/2001 NY
6/22/01
Doe, Jane DE 3/14/1990 3/14/2000 VT
5/15/01 4/10/08



I want to find the length of time for each location for each member.

Your table setup IS WRONG. You have repeating groups. I suspect you have - or,
worse, will sometime need - a Loction3, Location4 and so on!

If you have a one (member) to many (postings? not sure what the
location/start/end combination represents) then you should have two tables in
a one to many relationship.

As it is, you will need a Query based on another Query: first a UNION query to
normalize your "spreadsheet" format data:

SELECT [Member], Location1, DateDiff("d", [Start1], [End1]) AS Duration
FROM tablename
WHERE Location1 IS NOT NULL
UNION ALL
SELECT [Member], Location2, DateDiff("d", [Start2], [End2])
FROM tablename
WHERE Location2 IS NOT NULL;

Save this query and then base a Totals query on it, grouping by Member and
Location and summing Duration.

 

[Brian T]

I understand having two separate tables, but how do I create the one to many
relationship?

Here's the table set-up

Member Location1 Start1 End1 Location2 Start2
End2
Blow, Joe VT 2/17/1987 6/22/2001 NY
6/22/01
Doe, Jane DE 3/14/1990 3/14/2000 VT
5/15/01 4/10/08



I want to find the length of time for each location for each member.

Your table setup IS WRONG. You have repeating groups. I suspect you have - or,
worse, will sometime need - a Loction3, Location4 and so on!

If you have a one (member) to many (postings? not sure what the
location/start/end combination represents) then you should have two tables in
a one to many relationship.

As it is, you will need a Query based on another Query: first a UNION query to
normalize your "spreadsheet" format data:

SELECT [Member], Location1, DateDiff("d", [Start1], [End1]) AS Duration
FROM tablename
WHERE Location1 IS NOT NULL
UNION ALL
SELECT [Member], Location2, DateDiff("d", [Start2], [End2])
FROM tablename
WHERE Location2 IS NOT NULL;

Save this query and then base a Totals query on it, grouping by Member and
Location and summing Duration.

 

[John W. Vinson]

I understand having two separate tables, but how do I create the one to many
relationship?

Let's say you have two tables named Members and Postings (again, you have not
explained what the data means so I have no idea what a Location and date range
mean). You could then have three tables:

Members
MemberID <Autonumber, Primary Key>
LastName
FirstName
<other biographical info>

Postings
PostingID <Autonumber, Primary Key>
MemberID <Long Integer, link to Members>
Location <text, link to Locations>
StartDate <Date/Time>
EndDate <Date/Time>

Locations
Location <text, primary key>

The Locations table could be used as the rowsource of a combo box to save
typing and allow locations to be selected from a list. You could then have a
Form based on Members, with a continuous Subform based on Postings; use
MemberID as the master/child link field, and have the subform consist only of
a combo box to select the location and two textboxes for the dates. You could
add a third textbox to the subform with a control source

=DateDiff("d", [StartDate], [EndDate])

to dynamically calculate the time at that location, if it's useful to see that
info on the form.

 

[Brian T]

Thank you John, why I was vague your info helped.

I understand having two separate tables, but how do I create the one to many
relationship?

Let's say you have two tables named Members and Postings (again, you have not
explained what the data means so I have no idea what a Location and date range
mean). You could then have three tables:

Members
MemberID <Autonumber, Primary Key>
LastName
FirstName
<other biographical info>

Postings
PostingID <Autonumber, Primary Key>
MemberID <Long Integer, link to Members>
Location <text, link to Locations>
StartDate <Date/Time>
EndDate <Date/Time>

Locations
Location <text, primary key>

The Locations table could be used as the rowsource of a combo box to save
typing and allow locations to be selected from a list. You could then have a
Form based on Members, with a continuous Subform based on Postings; use
MemberID as the master/child link field, and have the subform consist only of
a combo box to select the location and two textboxes for the dates. You could
add a third textbox to the subform with a control source

=DateDiff("d", [StartDate], [EndDate])

to dynamically calculate the time at that location, if it's useful to see that
info on the form.