Solved

Ordering a query

Posted on 2002-07-09
10
190 Views
Last Modified: 2008-02-26
I have a sqlquery which i run in my vbapp using an access db. here is the query: Select Type, PLUNummer, VaregruppeNummer, Navn, Sum(Stk) AS SumStk, PrisLevel, Pris, Sum(Belop) AS SumBelop, ReseptFaktor, TilKoInfo, TilKjokkenPrinter, TilKundeKvittering, DummyBehandling, KjokkenPrinterDone, Momssats, Sum(rabattbelop) As SumRabattBelop From BestillingsTransaksjoner  Group By BestillingsNr, Type, PLUNummer, VaregruppeNummer, Navn, PrisLevel, Pris, ReseptFaktor, TilKoInfo, TilKjokkenPrinter, TilKundeKvittering, DummyBehandling, KjokkenPrinterDone, Momssats, rabattBelop Having (((BestillingsNr) = 10090))


The query works fine there is only one problem, i dont get the posts sorted as they where inserted into the table I know that i could have a new field describing which where inserted first, but i dont have that, is there any other way?
0
Comment
Question by:haggmar
  • 2
  • 2
  • 2
  • +4
10 Comments
 
LVL 7

Expert Comment

by:ildc
ID: 7140615
If there is one sort of field that indicates the order in which you inserted the records, (primary key autonumber ??)you have to put it first in the group by line. If you don't have such a field, then it seems pretty impossible to me.

regards
0
 

Author Comment

by:haggmar
ID: 7140621
dont have any field
0
 
LVL 28

Expert Comment

by:TextReport
ID: 7140630
You will need a field to keep a track on this. An AutoNumber Field will do the job.

Cheers, Andrew
0
 

Author Comment

by:haggmar
ID: 7140631
yes i know i can do that but is there any other say to do it without such a field??
0
 
LVL 7

Expert Comment

by:ildc
ID: 7140638
Nope,

Because access does not store internal record numbers.

regards
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 3

Expert Comment

by:dapperry
ID: 7140894
I often create a datetime field called InsertDate to store when the record was actually inserted. I don't know if you have a field like this, but you could use that. That said, you should probably add an autonumber field as well.

:) dapperry
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 7141002
the concept of INSERT ORDER in a true relational database is totally AD HOC, as it generally is of little or no intrinsic meaning.  Most database management systems do not AUTOMATICALLY keep track of when a record was inserted - that is generally up to the designer of the table(s) to add a field, to each table, to store that information, if it is important.

If you have not already designed such a field into your tables, then there is almost NO WAY to determine that information AFTER THE FACT.  The data that is currently in the table is in a completely arbitrary order, which has very little to do with the order in which the records were actually added.  If, on the other hand, you have an autonumber field(Access), then THAT field will tell you what order the records were added.  Or, as has been suggested by others, if you have a TimeStamp field, into which you record the Date and Time when a record was added to the table, then here again, you can determine the order in which records were added.  But in the absence of either of those types of fields, then NO, you cannot determine what order the records were added.

Arthur Wood
0
 
LVL 3

Expert Comment

by:dapperry
ID: 7141102
Arthur_Wood,
     Would it be safe in saying then, if there had NEVER been an index on the table, that the order would simply be from top to bottom?

:) dapperry
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7260158

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - PAQ'd and pts removed
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 7296328
Per recommendation, points NOT refunded and question closed.

Netminder
CS Moderator
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now