keeping data aligned

[chesterman]

I have an Table tblassets which has a field fldcurrentloc. In another table
tbltransactions I have another field fldlocationto. What I want to ensure is
that fldcurrentloc is always be the same as the latest entry in
fldcurrentloc. The tranactions table does have a flddate so transactions can
be sorted. THe two tables are linked via the field fldassetno.

Any guidance or help appreciated.

 

[Jeff Boyce]

I may not fully understand your data design. It sounds like you are storing
the same information in two tables. Are you using the primary key of
"location", or adding the text?

You didn't mention if you were using forms to do this. You'll find a strong
consensus in the newsgroups to use your tables to store data, but forms (and
reports) to add/edit/display your data.

If you already have a table (tblTransactions) that has a date field and a
field indicating location, why do you need to (re-)store the location?
Couldn't you just use a query and look up the most recent transaction for
that asset to find the location?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[John Vinson]

I have an Table tblassets which has a field fldcurrentloc. In another table
tbltransactions I have another field fldlocationto. What I want to ensure is
that fldcurrentloc is always be the same as the latest entry in
fldcurrentloc. The tranactions table does have a flddate so transactions can
be sorted. THe two tables are linked via the field fldassetno.

Any guidance or help appreciated.

The fildcurrentloc should not be stored in ANY table. It can be looked
up at any time using a query, with a criterion selecting the maximum
date.

John W. Vinson[MVP]

 

[chesterman]

Sorry for asking what is probably a very simple question but how do I select
fldlocationto based on latest flddate, when I try to create the query I
cannot get it to work. I have the tbltransactions selected and put
fldlocationto in the Field box, tbltransactions is in the Table box. What do
I need to enter in the criteria box it does not appear to like =max(flddate)

 

[John Vinson]

Sorry for asking what is probably a very simple question but how do I select
fldlocationto based on latest flddate, when I try to create the query I
cannot get it to work. I have the tbltransactions selected and put
fldlocationto in the Field box, tbltransactions is in the Table box. What do
I need to enter in the criteria box it does not appear to like =max(flddate)

Use a Subquery:

= (SELECT Max([flddate]) FROM tbltransactions AS X
WHERE X.fldassetno = tbltransactions.fldassetno)

John W. Vinson[MVP]

 

[chesterman]

John & Jeff

Thanks for all the help - so much to learn.

Sorry for asking what is probably a very simple question but how do I select
fldlocationto based on latest flddate, when I try to create the query I
cannot get it to work. I have the tbltransactions selected and put
fldlocationto in the Field box, tbltransactions is in the Table box. What do
I need to enter in the criteria box it does not appear to like =max(flddate)

Use a Subquery:

= (SELECT Max([flddate]) FROM tbltransactions AS X
WHERE X.fldassetno = tbltransactions.fldassetno)

John W. Vinson[MVP]