Reference Previous Record 10-16-07

I'm trying to calculate a backlog (Amount of previous days backlog + amount 
received today - Amount processed today= Backlog). Currently I have a query 
that calculates amount received - amount processed, but I need it to 
reference the previous days backlog to add that to the equation. Is that 
possible?
0
Utf
10/16/2007 10:27:02 PM
access.queries 6343 articles. 1 followers. Follow

7 Replies
989 Views

Similar Articles

[PageSpeed] 10

Yes, it is possible, but how you do it will depend on your table structure 
and what you want to do with the information?  How do you want to display 
this information; in the results of a query, in a cell on a form, or some 
other method.

-- 
Email address is not valid.
Please reply to newsgroup only.


"Whitney" wrote:

> I'm trying to calculate a backlog (Amount of previous days backlog + amount 
> received today - Amount processed today= Backlog). Currently I have a query 
> that calculates amount received - amount processed, but I need it to 
> reference the previous days backlog to add that to the equation. Is that 
> possible?
0
Utf
10/17/2007 11:52:01 AM
Ultimately I would like it to display on a report or subreport.

"Dale Fye" wrote:

> Yes, it is possible, but how you do it will depend on your table structure 
> and what you want to do with the information?  How do you want to display 
> this information; in the results of a query, in a cell on a form, or some 
> other method.
> 
> -- 
> Email address is not valid.
> Please reply to newsgroup only.
> 
> 
> "Whitney" wrote:
> 
> > I'm trying to calculate a backlog (Amount of previous days backlog + amount 
> > received today - Amount processed today= Backlog). Currently I have a query 
> > that calculates amount received - amount processed, but I need it to 
> > reference the previous days backlog to add that to the equation. Is that 
> > possible?
0
Utf
10/17/2007 4:02:05 PM
Still need to know your table structure (table name, field names) to help you 
write the queries for what you want.

Do you want this value on a row for every date?  What happens when your 
backlog goes away and you produce more than you need, do you want to track 
InStock as well?  Something like?

Date   Received_Today  Processed_Today   Backlog   InStock

Dale


-- 
Email address is not valid.
Please reply to newsgroup only.


"Whitney" wrote:

> Ultimately I would like it to display on a report or subreport.
> 
> "Dale Fye" wrote:
> 
> > Yes, it is possible, but how you do it will depend on your table structure 
> > and what you want to do with the information?  How do you want to display 
> > this information; in the results of a query, in a cell on a form, or some 
> > other method.
> > 
> > -- 
> > Email address is not valid.
> > Please reply to newsgroup only.
> > 
> > 
> > "Whitney" wrote:
> > 
> > > I'm trying to calculate a backlog (Amount of previous days backlog + amount 
> > > received today - Amount processed today= Backlog). Currently I have a query 
> > > that calculates amount received - amount processed, but I need it to 
> > > reference the previous days backlog to add that to the equation. Is that 
> > > possible?
0
Utf
10/17/2007 6:21:02 PM
Table Structure: Returns Table
Sign Out Date,  Sign Out Time, Sign In Date, Sign In Time, Warehouse, 
Received in Warehouse, Received in Customer Service, Batch Number, Amount 
Received, Batch Type, Agent Name, Amount Filed, Amount Sent to Accounting, 
Amound Pended

Yes, the value on the row for each date. If there is no backlog from the 
prior day that it should read 0 and there will only be a backlog the next day 
if the amount received that day is not processed. 

"Dale Fye" wrote:

