How to Check for a field i previous row with the current row in query

[deepakg27]

Hi Guys,

I am working with billing database. I have tblSale as main table and tblSaleDetail as child table. I have frmSale as main form and frmSaleDetail as sub-form. I have created a query which is pulling information from both the tables and grouping it based on Tax Rate. tblSale has a field called freight , Roundoff. Now if the subform has two types of tax rates two entries are created for the same bill Number. Invoice Total (invTot) in query is calculated field, whose value is not stored any where in any table. Till here everything woks fine. Results are sorted on Bill Number.

Now the issue is I want to add the value of Freight and Roundoff to the result of the query but it keeps getting added twice, if there are two different types of Tax Rates in the same bill. I was wondering if there is a way to check the bill number of previous record with the current record & if the bill number are same then nothing is added to the InvTot (Invoice Total) for the current record AND if they are different then Freight, roundoff is added to the field.

Hope have been able to explain my problem.

Thanks & Regards
Deepak Gupta

[ranman256]

no, 1 rec cannot see another rec, but,
you can query recs so they pull their own tax rates,( either regular select query or union queries)
then sum as normal.

[deepakg27]

Hi Ranman256,

Thanks for your info. I think I was not able to explain my problem in detail:

frmSale - has fields freight, roundoff and grand total.
frmSaleDetailSuborm - Qty, Amount and Tax Rate

I am able to make list from query based on different tax rates, for each bill with help of group by clause and it is fine as long as there is only one tax rate in the bill, because there will be only one entry and freight, round off and Grand total would list once only. But when there are two or more tax entries in same bill, number of entries for the same bill number becomes two or more and freight, roundoff, grand total are listed thrice in place of once. Want to avoid this. Any ideas will be highly appreciated.

Thanks and Regards
Deepak Gupta

[deepakg27]

Dear Ranman256,

Can we reference the previous record of the table??? Can we try and use a make table query and perform the calculation on that table and return the values to the report????

Regards
Deepak Gupta

[isladogs]

Yes you can get the value of a field from a previous record using a subquery.
allen Browne has a very clear example on his website which uses meter readings to explain the idea
See http://allenbrowne.com/subquery-01.html and look at the section Get the value in another record

Whether that will fit your needs, I cannot say.

[deepakg27]

Hi Ridders52,

Thanks for your guidance as always. Before I can use the sub-query system, I figured out I need a Table with all the information. Hence I created a make table query and now transfering the data to a new table B2b0 , through make table query. Now in order to achieve my target, I need to reference the record for which need to have the primary field. Any Idea how we can add Primary field to the table (and populate it) when make table query is running or afterwards through VBA.

Thanks and Regards
Deepak Gupta

[isladogs]

You may be able to do the subquery based on another query but let's work with using a table for now.
There are two possible approaches
1. Use a make table query then add a primary key field afterwards as you suggest.
One way to do that is explained here http://allenbrowne.com/func-DAO.html. Look at the CreateIndexesDAO code
2. Create an empty table with the structure you want and include the PK field in your design. Then use an append query to add records to the table.
You can then reuse the same table each time by emptying the table before appending new data. Do NOT DELETE and MAKE AGAIN.

Method 2 is in my opinion much better

[deepakg27]

Hi Ridders52,

Thanks for guiding me, I have been able to get the desired results. Though I feel I have taken a fairly long route, but results are OK. Don't know what would happen once it is put in use where number of entries will be immense, whether it will be a slow process or will it be fast enough. Now trying to automate the complete process of these queries.

Once again thanks.
Regards
Deepak Gupta

[deepakg27]

Dear Ranman256,

Thanks to you also for clearing my doubts.

Regards
Deepak Gupta

[isladogs]

You're welcome.
The append query approach will be slightly slower but simpler for you to setup.
Be aware that either approach will cause database bloat so if you do this repeatedly, make sure you compact at intervals (after making a backup just in case)

[deepakg27]

Thanks, for further insight. Will keep in mind.

Regards
Deepak Gupta