Question about sorting

[Tbure90]

This might be somewhat of a duplicate of a thread titled "table not sorting properly". (I'm still a newby so it won't let me post the link)

What I've gathered from that thread is that tables have no inherent sorting applied to them. They are just a jumbled container for your data, and the only way to sort this data is by using a query. Am i correct in saying this?

If what I said above is the case, do dao recordsets work the same as queries? As in, will they actually sort data the way i tell them to?

The reason i ask is I've been tasked to generate a running totals report for another department. The goal is to use a table from our MRP system that defines all actions required for a part. For example it contains records for each part number that tell you when you need to buy a part and how much you need to buy (adds to the running total), when that part needs to be sold or to be used as a component for building another part (subtracts from the running total) all based current demand, lead times, etc...

I have like 120ish lines of code that i wrote and the logic is all there and it works. The way it works is it loops through the recordset (which selects all records from a table called tblTotals) which is sorted by part number, then date, then action type (buy, make, sell, etc...). It then checks if the part number for the current record matches with the part number of the previous record, then adds or subtracts to the running total based on the action type of the current record. Once that value becomes negative, that means on that date we will be short the quantities that we need to fulfill the demand. Our purchasing group wants to use this so that they can see when that will happen ahead of time and can plan with our suppliers so that we are never short of what we need.

So once I find the records for each part where the quantity will go negative, it places the negative quantity and the date it will go negative into 2 fields for that record. i then run another query to select all records that have negative running totals and that is the data my purchasing/procurement group wants to see.

Herein lies the problem, I have reduced the number of records in my actions table for testing code's logic, and it works, most of the time... 9 times out of 10 i'll get the correct running total quantities and my only thought, since my code requires that the recordset be sorted properly, is that it isn't always sorting the way I'm expecting it to. Which means some records are appearing before others and it messes up the running total. One part number in particular will show the correct value (-53) 9 times out of 10. I'll come in the next morning and play around with it some more and all of a sudden it will show (-253).

So, do dao recordsets honor the order by clauses of the SQL statements you use to generate them?

 

[tvanstiphout]

Did you know that you can calculate running totals in a report? https://support.microsoft.com/en-us/office/summing-in-reports-ad4e310d-64e9-4699-8d33-b8ae9639fbf4
Wrt sorting: it is customary to base a report on a query, not a recordset. That query can be unordered. Then in the report you use the Sorting and Grouping feature to add sorting.

 

[Gasman]

This might be somewhat of a duplicate of a thread titled "table not sorting properly". (I'm still a newby so it won't let me post the link)

What I've gathered from that thread is that tables have no inherent sorting applied to them. They are just a jumbled container for your data, and the only way to sort this data is by using a query. Am i correct in saying this?

If what I said above is the case, do dao recordsets work the same as queries? As in, will they actually sort data the way i tell them to?

The reason i ask is I've been tasked to generate a running totals report for another department. The goal is to use a table from our MRP system that defines all actions required for a part. For example it contains records for each part number that tell you when you need to buy a part and how much you need to buy (adds to the running total), when that part needs to be sold or to be used as a component for building another part (subtracts from the running total) all based current demand, lead times, etc...

I have like 120ish lines of code that i wrote and the logic is all there and it works. The way it works is it loops through the recordset (which selects all records from a table called tblTotals) which is sorted by part number, then date, then action type (buy, make, sell, etc...). It then checks if the part number for the current record matches with the part number of the previous record, then adds or subtracts to the running total based on the action type of the current record. Once that value becomes negative, that means on that date we will be short the quantities that we need to fulfill the demand. Our purchasing group wants to use this so that they can see when that will happen ahead of time and can plan with our suppliers so that we are never short of what we need.

So once I find the records for each part where the quantity will go negative, it places the negative quantity and the date it will go negative into 2 fields for that record. i then run another query to select all records that have negative running totals and that is the data my purchasing/procurement group wants to see.

