Joining two tables based on timeframes

[Guest]

Hello.

I have two tables: audit status and log

Audit Status has five columns (shows that change in status on a ticket
throughout the life of the ticket)
CR_NUM (ticket number)
to_status
from_status
to_time
from_time

Log has several columns but three in play for this project (logs all actions
taken on the ticket)
ID (primary Key)
CR_NUM (ticket number)
Modified_Date

This issue I am having is that the log file doesnt show the status' of the
events at the time the event happened. I need to somehow join the tables
together so I show on the log file the status of the ticket for each log
entry.

An example:
I had a ticket that had 15 log entries into the log table. There were 5
change of status' in the audit status table. I need to join the tables and
add a field that says something like the following

Where Modified Date (from log table) is between to time (from audit table)
and from time (from audit table), select the from status (from audit table).

Im pretty new to access and this one has me stumped. I cant seem to join the
tables together and grab the status'. I hope this is a good enough
explanation of my issue...Thanks for any help you can give.

 

[kingston via AccessMonster.com]

Add the two tables to a query and create a join on CR_NUM.
I'm not sure what your intended output is*, but you can apply your logic as
such:
Field: [Modified_Date]
Criteria: Between [from_time] And [to_time]

*You're looking for data that all seem to come from [Audit Status] - select
status between from_time and to_time. What does the modified date have to do
with this?

 

[Guest]

Thanks for the response.

The modified date is the date that determines which status record to pick.
For example. If record 13 in the log table was modified at 11/11/2006 at 3pm
then it needs to look at the audit table and pull the status during that
timeframe. For example, ticket was on hold from 11/11/2006 at 1pm to
11/11/2006 at 5pm then record 13 had a status of hold when it was logged. I
might be putting the wrong type of join on cr_num but the dataset that
returns shows five sets of records. That could be because cr_num on log
matches all records in audit..but I need to return all records from log.

Add the two tables to a query and create a join on CR_NUM.
I'm not sure what your intended output is*, but you can apply your logic as
such:
Field: [Modified_Date]
Criteria: Between [from_time] And [to_time]

*You're looking for data that all seem to come from [Audit Status] - select
status between from_time and to_time. What does the modified date have to do
with this?

Hello.

I have two tables: audit status and log

Audit Status has five columns (shows that change in status on a ticket
throughout the life of the ticket)
CR_NUM (ticket number)
to_status
from_status
to_time
from_time

Log has several columns but three in play for this project (logs all actions
taken on the ticket)
ID (primary Key)
CR_NUM (ticket number)
Modified_Date

This issue I am having is that the log file doesnt show the status' of the
events at the time the event happened. I need to somehow join the tables
together so I show on the log file the status of the ticket for each log
entry.

An example:
I had a ticket that had 15 log entries into the log table. There were 5
change of status' in the audit status table. I need to join the tables and
add a field that says something like the following

Where Modified Date (from log table) is between to time (from audit table)
and from time (from audit table), select the from status (from audit table).

Im pretty new to access and this one has me stumped. I cant seem to join the
tables together and grab the status'. I hope this is a good enough
explanation of my issue...Thanks for any help you can give.

 

[kingston via AccessMonster.com]

OK, then join the tables based on CR_NUM and add this to the query output:

Field: [from_time]
Criteria: <=[Modified_Date]
AND
Field: [to_time]
Criteria: >=[Modified_Date]

This will work so long as there are no overlapping time periods for the same
CR_NUM.

Thanks for the response.

The modified date is the date that determines which status record to pick.
For example. If record 13 in the log table was modified at 11/11/2006 at 3pm
then it needs to look at the audit table and pull the status during that
timeframe. For example, ticket was on hold from 11/11/2006 at 1pm to
11/11/2006 at 5pm then record 13 had a status of hold when it was logged. I
might be putting the wrong type of join on cr_num but the dataset that
returns shows five sets of records. That could be because cr_num on log
matches all records in audit..but I need to return all records from log.

Add the two tables to a query and create a join on CR_NUM.
I'm not sure what your intended output is*, but you can apply your logic as

[quoted text clipped - 40 lines]

 

[Guest]

Thanks again for your help...Im not sure I have explained this clearly...Here
are my tables and the finished result I am looking for....

Log TABLE
ID cr_num mod_date

