Subquery select field from previous record

[ut4me]

I have the following table setup
Table Name - tblInventoryCounts
Field Names - Store Number, Count Date, Item name, Count Amount

Sample Data
Store Number Count date Item Name Count Amount
9 1/4/2010 Gear 10
9 1/11/2010 Gear 18
9 1/18/2010 Gear 18

I have a parameter query where the user is asked for the store number and
Count Date. I need for the query to return the information for the date
entered and the Count Amount from 7 days prior as "Previous Count". So, if
the user entered a value of 9 for Store Number and 1/11/2010 for the Count
Date; the result should me

Store Number Count date Item Name Count Amount Previous
Count
9 1/11/2010 Gear 18
10

I have tried the following as an expression....

(select tblInventoryCount.[Count Amount] from tblInventoryCount where [Count
Date:] = dateadd("d",-7,[Count date:]))

but I can't get it to work....any ideas? Thanks for your help....

 

[KARL DEWEY]

You say 'subquery' but how is it related to the main query? Post the main
query SQL.
What about store number criteria?

 

[John Spencer]

If your are using the sub-query in the select clause your expression should be
along the lines of the following:

SELECT First([Count Amount])
FROM tblInventoryCount as TEMP
WHERE Temp.StoreNumber = tblInventoryCount.StoreNumber
AND Temp.[Count Date] = DateAdd("d",-7,tblInventoryCount.[Count Date]

Another way to handle this would be to have a slightly different sub-query in
the FROM clause.

Also, this relies on the inventory count being taken every seven days. If you
want the previous inventory count whether it is 6 or 7 or 8 days prior that
can be done with a series of queries. You could do it in one query if your
field names did not contain spaces.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[ut4me]

Here is my SQl for the whole statement, the store number is always a
numerical value that the user will enter when prompted. Thanks for your
help....

SELECT tblInventoryCount.[Store Number], tblInventoryCount.[Count Date],
tblInventoryCount.[Item Number], tblInventoryItems.[Item Name],
tblInventoryCount.[Count Amount], [TRANSFERS IN QUERY].[SumOfTransfer Item
Quantity], [TRANSFERS OUT QUERY].[SumOfTransfer Item Quantity], (select
tblInventoryCount.[Count Amount] from tblInventoryCount where [Count Date:] =
dateadd("d",-7,[Count date:])) AS Expr1
FROM ((tblInventoryItems INNER JOIN tblInventoryCount ON
tblInventoryItems.[Item Number] = tblInventoryCount.[Item Number]) LEFT JOIN
[TRANSFERS IN QUERY] ON tblInventoryCount.[Item Number] = [TRANSFERS IN
QUERY].[Transfer Item Number]) LEFT JOIN [TRANSFERS OUT QUERY] ON
tblInventoryCount.[Item Number] = [TRANSFERS OUT QUERY].[Transfer Item Number]
WHERE (((tblInventoryCount.[Store Number])=[Store Number:]) AND
((tblInventoryCount.[Count Date])=[Count Date:]));

You say 'subquery' but how is it related to the main query? Post the main
query SQL.
What about store number criteria?

--
Build a little, test a little.

I have the following table setup
Table Name - tblInventoryCounts
Field Names - Store Number, Count Date, Item name, Count Amount

Sample Data
Store Number Count date Item Name Count Amount
9 1/4/2010 Gear 10
9 1/11/2010 Gear 18
9 1/18/2010 Gear 18

I have a parameter query where the user is asked for the store number and
Count Date. I need for the query to return the information for the date
entered and the Count Amount from 7 days prior as "Previous Count". So, if
the user entered a value of 9 for Store Number and 1/11/2010 for the Count
Date; the result should me

Store Number Count date Item Name Count Amount Previous
Count
9 1/11/2010 Gear 18
10

I have tried the following as an expression....

(select tblInventoryCount.[Count Amount] from tblInventoryCount where [Count
Date:] = dateadd("d",-7,[Count date:]))

but I can't get it to work....any ideas? Thanks for your help....

 

[John Spencer]

Your sub-query should read more like the following:

(SELECT First([Count Amount])
FROM tblInventoryCount as TEMP
WHERE Temp.StoreNumber = tblInventoryCount.StoreNumber
AND Temp.[Count Date] = DateAdd("d",-7,tblInventoryCount.[Count Date])) as Expr1

You could use your parameters to do this also, but that would not be as
efficient. Especially if you decided you wanted to do a date range or more
than one store.

(SELECT First([Count Amount])
FROM tblInventoryCount as TEMP
WHERE Temp.StoreNumber = [Store Number:]
AND Temp.[Count Date] = DateAdd("d",-7,[Count Date:])) as Expr1


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Here is my SQl for the whole statement, the store number is always a
numerical value that the user will enter when prompted. Thanks for your
help....

SELECT tblInventoryCount.[Store Number], tblInventoryCount.[Count Date],
tblInventoryCount.[Item Number], tblInventoryItems.[Item Name],
tblInventoryCount.[Count Amount], [TRANSFERS IN QUERY].[SumOfTransfer Item
Quantity], [TRANSFERS OUT QUERY].[SumOfTransfer Item Quantity], (select
tblInventoryCount.[Count Amount] from tblInventoryCount where [Count Date:] =
dateadd("d",-7,[Count date:])) AS Expr1
FROM ((tblInventoryItems INNER JOIN tblInventoryCount ON
tblInventoryItems.[Item Number] = tblInventoryCount.[Item Number]) LEFT JOIN
[TRANSFERS IN QUERY] ON tblInventoryCount.[Item Number] = [TRANSFERS IN
QUERY].[Transfer Item Number]) LEFT JOIN [TRANSFERS OUT QUERY] ON
tblInventoryCount.[Item Number] = [TRANSFERS OUT QUERY].[Transfer Item Number]
WHERE (((tblInventoryCount.[Store Number])=[Store Number:]) AND
((tblInventoryCount.[Count Date])=[Count Date:]));

You say 'subquery' but how is it related to the main query? Post the main
query SQL.
What about store number criteria?

--
Build a little, test a little.

I have the following table setup
Table Name - tblInventoryCounts
Field Names - Store Number, Count Date, Item name, Count Amount

Sample Data
Store Number Count date Item Name Count Amount
9 1/4/2010 Gear 10
9 1/11/2010 Gear 18
9 1/18/2010 Gear 18

I have a parameter query where the user is asked for the store number and
Count Date. I need for the query to return the information for the date
entered and the Count Amount from 7 days prior as "Previous Count". So, if
the user entered a value of 9 for Store Number and 1/11/2010 for the Count
Date; the result should me

Store Number Count date Item Name Count Amount Previous
Count
9 1/11/2010 Gear 18
10

I have tried the following as an expression....

(select tblInventoryCount.[Count Amount] from tblInventoryCount where [Count
Date:] = dateadd("d",-7,[Count date:]))

but I can't get it to work....any ideas? Thanks for your help....