Herein lies the problem, I have reduced the number of records in my actions table for testing code's logic, and it works, most of the time... 9 times out of 10 i'll get the correct running total quantities and my only thought, since my code requires that the recordset be sorted properly, is that it isn't always sorting the way I'm expecting it to. Which means some records are appearing before others and it messes up the running total. One part number in particular will show the correct value (-53) 9 times out of 10. I'll come in the next morning and play around with it some more and all of a sudden it will show (-253).

So, do dao recordsets honor the order by clauses of the SQL statements you use to generate them?

I am going to say yes. However reports have their own sort and group options, and for a long time thought they take NO notice of the incoming data order, but have been advised there is one way.

Report Cannot Sort on Date Field

Hi All, I have a query and the sorting is just fine. But when a make a report out of this query and sort it, it doesnt follow at all. Even when i tried to sort it in ascending or descending order. I just want to sort the field but as you can see in the report below, it doesnt follow. Data...

www.accessforums.net

 

[Tbure90]

I believe there might be some confusion, I'm not using an Access report at all. i'm doing all this to add the negative qty's to the appropriate records in a table, then querying that table for only records with negative values, then dumping that into an Excel file.

@Gasman when you say "yes" do you mean that dao recordsets behave like queries or tables when it comes to sorting?

 

[Gasman]

I would have to say.
A query opened as a recordset would be in the order of the fields mentioned in the order by.
A table opened as a recordset would be in the order of that table.

Really up to you to ensure the order is as you require.

 

[MajP]

dao recordsets behave like queries or tables when it comes to sorting?

Sounds to me like currently you are simply doing something like

dim rs as dao.recordset
Set RS = currentdb.openrecordset ("MyTable")

Where you should be doing something like this
Set RS = currentDB.openrecordset ("Select * from MyTable ... Order By SomeField')
or creating your query first
Set RS = currentDB.openrecordset ("qryMySortedQuery')

 

[Tbure90]

@MajP I'm using the following code:

dim rs as dao.recordset
set rs = currentDB.openrecordset("Select * from tblAction order by partno, acedate, acttype")

I'm using a make table query first to create tblAction then using tblAction for my recordset as shown above.

I was under the impression that if you use a query as the data source for a recordset then you can't edit any of the records

 

[The_Doc_Man]

If what I said above is the case, do dao recordsets work the same as queries?

Yes, if the DAO recordset opens a query with an ORDER BY statement. No if it opens a table directly.

A DAO recordset opens a set of records determined by what it opened. Open a table, get a table-type recordset (and the records will appear in what we believe to be last-updated, last-out.)

So, do dao recordsets honor the order by clauses of the SQL statements you use to generate them?

Yes. In fact, if you delved deeply enough, you would recognize that a DAO Recordset underlies each bound form and each bound report. In the case for the form and report, that binding is implied - but it is there and you can even get to it from VBA code in the form or report. Note that while you CAN get to a form's recordset, you should not diddle with the recordset except in very narrowly constrained ways. Otherwise your forms will look VERY strange and their behavior can become VERY unreliable.

EDITED by The_Doc_Man to correct presumed order of record appearance in a table.

 

[Mark_]

Three questions;
1) Why are you using a DAO recordset instead of using a query?
2) Why are you storing values you want to report on in a table?
3) Have you tried just creating a query on your data, then making a report to generate what you are looking for?

For me, if I were doing the purchasing I'd rather know we go negative 1/3/25 (so I need to order 7 pieces) AND we go negative 2/3/25 (so I need to order 114 pieces). This would let me know when I need delivery / quantity by so I can order a small number of items we need TOMORROW and put in the bigger order for what needs to be here next month.

 

[MajP]

I was under the impression that if you use a query as the data source for a recordset then you can't edit any of the records.

As long as the query is updateable the recordset based on it will be too. A simple select query should be editable.

 

[Pat Hartman]

So, do dao recordsets honor the order by clauses of the SQL statements you use to generate them?

Yes. The only time you run into issues is if you are not sorting on unique values (which you are not). The "duplicate" records are returned in an arbitrary sequence. As a "tiebreaker", I would include the autonumber as the last sort field. That gets the oldest records sorted first assuming your autonumber is sequentially generated as records are added.