> Still need to know your table structure (table name, field names) to help you 
> write the queries for what you want.
> 
> Do you want this value on a row for every date?  What happens when your 
> backlog goes away and you produce more than you need, do you want to track 
> InStock as well?  Something like?
> 
> Date   Received_Today  Processed_Today   Backlog   InStock
> 
> Dale
> 
> 
> -- 
> Email address is not valid.
> Please reply to newsgroup only.
> 
> 
> "Whitney" wrote:
> 
> > Ultimately I would like it to display on a report or subreport.
> > 
> > "Dale Fye" wrote:
> > 
> > > Yes, it is possible, but how you do it will depend on your table structure 
> > > and what you want to do with the information?  How do you want to display 
> > > this information; in the results of a query, in a cell on a form, or some 
> > > other method.
> > > 
> > > -- 
> > > Email address is not valid.
> > > Please reply to newsgroup only.
> > > 
> > > 
> > > "Whitney" wrote:
> > > 
> > > > I'm trying to calculate a backlog (Amount of previous days backlog + amount 
> > > > received today - Amount processed today= Backlog). Currently I have a query 
> > > > that calculates amount received - amount processed, but I need it to 
> > > > reference the previous days backlog to add that to the equation. Is that 
> > > > possible?
0
Utf
10/18/2007 3:58:02 PM
A couple more questions.

1.  I see you have a Warehouse field in your table.  Will the report you 
generate be for a single warehouse, or multiple warehouses, each on a 
separate "page"?

2.  For your report, I assume that you will be entering a range of dates 
(StartDate, EndDate) on a form to run this report.  Is that a valid 
assumption?

3.  Which fields are you trying to track as your received and processed 
columns?

Dale
-- 
Email address is not valid.
Please reply to newsgroup only.


"Whitney" wrote:

> Table Structure: Returns Table
> Sign Out Date,  Sign Out Time, Sign In Date, Sign In Time, Warehouse, 
> Received in Warehouse, Received in Customer Service, Batch Number, Amount 
> Received, Batch Type, Agent Name, Amount Filed, Amount Sent to Accounting, 
> Amound Pended
> 
> Yes, the value on the row for each date. If there is no backlog from the 
> prior day that it should read 0 and there will only be a backlog the next day 
> if the amount received that day is not processed. 
> 
> "Dale Fye" wrote:
> 
> > Still need to know your table structure (table name, field names) to help you 
> > write the queries for what you want.
> > 
> > Do you want this value on a row for every date?  What happens when your 
> > backlog goes away and you produce more than you need, do you want to track 
> > InStock as well?  Something like?
> > 
> > Date   Received_Today  Processed_Today   Backlog   InStock
> > 
> > Dale
> > 
> > 
> > -- 
> > Email address is not valid.
> > Please reply to newsgroup only.
> > 
> > 
> > "Whitney" wrote:
> > 
> > > Ultimately I would like it to display on a report or subreport.
> > > 
> > > "Dale Fye" wrote:
> > > 
> > > > Yes, it is possible, but how you do it will depend on your table structure 
> > > > and what you want to do with the information?  How do you want to display 
> > > > this information; in the results of a query, in a cell on a form, or some 
> > > > other method.
> > > > 
> > > > -- 
> > > > Email address is not valid.
> > > > Please reply to newsgroup only.
> > > > 
> > > > 
> > > > "Whitney" wrote:
> > > > 
> > > > > I'm trying to calculate a backlog (Amount of previous days backlog + amount 
> > > > > received today - Amount processed today= Backlog). Currently I have a query 
> > > > > that calculates amount received - amount processed, but I need it to 
> > > > > reference the previous days backlog to add that to the equation. Is that 
> > > > > possible?
0
Utf
10/18/2007 5:28:01 PM
See below

"Dale Fye" wrote:

> A couple more questions.
> 
> 1.  I see you have a Warehouse field in your table.  Will the report you 
> generate be for a single warehouse, or multiple warehouses, each on a 
> separate "page"? 

For this report the warehouse detail will not appear, I'm looking for a roll 
up sum of all returns received regardless of warehouse. A separate report 
will break down the amount received per warehouse.
> 
> 2.  For your report, I assume that you will be entering a range of dates 
> (StartDate, EndDate) on a form to run this report.  Is that a valid 
> assumption? 

