Filtering and then comparing dates

[Mike]

I have two tables:

Table 1 - Sign Data
Table 2 - Sign installation History

Table 1 is tied to my sign information display form. On this form is a
button to view the sign history, which opens a subform tied to Table 2.

Here is the problem that I have.

On my sign information display form I want to display the most recent sign
installation activity for each sign from the sign history table.

Each sign has a ID number that relates the two tables to each.

I do not know how to do this. I do not know how to have access look at the
same ID numbers in the history, determine the most recent date, and then show
the most recent information on the same sign ID display on the main form.

Hope my explaination is clear enough. Thanks.

 

[KARL DEWEY]

You did not show your table structure but you must have a DateTime field in
your table2.
In the record source for the subform include Max([YourDateTimeField]).

 

[Mike]

Table 2 has a DateTime field, signID field, Installedby field, and comment
field.

Table 1 has the same signID field, with the detail sign data.

When I open my form, I want all the data for a specific SignID from Table 1
to display, plus I want the most recent data from Table 2 for the same Sign
ID to be diplayed as well.

Still unsure how to implement.

You did not show your table structure but you must have a DateTime field in
your table2.
In the record source for the subform include Max([YourDateTimeField]).

I have two tables:

Table 1 - Sign Data
Table 2 - Sign installation History

Table 1 is tied to my sign information display form. On this form is a
button to view the sign history, which opens a subform tied to Table 2.

Here is the problem that I have.

On my sign information display form I want to display the most recent sign
installation activity for each sign from the sign history table.

Each sign has a ID number that relates the two tables to each.

I do not know how to do this. I do not know how to have access look at the
same ID numbers in the history, determine the most recent date, and then show
the most recent information on the same sign ID display on the main form.

Hope my explaination is clear enough. Thanks.

 

[KARL DEWEY]

Are you using a query for your record source for the subform? If so, then
include Max([YourDateTimeField]).
If you are not using a query please post your SQL that is your record
source. You find it by opening the form in design view. Click on the menu
VIEW - Properties.

Table 2 has a DateTime field, signID field, Installedby field, and comment
field.

Table 1 has the same signID field, with the detail sign data.

When I open my form, I want all the data for a specific SignID from Table 1
to display, plus I want the most recent data from Table 2 for the same Sign
ID to be diplayed as well.

Still unsure how to implement.

You did not show your table structure but you must have a DateTime field in
your table2.
In the record source for the subform include Max([YourDateTimeField]).

I have two tables:

Table 1 - Sign Data
Table 2 - Sign installation History

Table 1 is tied to my sign information display form. On this form is a
button to view the sign history, which opens a subform tied to Table 2.

Here is the problem that I have.

On my sign information display form I want to display the most recent sign
installation activity for each sign from the sign history table.

Each sign has a ID number that relates the two tables to each.

I do not know how to do this. I do not know how to have access look at the
same ID numbers in the history, determine the most recent date, and then show
the most recent information on the same sign ID display on the main form.

Hope my explaination is clear enough. Thanks.

 

[Mike]

OK, let me start over since my descriptions are confusing me.

I have 2 tables.

Table 1 Sign Data - table of all sign data by specific sign
Table 2 Sign History - table of all sign history by specific sign

The two tables have a similar SignID field.

In my display form I currently show all the sign data from Table 1 for each
sign/record. Based upon whatever record/sign I am viewing, I want to have
the most recent data for that specific sign from Table 2 autofill onto my
form. The most recent data is table 2 will be indicated by the most recent
date.

I have two problems.

1) How do I make a query that will take the appropriate signID from the
record I have open in the form, apply it to table 2 to get all the history
for that specific record/sign ID?

2)Then how do you determine the most recent history activity and then
autofill the most recent activity onto my form?

Are you using a query for your record source for the subform? If so, then
include Max([YourDateTimeField]).
If you are not using a query please post your SQL that is your record
source. You find it by opening the form in design view. Click on the menu
VIEW - Properties.

Table 2 has a DateTime field, signID field, Installedby field, and comment
field.

Table 1 has the same signID field, with the detail sign data.

When I open my form, I want all the data for a specific SignID from Table 1
to display, plus I want the most recent data from Table 2 for the same Sign
ID to be diplayed as well.

Still unsure how to implement.

You did not show your table structure but you must have a DateTime field in
your table2.
In the record source for the subform include Max([YourDateTimeField]).

:

I have two tables:

Table 1 - Sign Data
Table 2 - Sign installation History

Table 1 is tied to my sign information display form. On this form is a
button to view the sign history, which opens a subform tied to Table 2.

Here is the problem that I have.

On my sign information display form I want to display the most recent sign
installation activity for each sign from the sign history table.

Each sign has a ID number that relates the two tables to each.

I do not know how to do this. I do not know how to have access look at the
same ID numbers in the history, determine the most recent date, and then show
the most recent information on the same sign ID display on the main form.

Hope my explaination is clear enough. Thanks.

 

[KARL DEWEY]

Make the SignID of Sign Data table a key field. Set a one-to-many relation
between the Sign Data table and the Sign History table.
Create a query in design view. Click on the Show Table icon on the tool bar
(has a yellow plus sign and a datasheet) and use the pulldown to select the
two tables.
Double click on the asterick in the tables to place the fields in the grid
of the query (The two tables will show a join due to their relations.).
Drag the field that you want to sort the results by to the grid. Select
Ascending for sort order. Click on the Show for that filed to turn off the
output (it will already be in the output by virture of the asterick).
Drag the date field Sign History table to the grid. Select Descending for
sort order. Click on the Show for that filed to turn off the output (it will
already be in the output by virture of the asterick).
Save.
Use this query as the record source of the form. The last history event
will be displayed.

It would be better to use a subform for the history.

OK, let me start over since my descriptions are confusing me.

I have 2 tables.

Table 1 Sign Data - table of all sign data by specific sign
Table 2 Sign History - table of all sign history by specific sign

The two tables have a similar SignID field.

In my display form I currently show all the sign data from Table 1 for each
sign/record. Based upon whatever record/sign I am viewing, I want to have
the most recent data for that specific sign from Table 2 autofill onto my
form. The most recent data is table 2 will be indicated by the most recent
date.

I have two problems.

1) How do I make a query that will take the appropriate signID from the
record I have open in the form, apply it to table 2 to get all the history
for that specific record/sign ID?

2)Then how do you determine the most recent history activity and then
autofill the most recent activity onto my form?

Are you using a query for your record source for the subform? If so, then
include Max([YourDateTimeField]).
If you are not using a query please post your SQL that is your record
source. You find it by opening the form in design view. Click on the menu
VIEW - Properties.

Table 2 has a DateTime field, signID field, Installedby field, and comment
field.

Table 1 has the same signID field, with the detail sign data.

When I open my form, I want all the data for a specific SignID from Table 1
to display, plus I want the most recent data from Table 2 for the same Sign
ID to be diplayed as well.

Still unsure how to implement.

:

You did not show your table structure but you must have a DateTime field in
your table2.
In the record source for the subform include Max([YourDateTimeField]).

:

I have two tables:

Table 1 - Sign Data
Table 2 - Sign installation History

Table 1 is tied to my sign information display form. On this form is a
button to view the sign history, which opens a subform tied to Table 2.

Here is the problem that I have.

On my sign information display form I want to display the most recent sign
installation activity for each sign from the sign history table.

Each sign has a ID number that relates the two tables to each.

I do not know how to do this. I do not know how to have access look at the
same ID numbers in the history, determine the most recent date, and then show
the most recent information on the same sign ID display on the main form.

Hope my explaination is clear enough. Thanks.