Solved

Ordering a query

Posted on 2002-07-09
10
214 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

820 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