Yes, I would like the report to show a weekly snap shot, or at minimum a 
daily report.
> 
> 3.  Which fields are you trying to track as your received and processed 
> columns?
Received - Received in Customer Service (Date) and Amount Received (sum of 
all received that date).
Processed - Sign In Date (Date Return was processed) and Amount Filed + 
Amount Sent to Accounting + Amount Pended (Sum of these three equals Amount 
Process for the Sign In Date).
> 
> Dale
> -- 
> Email address is not valid.
> Please reply to newsgroup only.
> 
> 
> "Whitney" wrote:
> 
> > Table Structure: Returns Table
> > Sign Out Date,  Sign Out Time, Sign In Date, Sign In Time, Warehouse, 
> > Received in Warehouse, Received in Customer Service, Batch Number, Amount 
> > Received, Batch Type, Agent Name, Amount Filed, Amount Sent to Accounting, 
> > Amound Pended
> > 
> > Yes, the value on the row for each date. If there is no backlog from the 
> > prior day that it should read 0 and there will only be a backlog the next day 
> > if the amount received that day is not processed. 
> > 
> > "Dale Fye" wrote:
> > 
> > > Still need to know your table structure (table name, field names) to help you 
> > > write the queries for what you want.
> > > 
> > > Do you want this value on a row for every date?  What happens when your 
> > > backlog goes away and you produce more than you need, do you want to track 
> > > InStock as well?  Something like?
> > > 
> > > Date   Received_Today  Processed_Today   Backlog   InStock
> > > 
> > > Dale
> > > 
> > > 
> > > -- 
> > > Email address is not valid.
> > > Please reply to newsgroup only.
> > > 
> > > 
> > > "Whitney" wrote:
> > > 
> > > > Ultimately I would like it to display on a report or subreport.
> > > > 
> > > > "Dale Fye" wrote:
> > > > 
> > > > > Yes, it is possible, but how you do it will depend on your table structure 
> > > > > and what you want to do with the information?  How do you want to display 
> > > > > this information; in the results of a query, in a cell on a form, or some 
> > > > > other method.
> > > > > 
> > > > > -- 
> > > > > Email address is not valid.
> > > > > Please reply to newsgroup only.
> > > > > 
> > > > > 
> > > > > "Whitney" wrote:
> > > > > 
> > > > > > I'm trying to calculate a backlog (Amount of previous days backlog + amount 
> > > > > > received today - Amount processed today= Backlog). Currently I have a query 
> > > > > > that calculates amount received - amount processed, but I need it to 
> > > > > > reference the previous days backlog to add that to the equation. Is that 
> > > > > > possible?
0
Utf
10/18/2007 6:12:00 PM
Maybe something like:

SELECT a.product, a.dateTime, LAST(a.anyOtherRequiredField), 
LAST(b.anyOtherRequiredField)

FROM (myTable AS a LEFT JOIN myTable AS b
                    ON a.product = b.product AND a.dateTime> b.dateTime)
                                        LEFT JOIN myTable AS c
                    ON a.product=c.product AND a.dateTime > c.dateTime

GROUP BY a.product, a.dateTime, b.dateTime
HAVING b.dateTime = MAX(c.dateTime)




where b.fieldName refers to the record with the closest dateTime, same 
product, that the one refered by a record with alias  "a".


Vanderghast, Access MVP

"Whitney" <Whitney@discussions.microsoft.com> wrote in message 
news:51DB059C-8F0B-4F74-AED0-3EA9CF8EE1B7@microsoft.com...
> I'm trying to calculate a backlog (Amount of previous days backlog + 
> amount
> received today - Amount processed today= Backlog). Currently I have a 
> query
> that calculates amount received - amount processed, but I need it to
> reference the previous days backlog to add that to the equation. Is that
> possible? 


0
Michel
10/18/2007 7:31:20 PM
Reply:

Similar Artilces:

Find the closest Matched Record
Wild card look up's. I want the user to be able to enter a number or part of a number and the system to find the first match or if it can't find a match to find the next one that is close. Can this be done. Below is my current code that works great if you have the exact number. Thanks Matt >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Private Sub LookUpSerialNo_AfterUpdate() ' Find the record that matches the control. Dim SID As String Dim stLinkCriteria As String Dim r...

