Subquery select field from previous record

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....
0
Utf
1/7/2010 10:16:01 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
738 Views

Similar Articles

[PageSpeed] 2

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" wrote:

> 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....
0
Utf
1/7/2010 11:41:01 PM
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 wrote:
> 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....
0
John
1/8/2010 1:39:25 PM
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" wrote:

> 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" wrote:
> 
> > 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....
0
Utf
1/8/2010 1:51:01 PM
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

ut4me wrote:
> 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" wrote:
> 
>> 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" wrote:
>>
>>> 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....
0
John
1/8/2010 3:27:30 PM
Reply:

Similar Artilces:

instructions disppear when users begin type (text field)
Hi all, I need to customize the outlook contact form and I want to add one text field to allow users to add details info and instruct users how to add. Instructions shows in the field and the instructions disappear when users click and begin to type. How should I do this? exchange 2003/outlook2003 Thank you. It's hard for me to visualize exactly what you're expecting to happen. If you want the instructions to stay on the screen, you could display them in a label control. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook 2007 Programming: Jumps...

Updating Boolean Field with onChange
I am having difficulty with setting a value of a new boolean field using the OnChange scripting against a picklist. If anyone has some sample code / formatting information that I can use it would be greatly appreciated. Also, I also need to know if it is possible to stop the default value for the Revenue (isrevenuesystemcalculated) field on the opportunity form being set to "Yes" when the "Convert Lead" function is used. We can set the default value to "No" for user created Opportunities but when Convert Lead is used it seems to ignore the default value. Again, a...

When hitting reply to a message, no "Bcc:" header field appears
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: Exchange Hi. When I hit &quot;Reply&quot; or &quot;Reply All&quot; to a message in Entourage, the composition window that appears has the usual &quot;To:&quot;, &quot;Cc:&quot;, and &quot;Subject:&quot; form fields near the top, but not one for &quot;Bcc:&quot;. I've looked all over Preferences but don't see an option for this. What am I missing? <br><br>Thanks, <br><br>-c On 2/26/10 11:51 AM, cmetzler@officeforma...

recording changes
I have a field called "TimeStamp" What I would like is that if someone modifiys or make any change in the form that the "TimeStamp" will record the time and date of change. How can this be achieved? sandrao In an event procedure in the form's AfterUpdate event, you could do something like: Me!txtYourTimeStampField = Now() Note that if you use the BeforeUpdate event, every time your code changes the value of this field, the form tries to update, the BeforeUpdate (re-)triggers, and you "loop up" (at least, it seems to me I've seen this...)...

Fields
I am working in an education template and want to see if there is a way to create a field that can be added to periodically to show all the courses a person takes from my instructors. I created a field named course but it is only allowing me to put one course in. How can I make the field list all the courses? Thanks, Nantika -- Enjoy every day! On Sat, 17 Apr 2010 09:18:01 -0700, Nantika <Nantika@discussions.microsoft.com> wrote: >I am working in an education template and want to see if there is a way to >create a field that can be added to periodically ...

Possible to Look Up Records by Initials?
I have a user who's telling me that prior to this weekend in CRM, she could lookup user records by initials rather than typing in part of the name. I don't see where that setting would have been marked in CRM. Has anyone else heard of this? If so, how did you implement this type of search customization/configuration? Steps to recreate problem: Account screen Account Owner field's magnifying glass Assign Account Assign to another user fields magnifiying glass Look Up Records' Look For field = initials of user you're attempting to Find. I doubt you can use the init...

How to record percentage increases in materials costing sheet.
I am trying to do a materials costing sheet where I would like to be able to periodically increast the base cost of the product by different percentages. E.g. Cost of Apples $10.00 but might be increased by 5% then 2.5% and therefore the base cost would keep changing. I know how do first % increase but not how to then use that figure as the base figure for future adjustments. Thanks for amy help. I am very new to this. You could put a % in one cell. Select the cell and give it a name by Insert-->Name-->Define. Call it "increase". Then, in the cell where you put the f...

