Solved

Ordering a query

Posted on 2002-07-09
10
225 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 

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
 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

632 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