Sales Process not appearing under Apply Rule 08-15-07
[posted and mailed] I have a user who lives within a subsidiary business unit who is trying to import a sales process workflow (for opportunities) for his team. The workflow wont appear in the apply rule dialog unless I (as an admin in the top-most business unit) import it myself, or, I promote the user into the parent B.U. and then have him import the sales process. The problem with this, even after I put him back into his own business unit, is that the rule doesn't move back with him, and appears for the entire organization. Am I missing something really obvious? What does it take to ...

Prenotes in ACH file in GP 10
When we create an ACH file in GP 10 and check the box to Include all Prenotes, the prenote records are not being included in the count. They are included in the record count, however. For instance, the record count on the "82" line shows 6 but we only have 4 "62" records. The other 2 should be prenotes. What do we need to do to fix this? Please disregard. This issue was fixed in the GP 10 year-end update. "Elaine" wrote: > When we create an ACH file in GP 10 and check the box to Include all > Prenotes, the prenote records are not being included ...

Find distinct records of the last order from a customer
Hi I am trying to find a list of customers that have not bought anything for the last xx months. I have a salesorder table which holds an orderdate and customer account code, other customer details are held in the customers table. i have a table called soitemsdespatch thats holds the parts that have been sold to that customer. salesorder and soitemsdespatch are linked by sonumber. I was hoping that would show me the last orderdate for all customers and list them in order but it repeats customer orders but not all of them. What i would really like is one query that would show m...

calculation help 04-20-10
I will try to explain this I want to have a running total for C D F and G and use the numbers in A as a filter so if I filter the numbers = 33369 I want the totals of C D F G in other cells on the sheet (ie L6,M6,N6,O6) The data in A C D F G is entered maually > In the data below I want to calculate the column C ,D, F ,G but filtered by > Column A > A C D F G > > 33369 04/06/10 2 10 04/06/10 2 3 > 33369 04/07/10 0 11 04/07/10 2 10 > 33369 > 23456 > 2345...

Report Help 05-24-07
I have just created a report the prints to my label printer. The report has a text box for each line of the address with printing initiated from a button within the individual record on a form. My question is is there a way to suppress empty text boxes in the report as some of the records that don't have all the address fields populated look odd spaced out by the empty records Grateful for any help Paul In news:3ln5i.5530$F_4.2937@newsfe4-gui.ntli.net, Paul Martin <ricketrescue@yahoo.com> wrote: > I have just created a report the prints to my label printer. > > Th...

Three variable correlation 05-28-10
Hi. I was wondering if it was possible to work out the correlation between three variables in excel, not just two. My data looks like this (this is just an example, with random values): A B C 15,283,741,924 22,891,175,671 182,489,383 12,450,453,827 25,604,121,237 458,039,634 25,017,912,202 10,105,478,615 408,312,030 15,608,597,275 27,352,700,295 71,393,406 6,293,126,663 33,730,623,537 699,182,754 15,665,042,562 25,952,123,256 76,238,591 11,565,224,759 30,426,279,924 44,724,721 8,716,353,737 33,395,986,265 135,495,541 ...

Bypass 16-point touch calibration
Does anybody have an idea how to bypass 16-point touch calibration appearing on first calibration of a digitizer device. I am working with a capacitive touchscreen and don't need the extra outside square and some users find it hard to get their fingers on the corner spots. The 16-point calibration shows up on first user calibration and then the 4-point calibration is used from second time on. I would like to skip straight to the 4-point calibration even if the touch digitizer has never been calibrated before. Thanks for any help. ...

updating fields in a form to match a specific record
I hope someone can help. I am creating a database wherein I have several different meeting types. Each meeting has it's own agenda wherein each agenda item (different table) is listed as item 1, item 1 name; item 2, item two name; etc. on continuous subforms for each meeting record. My supervisor requested it this way so that if we choose to rearrange or duplicate agenda items for different meetings, we would have that option; however, this has posed a problem in that for every meeting, each agenda item has to be selected all over again. Therefore, I am trying to write an update q...

