Calculation Between Records

[ajf200]

I'm working on a database used for tracking production. Currently, I have a query based off of multiple tables that looks like this:

Product__Date_____Shift___Throughput_Rejects
A...........12/4/06.....1.........1500...........5
A...........12/4/06.....2.........3500...........22
A...........12/4/06.....3.........4000...........29
B...........12/4/06.....3.........500.............8
B...........12/5/06.....1.........2000...........14

As you can see, each shift records the numbers cumulatively. I want to create a query that will tell me what the throughput & rejects (and eventually yield) are for each shift. I already have figured out a way to sort the query correctly so I had anticipated on just saying:

If (CurrentRec.product = PrevRec.product)
Then
ThroughputPerShift = CurrentRec.Throughput - PrevRec.Throughput
Else
ThroughputPerShift = CurrentRec.Throughput

The above is obviously my own syntax. How would I go about doing this in VBA?

 

[KeithG]

I would make a totals query off of the above query and group by Shift and sum the rejects and throughput fields.

 

[ajf200]

Re-Explained.

^^
Sorry, I think maybe I didn't fully explain the problem. Here is an example of something I would need to figure out. What is the yield of of product A on Shift 3 on 12/4/06?

Yield = (Throughput - Rejects) / Throughput

If I simply took (Throughput - Rejects) / Throughput....that would give me the Yield To Date (for lack of a better term) because the Throughput and Rejects numbers are cumulative. They didn't actually make 4000 units that shift, they only made 500, bringing the cum. total to 4000.

The actual yield should be ((4000-3500)-(29-22))/(4000-3500). But, I need to put a way in there to check to make sure it is the same product on there. Otherwise, it would give me negative values for Product B on Shift 3 of 12/4/06.

Hope this is a better explaination.

 

[Cosmos75]

Product__Date_____Shift___Throughput_Rejects
A...........12/4/06.....1.........1500...........5
A...........12/4/06.....2.........3500...........22 (Previous Record)
A...........12/4/06.....3.........4000...........29 (Current Record)
B...........12/4/06.....3.........500.............8
B...........12/5/06.....1.........2000...........14

It sounds like you trying to use the (Previous Record) row in (Current Record) row, correct?

If so, you may want to take a look at this, Previous Date & Sum of Month/Year to-date using a correlated subquery.

 

[ajf200]

Possible Solution: Subquery?

I think I found another possible way to do this. I found this on the MS support site. How to Compare a Field to Field in Prior Record.

I took this idea and added an additional statement to the Where clause.

So, based on this, here is what I did. I made a new query with the following fields:

Date.....Shift.....ProductLot.......Throughput.......PrevThroughput

PrevInspected: (
[B]Select [/B]Max([Throughput]) 
[B]From [/B][qryRejectDetailsByShift] 
[B]Where[/B]([qryRejectDetailsByShift].[Throughput]<[Throughput] 
[B]And [/B][qryRejectDetailsByShift].[ProductLot]=[ProductLot])
)

But, it's returning null values for all records. I messed around with it some more but I still am not getting anything. Any suggestions from there? If I can get that to work I can simply just make another field which finds the difference [Throughput]-[PrevThroughput].

 

[ajf200]

Got It!

Okay got it!

I had to figure out the whole Alias thing.

So in summary:

I made a new query and I based that off of my original query (qryRejectDetailsByShift) from my first post.

Then right click on the qryRejectDetailsByShift while it's in the upper pane then Properties. I changed the Alias to qryRejectDetailsByShift1. If you are confused on the Alias part, see How to Modify Query Properties.

Then this is how I created the query from there.

Date.....Shift.....ProductID.......Throughput.......PrevThroughput

The first 4 fields were from qryRejectDetailsByShift. The field PrevThroughput is a subquery:

PrevThroughput: (Select Max(Throughput) from [qryRejectDetailsByShift]
Where ([Throughput] < [qryRejectDetailsByShift1].[Throughput] 
And [ProductID]=[qryRejectDetailsByShift1].[ProductID]))

The great thing about this method is that it doesn't matter which order the records are in, it will find the next highest [Throughput] value for records where the ProductID values are equal.

Thanks guys for your ideas. I don't see much help on here with topics like this so I hope this helps others too!


We Are...Penn State!