1 123456 4/15/2006 11:30:00 PM
2 123456 4/15/2006 11:00:00 PM
3 123456 4/15/2006 10:00:00 PM
4 123456 4/15/2006 6:00:00 PM
5 123456 4/15/2006 1:00:00 PM
6 123456 4/15/2006 12:00:00 PM
7 123456 4/15/2006 11:00:00 AM
8 123456 4/15/2006 10:00:00 AM
9 123456 4/15/2006 6:00:00 AM
10 123456 4/15/2006 3:00:00 AM
11 123456 4/15/2006 2:00:00 AM
12 123456 4/15/2006

AUDIT TABLE
cr_num to_stat from_stat to_time from_time

123456 closed 4/15/2006 12:00:00 PM
123456 Open closed 4/15/2006 10:00:00 AM 4/15/2006 12:00:00 PM
123456 Hold Open 4/15/2006 6:00:00 AM 4/15/2006 10:00:00 AM
123456 Open Hold 4/15/2006 2:00:00 AM 4/15/2006 6:00:00 AM
123456 Researching Open 4/15/2006 4/15/2006 2:00:00 AM

The finished result would look like this:

ID cr_num mod_date Status

1 123456 4/15/06 11:30 PM Closed
2 123456 4/15/06 11:00 PM Closed
3 123456 4/15/06 10:00 PM Closed
4 123456 4/15/06 6:00 PM Closed
5 123456 4/15/06 1:00 PM Closed
6 123456 4/15/06 12:00 PM Closed
7 123456 4/15/06 11:00 AM Closed
8 123456 4/15/06 10:00 AM Closed
9 123456 4/15/06 6:00 AM Open
10 123456 4/15/06 3:00 AM Hold
11 123456 4/15/06 2:00 AM Hold
12 123456 4/15/06 12:00 AM Open

Thanks again for any help you can give....

OK, then join the tables based on CR_NUM and add this to the query output:

Field: [from_time]
Criteria: <=[Modified_Date]
AND
Field: [to_time]
Criteria: >=[Modified_Date]

This will work so long as there are no overlapping time periods for the same
CR_NUM.

Thanks for the response.

The modified date is the date that determines which status record to pick.
For example. If record 13 in the log table was modified at 11/11/2006 at 3pm
then it needs to look at the audit table and pull the status during that
timeframe. For example, ticket was on hold from 11/11/2006 at 1pm to
11/11/2006 at 5pm then record 13 had a status of hold when it was logged. I
might be putting the wrong type of join on cr_num but the dataset that
returns shows five sets of records. That could be because cr_num on log
matches all records in audit..but I need to return all records from log.

Add the two tables to a query and create a join on CR_NUM.
I'm not sure what your intended output is*, but you can apply your logic as

[quoted text clipped - 40 lines]

tables together and grab the status'. I hope this is a good enough
explanation of my issue...Thanks for any help you can give.

 

[kingston via AccessMonster.com]

Using the criteria from my first answer and a LEFT JOIN:

SELECT [Log TABLE].[ID], [Log TABLE].[cr_num], [Log TABLE].[mod_date], [AUDIT
TABLE].[from_stat] AS Status
FROM [Log TABLE] LEFT JOIN [AUDIT TABLE] ON [Log TABLE].[cr_num] = [AUDIT
TABLE].[cr_num]
WHERE ([Log TABLE].[mod_date] Between [AUDIT TABLE].[from_time] And [AUDIT
TABLE].[to_time]);

However, it looks like your from_time and to_time values are backwards and
it's not clear what happens when a mod_date corresponds to two time periods
(e.g. ID 11, 4/15/2006 2:00:00 AM).

Thanks again for your help...Im not sure I have explained this clearly...Here
are my tables and the finished result I am looking for....

Log TABLE
ID cr_num mod_date

1 123456 4/15/2006 11:30:00 PM
2 123456 4/15/2006 11:00:00 PM
3 123456 4/15/2006 10:00:00 PM
4 123456 4/15/2006 6:00:00 PM
5 123456 4/15/2006 1:00:00 PM
6 123456 4/15/2006 12:00:00 PM
7 123456 4/15/2006 11:00:00 AM
8 123456 4/15/2006 10:00:00 AM
9 123456 4/15/2006 6:00:00 AM
10 123456 4/15/2006 3:00:00 AM
11 123456 4/15/2006 2:00:00 AM
12 123456 4/15/2006

AUDIT TABLE
cr_num to_stat from_stat to_time from_time