Spell check 05-22-10
How do I get spell check to work on all programs as I'm typing? WORD 2007 Office Button (top left hand corner) Word Options (lower right hand corner) Proofing on the left hand side, then, on the right hand side, in the:- When correcting spelling and grammar in Word - section place a tick (check) in the:- Check spelling as you type - box then hit OK. If my comments have helped please hit Yes. Thanks. "tomscott56" wrote: > How do I get spell check to work on all programs as I'm typing? See http://sbarnhill.mvps.org/WordFAQs/M...

HOw do I change group of cells from absolute reference?
When I do a Paste Special>Paste Link, the cell looks something lik ='MAIN'!$A$3. This is great, its just what I want. Now I remove the beofre the 3 and drag the cells so my list is linked correctly, like ='MAIN'!$A3, ='MAIN'!$A4, ='MAIN'!$A5, and so on. If I don't remove th $, it just stays as $A$3 for every item. Here's my question. Is there and easy way to convert the cells back t being locked with the $ in place again like: ='MAIN'!$A$3 ='MAIN'!$A$4, ='MAIN'!$A$5, and so on? Sometimes the list is long an its a pain puttin...

Formula for unique records
I am trying to make a report a little more user friendly for a novice excel user, and I am trying to use all formula's instead of pivotables on large data sources. Is there a formula that will pull unique records out of a list of customer #'s as an example without having to use auto filter which would then require more manual intervention. I am then using sumproducts using the customer number to create the report. >I am trying to use all formula's instead of pivotables >on large data sources. How large is large? More than a few hundred rows? Formulas to e...

WinMgmt event error 10
I'm receiving this error on my Exchange 2003 cluster. Any ideas how to fix? Thanks! Event filter with query "select * from __InstanceModificationEvent within 60 where TargetInstance isa "ExchangeServerState" and PreviousInstance.ServerState != 2 and TargetInstance.ServerState = 2 and TargetInstance.ServerMaintenance = FALSE and TargetInstance.GUID = "{AA2B4B17-5E9A-485A-82D3-AAFF2514EC09}"" could not be (re)activated in namespace "//./root/cimv2/Applications/Exchange" because of error 0x8004106c. Events may not be delivered through this fil...

Go to a record in a combo box
Hi, I have a form with a combo box with a key and description. Using a key value what code do I use to have the combo box go to that record? For example, if I have a text box that I enter a key value in and then have a command button that would use that key value and select the data in the combo box just like if I used the dropdown arrow and selected the data. What code would I use in the command button? I think this is possible, but I don't know how...... Thanks, -- Phil Me.ComboBoxName.Value = "TheKeyValue" -- Ken Snell <MS ACCESS MVP> "Phil&qu...

'developer reference' help window font size...
hi all... I have excel 2007 and changed my screen resolution to (bigger) to view the help files. unfortunately with the ‘search scope’ set to ‘developer reference’ the font is tiny but with the other ‘search scopes’ the font size is bigger than before. I have changed the ‘font size’ button in the help window to ‘largest’ but the font is still small. all help greatly appreciated and I thank all of you so much for your time and advice in advance. many many thanks. Paul. ...

Crystal Licenses 12-10-03
We have the Action Pack version of CRM loaded, and when we try to run a report we get : "Please verify that you have enough Crystal licenses" Is this because we have; 1. to buy and install Crystal 2. to enter a license for Crystal that we should have received with CRM 3. to finish the install because we screwed up someplace? What comes with CRM, and what do we need to buy (if anything) to run Crystal against the CRM DB? Thanks Ed Ed, There are plenty of errors that surround the Crystal config with CRM. You don't need to buy anything or enter a Crystal license to run ...

publisher '03 or '07. which is better?
i've had the '07 trial, but now it's time to buy. i've heard '03 is better.?? Did you ask why that person thought 2003 was better? It's your money - what did *you* think about 2007? Why do you want to purchase outdated software? -- JoAnn Paules Microsoft MVP - Publisher How to ask a question http://support.microsoft.com/kb/555375 "sb" <sb@discussions.microsoft.com> wrote in message news:1D87C8F3-E901-4341-819F-80785E7B35D2@microsoft.com... > i've had the '07 trial, but now it's time to buy. i've heard '03 is > bette...

Duplicate Record Error on Owner Assignment
Hello, When I try and reassign an Account record to a different owner I get an error of: "Duplicate Record A record with these values already exists. A duplicate record cannot be created. Select one or more unique values and try again." But I've looked and the record would not be a duplicate record. And this only happens in some cases of transfering owners, but not for others. The error I get on the server is: "Exception from HRESULT: 0x80040237 Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace fo...

Update 10.1.2 of Offic v.x
Everytime I install the 10.1.2 update, Installer Vise (installation=20 application) installs all the files in trash. Installer Vise gives me = the=20 option of installing in trash or office and I choose office. I don't=20 understand what's going on. Try emptying the trash before you update. Then, run the update, and make sure you select the proper Office X folder if prompted. The files you'll find in the Trash will be the old un-updated copies, and the new copies will be in the proper location inside the Office X folder. On 10/3/03 10:51 AM, in article 087e01c389c6$25c281e0$a...

Move a Beginning Balance back into previous year
An entry was made after the year end close for 2006. This entry is now showing as a 2007 beginning balance where there should be none. How can I move this amount out of the beginning balance and back into last year? -- Amy Amy, is this account set up with Balance Sheet or P&L under posting type? If it's a Balance Sheet account and you made an entry for 2006, then the 2006 balance will get 'reclosed' and become a beginning balance for 2007. If it's a P&L account and you made an entry for 2006, then the 2006 balance will also get 'reclosed' but will now ...

excell #10
Hello, Iam trying very hard to find a fourmula in order to calculate holiday for my employees. The problem is as follow... If they are in the company for less than 2 years they are entitle to 20 days between the 1st of January and the 31st of december If they are in the company between 2 and 5 years they are entitle to 22 days from their anniversary date. If they are in the company for more than 5 years they are entitle to 25 days from their anniversary date. My problem is to take in consideration their anniversary date (ex starting date the 15 of march 2000 how many holidays does this perso...

Upgarde from GP9 to GP 10
Dear Friends, I am planning to upgrade GP 9 to GP 10 in my environment. I went through lots of documentation already but would like to learn more to complete upgrade process as smooth as possible. Following is my current GP 9 environment: OS: Windows 2000 SP4 server GP: Version 9 IM: Integration Manager SQL Server: SQL 2000 SP3 I have a new server available so I plan to do the followings: OS: Windows 2003 SP1 (already installed) GP: version 10 SP2 + hot fixes (already done) IM: Intergration Manager + hot fix ( Installed) SQL Serv...

Excel formula 01-31-10
I want to get a total of range b3:af3 for cells that has s= a number ex 2,5,3 and have a formula to give me totals for those entries? ?? -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Rick" <Rick@discussions.microsoft.com> wrote in message news:790EAF59-5BE5-46E5-97EC-13591A148CAA@microsoft.com... >I want to get a total of range b3:af3 for cells that has s= a number ex >2,5,3 > and have a formula to give me totals for those entries? Check your earlier post. Rick wrote: > > I want to get a total of range b3...

Blank subform 06-14-07
** Mistakenly posted to General Questions originally. ** Please help me figure this out! My user has a main client form with multiple job subforms. On client selector's onNotInList event, the user is presented with a new client entry form popup. On closing the client entry form, the user returns to the main client form (the new client) and a job (on the subform) has been started -- assigned an ID number and the date entered. The tables are related, cascading updates/deletes, one client-many jobs. Here is code excerpted from popup new client entry onClose event: Forms![frm1 Client]...

forgot to post version for previous question posted on update quer
In my previous question I forgot to state I'm using Office 2003 ...