Comparing data with same table

[TH22]

I need a recommendation on how I could create a report.
Currently, I'm creating a report in Access where I import a file containing 350,000 rows (and growing), and run various queries to report the data.

I need to add a new element, and I'm not sure how to do this. I'm not sure if Access is still the best option anymore.

Here is an example of what I am needing to do:
(1) I need to identify the records that have a duplicate id number. This number is quite large. It represents about 1/3 of the total file. Thats easy to do. For example:

[blue]
ID AMOUNT DATE
022 100 1/1/03
022 150 7/1/05
091 100 1/1/03
091 130 5/1/04
091 150 8/1/05
[/blue]
(2) Once I identify the records, I need to do two things:

* determine the difference in amount between the most recent record, and the most previous record to that.. and then multiply it by a certain factor depending on the year
* determine the original amount (amount on the first record) and multiply it by a certain amount)

For example, using ID '091' above:
(150 - 130) * 3 ('3' is used if the date was in 2005)
(130 - 100) * 2 ('2' is used if the date was in 2004)
(100) * 1 ('1' is used if the date was in 2003)


The question is, is this something I can do in Access. If so, I'm assuming I would have to bring vbscript into use. If not, any recommendations.

Thanks,
Tim

 

[dRahme]

Hello, off the top of my head you might do something like first run a 'Find Duplicates' Query and then run another where you take the Top 2 from each ID and then perform the rest of your calculations in a report.

Hope that helps.

 

[Pigster14]

As the previous person stated, make a query to show your duplicates and order by the date column.

In an Access Module, write code that would take the recordset of the duplicate query and loop through each record. Compare the previous record to the next record once you find the second record (keep a counter variable to check this), then do your calculation with the previous and recent record's amount.

So it would look something like this.

Dim rs as recordset
Dim prevAmt as string
Dim CurrentAmt as string
Dim prevID as string
Dim CurrentID as string
Dim TotAmt as string

Set rs = currentdb.openrecordset(DuplicateQuery)

counter = 1

With rs
.Movefirst
Do until .eof
currentamt = !amountinfile
If previd = currentid then
counter = counter + 1
If counter = 2 Then
TotAmt = currentamt - prevamt (Or whatever calculation you want.)
If you want to update the total amount to a table or record you will have to set up another recordset and then in this IF statement do an update to that record.

End If
Else
counter = 1 'resets the counter back to 1
End If
prevID = currentId
Loop
End With

To do the calculation based on year for each record, just do a recordset of the data you want above and loop through the recordset, do the calculation for each record and update an appropriate field.

I hope this helps.
Good Luck!

 

[TH22]

Thanks to the both of you for your comments. I'm going to try this shortly. I will let you know how it goes.

Thanks,
Tim