123456 closed 4/15/2006 12:00:00 PM
123456 Open closed 4/15/2006 10:00:00 AM 4/15/2006 12:00:00 PM
123456 Hold Open 4/15/2006 6:00:00 AM 4/15/2006 10:00:00 AM
123456 Open Hold 4/15/2006 2:00:00 AM 4/15/2006 6:00:00 AM
123456 Researching Open 4/15/2006 4/15/2006 2:00:00 AM

The finished result would look like this:

ID cr_num mod_date Status

1 123456 4/15/06 11:30 PM Closed
2 123456 4/15/06 11:00 PM Closed
3 123456 4/15/06 10:00 PM Closed
4 123456 4/15/06 6:00 PM Closed
5 123456 4/15/06 1:00 PM Closed
6 123456 4/15/06 12:00 PM Closed
7 123456 4/15/06 11:00 AM Closed
8 123456 4/15/06 10:00 AM Closed
9 123456 4/15/06 6:00 AM Open
10 123456 4/15/06 3:00 AM Hold
11 123456 4/15/06 2:00 AM Hold
12 123456 4/15/06 12:00 AM Open

Thanks again for any help you can give....

OK, then join the tables based on CR_NUM and add this to the query output:

[quoted text clipped - 23 lines]

 

[Guest]

Perfect thanks...that worked....

Also..on the timeframes matching both from and to time....Ill just be
matching against the from time. The data is backwards....I just threw it
together quickly...Thanks again for your help....!

Using the criteria from my first answer and a LEFT JOIN:

SELECT [Log TABLE].[ID], [Log TABLE].[cr_num], [Log TABLE].[mod_date], [AUDIT
TABLE].[from_stat] AS Status
FROM [Log TABLE] LEFT JOIN [AUDIT TABLE] ON [Log TABLE].[cr_num] = [AUDIT
TABLE].[cr_num]
WHERE ([Log TABLE].[mod_date] Between [AUDIT TABLE].[from_time] And [AUDIT
TABLE].[to_time]);

However, it looks like your from_time and to_time values are backwards and
it's not clear what happens when a mod_date corresponds to two time periods
(e.g. ID 11, 4/15/2006 2:00:00 AM).

Thanks again for your help...Im not sure I have explained this clearly...Here
are my tables and the finished result I am looking for....

Log TABLE
ID cr_num mod_date

1 123456 4/15/2006 11:30:00 PM
2 123456 4/15/2006 11:00:00 PM
3 123456 4/15/2006 10:00:00 PM
4 123456 4/15/2006 6:00:00 PM
5 123456 4/15/2006 1:00:00 PM
6 123456 4/15/2006 12:00:00 PM
7 123456 4/15/2006 11:00:00 AM
8 123456 4/15/2006 10:00:00 AM
9 123456 4/15/2006 6:00:00 AM
10 123456 4/15/2006 3:00:00 AM
11 123456 4/15/2006 2:00:00 AM
12 123456 4/15/2006

AUDIT TABLE
cr_num to_stat from_stat to_time from_time

123456 closed 4/15/2006 12:00:00 PM
123456 Open closed 4/15/2006 10:00:00 AM 4/15/2006 12:00:00 PM
123456 Hold Open 4/15/2006 6:00:00 AM 4/15/2006 10:00:00 AM
123456 Open Hold 4/15/2006 2:00:00 AM 4/15/2006 6:00:00 AM
123456 Researching Open 4/15/2006 4/15/2006 2:00:00 AM

The finished result would look like this:

ID cr_num mod_date Status

1 123456 4/15/06 11:30 PM Closed
2 123456 4/15/06 11:00 PM Closed
3 123456 4/15/06 10:00 PM Closed
4 123456 4/15/06 6:00 PM Closed
5 123456 4/15/06 1:00 PM Closed
6 123456 4/15/06 12:00 PM Closed
7 123456 4/15/06 11:00 AM Closed
8 123456 4/15/06 10:00 AM Closed
9 123456 4/15/06 6:00 AM Open
10 123456 4/15/06 3:00 AM Hold
11 123456 4/15/06 2:00 AM Hold
12 123456 4/15/06 12:00 AM Open

Thanks again for any help you can give....

OK, then join the tables based on CR_NUM and add this to the query output:

[quoted text clipped - 23 lines]

tables together and grab the status'. I hope this is a good enough
explanation of my issue...Thanks for any help you can give.