HOWTO create a Mail Merge Template in MSCRM with multiple child records
Hi, I have created successfully a basic Mail Merge template in MS Word in Dynamics CRM 4.0 for Order Entity. My challenge now is how to design a template that retrieves the order details (product and prices) related to the order record? This sounds like a parent-child template. Any idea? On Jun 2, 1:26=A0pm, "Benjie Fallar III" <bfall...@hotmail.com> wrote: > Hi, > I have created successfully a basic Mail Merge template in MS Word in > Dynamics CRM 4.0 for Order Entity. > My challenge now is how to design a template that retrieves the order > details (prod...

Default value for custom field?
How can I populate a custom field with a default value? Specifically, I have a custom field "DisplayName" associated with the Quote Detail object. I want initially to populate this field with the value of the product name field when a new product is added to a quote. The user can then edit the DisplayName custom field if desired. The DisplayName custom field will be used as the product name on a Crystal Reports quote form. ...

Replace null value with the previous value?
I have a database that was just imported that has approximately 388000 records. The problem is that there is information about a person in multiple different records but the name did not come across with each record. (So I have 10 records with information for a certain name, but the name only appears in field 1 of the first record and not the subsequent 9, etc.) I need to create a query or expression that will fill field 1 with the preceding value if it is null. This way I will have all the information for field 1 in a manner that I can link and combine data. Simply I need to fil...

modify description field in reports
When I print my PO's I can make the part # field in the view report screen wider but when i print it, it defaults back to the regular size. The problem is it cuts off my part numbers on some of my long numbers. Is there a way to overcome this? you must modify the po.xml file directly. "www.choppersapparel.com" <www.choppersapparel.com@discussions.microsoft.com> wrote in message news:8B4E31D5-10A7-4A99-913D-F8F6E3BD7043@microsoft.com... > When I print my PO's I can make the part # field in the view report screen > wider but when i print it, it defaults ...

How do you change a field name in 2002 Excel
I have copied and pasted a whole database from Works into the Excel program but can't seem to find a way to change the field names from A, B, C, to what I want as Last Names, First Names, etc. Aarrrrgh. It can't be THAT difficult! <G> Using the HELP did nothing for me thus I am here asking this silly Q. AnnE in MN You cannot change the Column letters from A, B, C etc. You can choose to have column and row headers not shown under Tools>Options>View Enter your titles(names) in row 1 then select A2 and Window>Freeze Panes to lock row 1 in view. Gord Di...

Multiple indexes on same fields in different order
I sometimes need to find the largest or smallest value in a field. If I put two indexes on the same field, one in ascending order and one in descending order, is Jet smart enough to use the proper index if a give it a grouping query with the condition Max or Min, or am I just wasting resources? Pete -- This e-mail address is fake, to keep spammers and their address harvesters out of my hair. If you want to get in touch personally, I am 'pdanes' and I use yahoo mail. But please use the newsgroup when possible, so that all may benefit from the exchange of ideas. ...

Recording ownership in an LLC partnership
I'm a partner in an LLC company and would like to track my ownership interest in Money 2004. It's not "real" obvious how I might go about this, particularly ... What account type should be used? How do I record my portion of the profits earned, for which I'm am personally taxed? How do I record the gain in ownership value these profits provided when I haven't necessarily received them in cash? How do I record a portion of these profits as received in cash when they aren't dividends but distributions? I'd prefer to use an investment oriented approach rather ...

Can you record a macro in Publisher like you can in Excel?
In Excel I use the feature to Record a Macro. Excel records the keystrokes and converts to VB. Does Publisher have that option? In my version, Publisher 2003, I can Create a Macro, and it takes me into VB. However, I don't know how to code VB. Darlene wrote: > In Excel I use the feature to Record a Macro. Excel records the keystrokes > and converts to VB. Does Publisher have that option? In my version, > Publisher 2003, I can Create a Macro, and it takes me into VB. However, I > don't know how to code VB. Publisher does not have a Macro Recorder, sorry. --...

Change Backcolor for the selected line
I would like to be able to highlight or change the color of a single line on a subform formated as a continuous form. The user selects one line from many possible lines on a continuous form to respond to. When he selects the line another pop-up form is display which the user must complete. I would like to maintain some kind of highlight (backcolor?) so that the user can clearly see which line he is reponding to. Setting the backcolor after selecting a line sets the back color for the entire form, which really defeats the purpose. Any help? -- Message posted via AccessMons...

Multi-Select Listbox VBA code causing overflow in loop
Private Sub Form_Current() Dim oItem1 As Variant Dim oItem2 As Variant Dim bFound1 As Boolean Dim bFound2 As Boolean Dim sTemp1 As String Dim sTemp2 As String Dim sValue1 As String Dim sValue2 As String Dim sChar1 As String Dim sChar2 As String Dim iCount1 As Integer Dim iCount2 As Integer Dim iListItemsCount1 As Integer Dim iListItemsCount2 As Integer sTemp1 = Nz(Me!SignsList.Value, " ") sTemp2 = Nz(Me!SymptomsList.Value, " ") iListItemsCount1 = 0 iListItemsCount2 = 0 bFound1 = False bFound2 ...

Sorting records in the Report Design View
Currently, I use "Sorting and Grouping" tool in the Report Design View to create group headers so that I can group my data accordingly. However, I would like to know if I can apply a custom sort order. For example, the field name that I want to group is "Region". I have records of "East", "North", "South", and "West". Is there a way that I can order by "North", "South", "East", and "West" instead? If I use the "Sorting and Grouping" tool, it won't work because it only accepts a...

records in current month
Hi I want to use a query that returns records in the current month from a date field, but cant work the query out - anyone help please Thanks Alec Set the criteria under the date field to: Between Date()-Day(Date())+1 AND DateSerial(Year(Date()),Month(Date())+1,0) -- Duane Hookom Microsoft Access MVP "alecgreen" wrote: > Hi > > I want to use a query that returns records in the current month from a > date field, but cant work the query out - anyone help please > > Thanks > > Alec > > ...

Pivot table field dropdown list reset #2
I would like to know if there is a way to reset a field dropdown list in a pivot table. In some field sometimes I delete data from the source and some items of the field will no longer be there. However, when I refresh the pivot table the dropdown list will still show the old items even if they're not in the source data anymore. I really need your help with this. Thanks a lot in advance for your help, Francisco Molina ...

Select more than 1 object in the lookup field
I would like to have the ability to select more than one object in a lookup field. Is there a way to accomplish this? Thanks No. The only exception to this is the party list (like the to, cc and bcc fields in an email), but this is an internal thing and cannot be used in customizations. -- Michael H�hne, Microsoft Dynamics CRM MVP Web: http://www.stunnware.com/crm2 Feed: http://www.stunnware.com/crm2/atom.aspx Custom Lookup Dialog: http://www.stunnware.com/crm2/?area=customLookup ---------------------------------------------------------- "Onetreeup" <Onetreeup@discussions...

granting users Send as permissions causes errors unless they select the FROM address from the GAL
I've got some users who have two mailboxes, each with a different address. They need to be able to select which address they send from, on occasion. I've given them "send on behalf" and "send as" permissions the 2nd accounts. But, unless they select the FROM address from the GAL, they get the following error: You do not have permission to send to this recipient. If they manually type in the address, they receive this error. This is only a problem because I would prefer to hide the 2nd account from the GAL, to avoid confusion. Does anyone know a way around this?...

Keep data that is entered in a field until changed
Access 2000, I want to retain a field contents that is entered, until it is changed. Like a default, except that it will only change whenever the user changes the field contents. How do I accomplish that? "lths-kblank" wrote: > Access 2000, I want to retain a field contents that is entered, until it is > changed. Like a default, except that it will only change whenever the user > changes the field contents. How do I accomplish that? If you set a default value for a field, the field will retain that value. If you change the value, the field will save the change. ...

Show me these records
Ok, I guess I didn't really post what I needed earlier. I need to see the info in column a,b,c,d,e,f where column "c" contains street name of "ivycrest" and I have multiple streets. How do I do this? Please help? MS Access has records and MS Excel has columns. You would use a Query in MS Access and Find in MS Excel. Tammy wrote: > Ok, I guess I didn't really post what I needed earlier. > > I need to see the info in column a,b,c,d,e,f where column "c" contains > street name of "ivycrest" > > and I have multiple st...

Record of attached file
Does anybody know how to make Outlook 2002 automatically insert the file name (of the file that is attached to the e-mail) at the bottom of the body of the message? example: <<test.xls>> Outlook Rich Text - files inserted in the body where the insertion point it. Plain Text - files inserted in window below the message body. HTML - files inserted in header just below the subject. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted ...