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
584 Views

Similar Articles

[PageSpeed] 18

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:

Extender field report writer
How I can add a extender field in report writer (payroll check) You will need to create a view from the Extender Field, then creat an External Report. Mick "Yaritza Andino" wrote: > How I can add a extender field in report writer (payroll check) > > ...

Using combobox results when bound field is null
Access 2003: This combobox is a list of all the teachers, and also displays the number of children, and the number of families assigned to each teacher. Some children do not have a teacher assigned yet. The drop-down list, and the query it is based upon, correctly displays a null in the Teacher column, and the correct count of children and families in columns(1) and (2). However, when I look at the value of cboTeachers.Column(1) or cboTeachers.Column(2) in the Immediate window, the Locals window, or passed in a Message Box, they all show up as null values. I'd like to get a coun...

Why is Actual Work updated when entering time into Work Field?
We're using MS Project 2007 and seeing that sometimes when time is entered into the Work field for a task, that task's Actual Work is updated to the same value entered in the Work field. This happens to multiple resources, but only on some of their tasks and not on others. For example, for the weeks of 1/24 and 1/31, whatever hours I schedule (enter in the "work" cell in the Resource Usage View) for a given task also appears in the "actual" cell. The tasks are setup up differently. Some are Fixed Work, some are Fixed Duration. I believe all have the e...

Year to Date subquery
HI I would like assistance with creating a year to date subquery. I tried to copy the MS help example but still can't get the query to run. I have the following: Table name Orders Field Names- [ID] ( system generated primary key) , [OrdersDate], [Quantity], [UnitPrice] Query SQL - SELECT Year([Orders].[OrderDate]) AS TheYear, Month([Orders].[OrderDate]) AS TheMonth, Sum([Orders].[Quantity]*[Orders].[UnitPrice]) AS MonthAmount, (SELECT Sum(dupe.Quantity * dupe.UnitPrice) AS YTD FROM Orders AS A INNER JOIN [Orders] AS dupe ON orde...

Selective Editing of Nickname list
Anyone know if there's anyway to selectively purge or add to the *.nk2 file that is built by outlook to autocomplete e-mail addresses? I know how to start from scratch but it sure would be nice to edit the list. Thanks John K To remove a single entry, * start a new message * type the partial name you want removed * when outlook suggests a name, use the arrow keys to highlight the entry and press the delete key /neo ps - steps above are for outlook 2002/2003. "John K" <anonymous@discussions.microsoft.com> wrote in message news:1e11201c45554$28f11ac0$a601280a@phx.gb...

Record Locking
Hi, I have found that if 2 users open the same account there is no warning that the record is in use. As a result both parties can edit the same details at the same time. The last person that updates the record overwrites any information that the 1st person has updated. Is this a known issue, does anybody know if this has been resolved in the next release ? Thanks in advance Julie Is this a known issue? Yep. In the training they sort of glance over it but it is a problem. Is it fixed in the new version? Dunno yet. :) "Julie Wiatr" wrote: > Hi, > > I have found...

allow discount date and due date criteria for selecting checks
In the select checks window, the discount dates and due dates options are an either/or option. Both of these criteria should be allowed as it can allow companies to better utilize cash flows. This will automate the activity instead of depending on the user to manage the selected invoices that would be within the dates selected. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to o...

cell selection stuck on
Hi, while working in excel, occasionally the cell selection gets stuck on, so moving the mouse/cursor highlights cells even though I dont want it to. closing excel does not help, I have to reboot to stop it happening, then it comes back. it is a large spreadsheet. argh! need help! thanks brian. Hi Brian! A frequently experienced problem. Several possible solutions: 1. Once in this mode you'll probably see EXT in the status bar of XL, press F8 and see if it goes away. 2. Sometimes clicking the wheel also helps. 3. Check you haven�t got a sticky key on your key...

SubQuery
Hi, I try to build the following query select * from tableA where tableA.id not in (select id2 from tableB where tableB.columnA = 'value') with a mscrm query-/conditionexpression. Can anyone help me, please ? Thx, Simone maybe this will help you http://www.stunnware.com/crm2/topic.aspx?id=FindingData5 On 24 ene, 17:46, "mo" <m...@gmx.de> wrote: > Hi, > > I try to build the following query > > select * from tableA where tableA.id not in (select id2 from tableB > where tableB.columnA = 'value') > > with a mscrm query-/conditi...

Tranfer Custom Field during LEAD conversion
Hi, I add few custom field to LEAD before importing data from a TXT file source. I publish the new information and I restart IIS without problems. Then I go on with data import and I map every field (including custom one) to MSCRM ones. So I populate the CRM database with several LEAD. How can I mantain the informations in the LEAD custom field when I convert these in Account? Thanks... Not sure what you are asking...But if you add the fields to the lead entity and import the data to those fields they will remain in the lead entity. When the lead is converted to an Account the lead...

How to display selection handles
Hello all, A quick question .. I have been developing Windows Apps for some time, but heretofore have not had the requirement to display graphics in a document view that have to be selectable via a left mouse click. The standard Windows procedure to to left click the graphic and have the graphic highlighted via display of the familar selection rectangles. These selection rectangles are typically small filled black rectangles on the periphery of the graphic. For a line, these selection rectangles are at the endpoints. For a rectangle, the selection rectangles are at each rectangle ...

Insert calculation fields
I export a unit forecasting spreadsheet from CRM to Excel and then create a pivot table off the data. I want to add two calculation fields to the CRM database so I don't have to insert the columns and then calculate in Excel. The formula will be very simple like H2*H4/100 and then H3*H4/100. Is this an easy thing to do? Seems like this would be in the manual...but is not. Thank you Hi SJW What youcould dois add to new attributes to the form and perform the calculation in Javascript either on the onsave or the onload, or the onChange of the fields. This would populate the new...

Lookup to create a table of data in terms of selection
my data looks like these in columns B to Q, I need to generate a list in terms of Sales Rep. and print for each one at a time. I have a drop down list (data validation) in cell AD3, with the choises of all Sales Rep. I would like to setup a table below with lookup equations, so that whichever Sales Rep is selected in AD3, only values for that Sales Rep will be shown in a table below that. (something like the table at the bottom of this page) heading1 heading2 heading3 heading4 heading5 heading6 Sales Rep. name 1 Company 121 556 50 5463 ...

Select Case Options
Hi, Apart from making really long hard to read If statements, are they any options open to me? I do not use excel a lot, so i may be missing something obvious. I was thinking the code could be made into a custom sub/macro, but not sure if that's the way to go. Any advice? Thanks! jody' i want c cells contents to be based on the b cell as below for b1... -> 1000 select case row.column case 30 to 40 row.column+1 = 300,000 case 41 to 50 row.column+1= 500,000 case 51 to 60 row.column+1= 700,000 case 61 to 70 row.column+1= 700,000...

select class by combobox??
I'm wirting application which will be "host device" or "peripheral device" Program will be to perform two roles. I choose it when I starting application. And in first case I use some other functions which I will not use in second case and vice versa. So I thing that I can use two class for each case separately. And I want choose class which I want use by combobox... How can I do this ? e.g. class One { some function and variables } class Two { some function and variables } //select class - c_number_class is variable of combobox int nIndex = c_number_class.GetCurS...

Code when some fields have no data.
I am using Access 2002. I am creating a report and have three fields I need to list with semicolons between them but they don't all have data in them. They are txtPrimaryCompetency, txtSecondaryCompetency and txtSupplementalCompetency. I've been trying to figure out how to list them and not have the semicolon appear if one or two of them have no data. I have tried using Not Null but can't get it to work. I tried using Len but I couldn't get that to work either. I am a beginner to programming and am a bit frustrated with trying to get this to work. I hope so...

Selecting Multiple Bills for Entry
Hello all. I tried searching for any past posts on this, but the search tool is basically useless. Previous releases allowed the user to select multiple bills, then hit the "Enter in Register" button. Money would then cycle through them, one after the other. Starting with Money 2002 or 2003, that has gone away. How do we ask the developers to put that back in? It was a huge time saver when I sat down to write my bills out. Now I have to go one-by-one and make a lot of mouse clicks. Thanks for you time. In microsoft.public.money, longtimeuser wrote: > > >I tr...

Subject field being hidden when writing new e-mail
I'm running Windows 2000 and Outlook 2000. When I go to write a new e-mail only the To section and message area are shown and not the Subject section. If I click on the To button and add people to the Bcc the rest of the sections (CC,Bcc and Subject) will display. How do I fix it so that the subject area will display by default all the time? I have not made any changes to the form. Geoff When you start a new message, select View | Message header. "Geoff" <gcurrey@yahoo.com> wrote in message news:013701c3641a$586817d0$a101280a@phx.gbl... > I'm running Window...

Msg 130 Subquery Error
I'm using SQL 2008 and I have the following TSQL code that works fine if you remove the outer SUM function, but I would like to sum the "NumOverLap" field. As it stands, it just returns "1" for each line instead of summing the "1's" for a particular group: ------------Begin------------- SELECT top 5 a.EmpID, a.EmpName, a.StoreNum, a.Date, a.TotalHours, Sum( case when SUM( case when a.[EmpID]<> b.[EmpID] then case when b.[TotalHours] < 4.5 then 0 else case when b.[StartTime] > a.[StartTime] then DateDiff(n,b.[Sta...

RMS POS Find Customer won't remember search fields
I've never had this problem before. The Find Customer form won't remember the search fields from session to session. Within any given session it remembers from the previous search. Exit POS and they're gone the next time you log in. HQ Manager has always been this way, but RMS POS has always remembered which fields were selected in the Find Customer window. Suggestions? Tom -- Stop fishing for e-mail check that u have full access to the RMS registry key -- Ahmed Nashat http://ahmed.nashat.googlepages.com "Terrible Tom" <tomg@GOFISHrobysfurniture.com> w...

In Excel 2000, How do you select the whole of a worksheet (Select.
In Excel 2000, How do you select a whole worksheet (Select All) with ONE click of the mouse? Cheers!! click on that little grey button to the right of the column headers and at the top of the row headers. Using the keyboard (you didn't ask!). Ctrl-a (twice in xl2003.) Rascal wrote: > > In Excel 2000, How do you select a whole worksheet (Select All) with ONE > click of the mouse? Cheers!! -- Dave Peterson ...

Setting Report field properties on open
I am trying to set a field so it does not get printed in a report when it opens. I used to OpenARG statement and it passes the variable fine. However when trying to turn the field visiblity to false I get an error. Strange????? Can you change report fields visibility so certain fields do not get printed on the fly like this? I tried this with no luck. Private Sub Report_Open(Cancel As Integer) If Not IsNull(Me.OpenArgs) Then InputArgs = Me.OpenArgs Select Case InputArgs Case "NoValues" txtUM_QTY.Report.Visible = False '&l...

Source for Table and Field Descriptions
Is there a good source for table and field descriptions that actually say what the data fields and tables hold. For example I am trying to figure out what the field IV00108.UOMPRICE contains. Regards This is a price list table and that field is the unit price at the listed UOM. The table and field names are listed in the Resource Descriptions under the Tools menu. But all that is is a list of the tables and a list of the field. Our book Information Flow and Posting takes each transation in the application and lists the type of data that goes into or through each table when the transac...

Attachment Selection
When composing a new mail message there are buttons to use to select the following: To: Cc: and Bcc: as well as a box to type in the Subject. We used to have an additional button to select a file to ATTACH to the message as well. And the attached files would appear in the message header. That option has disappeared, and we can now only INSERT using the Insert on the Menu or the Paperclip; with the inserted files appearing within the body of our message. We would like to be able to have the Attachment Selection button and box back. Why is it no longer available, and how can be get ...

Select every 10th number in a list
I have a long list of numbers in column A. I'd like to select every 10t number down the list. ie the 10th number, 20th, 30th etc I'm using the formula... =INDEX(A:A,ROW()*20) which just produces a ro of zeros. Any ideas -- judois ----------------------------------------------------------------------- judoist's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1059 View this thread: http://www.excelforum.com/showthread.php?threadid=48792 If you're copying the formula along a row, you need to make the reference to Column A absolute: =INDEX($A:$A,ROW()*...