link queries

[angie]

i have one table with the following fields among others:

vehicle plates, previous kms, current kms, date, location. i have created a
query that draws all data from my table and it works fine.
i have also created another query with a calculated field that adds one
month to the date mentioned above.
my aim is to show the current kms of the month as the previous kms of the
following month.
i have tried to link the two queries but instead of returning 200 records i
get 974 records.
how can i solve this problem? do i need the two queries?

 

[MGFoster]

i have one table with the following fields among others:

vehicle plates, previous kms, current kms, date, location. i have created a
query that draws all data from my table and it works fine.
i have also created another query with a calculated field that adds one
month to the date mentioned above.
my aim is to show the current kms of the month as the previous kms of the
following month.
i have tried to link the two queries but instead of returning 200 records i
get 974 records.
how can i solve this problem? do i need the two queries?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Go to this site, it describes how to get the previous row's (record's)
info.

http://support.microsoft.com/support/kb/articles/q101/0/81.asp

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSbmQzYechKqOuFEgEQJBAgCfcLY75dkDSspGgeXUbRpIscARkKUAniLN
bguVXh7NC2LJA5h5+rvpto9C
=cXxo
-----END PGP SIGNATURE-----

 

[angie]

thannk gor the reply, this works fine if there is a primary key in the table
and data are entered by users in a sequence. unfortunately there is no
primary key in the table and many users enter data randomly. e.g. a user may
enter data for january 2009 and another user may enter data for december
2008.
could i use this function to retrieve data from e.g.
year:2009, month: jan, starting kms: 5000, current kms: 10000, plates:
YAB3658, location: crete
i want to retrieve for this record in a calculated field the "current kms"
of december 2008 (provided that the fields "plates" are the same).

could i achieve that?

Ο χÏήστης "MGFoster" έγγÏαψε:

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yes, that function can do that. It doesn't matter what sequence the
users enter the data. All tables should have a Primary Key (PK). It
looks like the the PK might be the date, plates and location columns
(fields) if they uniquely identify the rows (records).

Just use the DLookup() function, as described in the KB article, as one
of the columns in a query.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSbphLIechKqOuFEgEQI9JQCfcy1WDzSJxvWf5CiLSOcvwR5FpV4An0Zv
+CF4pqsyQO2bibchfbj1AWJe
=rPoJ
-----END PGP SIGNATURE-----

 

[angie]

ok, i have created a primary key for my table, but it is an expression. i
have entered the following function and it returns an error. can you help me
fix it?

Expr3: DLookUp("[current kms]";"EXPENSES-TBL-3";" [EXPENSES-TBL-3].Expr1=" &
[EXPENSES-TBL-3].[Expr1]-1)

Ο χÏήστης "MGFoster" έγγÏαψε:

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can't have any expression in a table. I suspect that you've created
the expression in a query. Your criteria in the DLookup() function is
meaningless because it is comparing a value to itself minus 1. It
should be comparing the DLookUp()'s column value to the main query's
column value. E.g. (in the SQL view):

SELECT

DLookUp("[current kms]", "[Expenses-Tbl-3]", "Expr1=T1.Expr1-1") As
PreviousValue

FROM [Expenses-Tbl-3] As T1
WHERE <criteria>

In the FROM clause, the "As T1" is an alias. The alias is used in the
DLookUp() function to compare the column value the DLookUp() function
reads to the column value the main query reads.

You need to read the Access Help article on the DLookUp() function. An
easy way to get the article is to open the Debug window (Ctrl-G); type
in the function name; put the cursor on the name and hit the F1 key.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSbrdD4echKqOuFEgEQLjRQCfS2Plyn6QFzjdv8YqRPnFKmovuzoAn08e
4PkXPi3AqSeWfnkqEeSz4O7w
=p9pO
-----END PGP SIGNATURE-----

ok, i have created a primary key for my table, but it is an expression. i
have entered the following function and it returns an error. can you help me
fix it?

Expr3: DLookUp("[current kms]";"EXPENSES-TBL-3";" [EXPENSES-TBL-3].Expr1=" &
[EXPENSES-TBL-3].[Expr1]-1)

Ο χÏήστης "MGFoster" έγγÏαψε:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yes, that function can do that. It doesn't matter what sequence the
users enter the data. All tables should have a Primary Key (PK). It
looks like the the PK might be the date, plates and location columns
(fields) if they uniquely identify the rows (records).

Just use the DLookup() function, as described in the KB article, as one
of the columns in a query.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSbphLIechKqOuFEgEQI9JQCfcy1WDzSJxvWf5CiLSOcvwR5FpV4An0Zv
+CF4pqsyQO2bibchfbj1AWJe
=rPoJ
-----END PGP SIGNATURE-----