Subquery select field from previous record

  • Thread starter Thread starter ut4me
  • Start date Start date
U

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....
 
You say 'subquery' but how is it related to the main query? Post the main
query SQL.
What about store number criteria?
 
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
 
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:]));


KARL DEWEY said:
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.


ut4me said:
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....
Click to expand...
Click to expand...
 
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:]));


KARL DEWEY said:
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.


ut4me said:
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....
Click to expand...
Click to expand...
Click to expand...
 
Back
Top