how to make a query?

[Beginner]

Hi!

I have the following problem:
I need to have data in a table (or query?) sorted ascending by a field. I do
this with SQL (INSERT INTO table SELECT fields FROM table2 ORDER BY fieldx
asc), but I have noticed that when I open the table the records are not
always in the order I want them to be in. I need to calculate things and the
records should be sorted, but they aren't.

I found this out today, I have been testing my calculation for a week and
everything has worked fine, but now suddenly the order is different and
calculation calculates correctly but with the wrong values so the outcome is
wrong. I wouldn't mind if the table would be sorted correctly and the sorting
wouldn't be visible when I open the table (so that the calculation would be
correct), but it isn't..

So how should I fix this problem? I searched for this forum and found one
conversation concerning this matter, but I didn't find an answer (there
apparently was an answer in some other place). I was wondering if I could for
example make a query that would be visible (so that I could check it out) in
the Queries-window? Or how should I make this problem disappear? Going
recordset through record by record is quite fast and works if the tables are
correctly sorted.. So is there any way to sort and stay sorted?

- Beginner -

 

[Beginner]

I forgot to mention that I have 5 tables that I sort and only one of them
doesn't sort correctly. Weird! Or not?

- Beginner -

 

[Mr. B]

I forgot to mention that I have 5 tables that I sort and only one of them
doesn't sort correctly. Weird! Or not?

- Beginner -

If you use a query to acquire your records then you can specify the
sort order in the query and it will remain sorted the same way
everytime.

Not sure that that this helps, but post back if needed.

Mr B

 

[John W. Vinson]

I wouldn't mind if the table would be sorted correctly and the sorting
wouldn't be visible when I open the table (so that the calculation would be
correct), but it isn't..

A table HAS NO ORDER.

It's an unordered "sack" of data. It will often be presented in primary key
order, but if Access finds some other order more convenient, that's what
you'll see.

You shouldn't be looking at table datasheets anyway. If you want records
presented in some particular order then you must - no option! - use a Query
sorting the records.

John W. Vinson [MVP]

 

[Beginner]

Okay! My next question is how to make a query? I know how to sort a recordset
and put it into a table, but I have no clue in how to get a query made. I
found this example but it doesn't work (Object.), is it even close being the
way to make it?

Dim qryDef As QueryDef
Dim strSQL As String
rstSQL = "INSERT INTO MyQuery SELECT * FROM Table1 ORDER BY Id"
Set qryDef = Object.CreateQueryDef("MyQuery", strSQL)

-Beginner-

 

[John W. Vinson]

Okay! My next question is how to make a query? I know how to sort a recordset
and put it into a table, but I have no clue in how to get a query made. I
found this example but it doesn't work (Object.), is it even close being the
way to make it?

Dim qryDef As QueryDef
Dim strSQL As String
rstSQL = "INSERT INTO MyQuery SELECT * FROM Table1 ORDER BY Id"
Set qryDef = Object.CreateQueryDef("MyQuery", strSQL)

Ordinarily one would simply create a query in the query grid or the SQL
window. Do you have some particular reason for creating it in code????

Perhaps you've come from some programming environment where everything has to
be done in code. That's not Access's style.

If you DO want to do so, use the CreateQueryDef method of a Database object:

Dim db As DAO.Database
Dim qryDef As DAO.Querydef
Dim strSQL As String
strSQL = "SELECT * FROM Table1 ORDER BY ID" ' a simple select query
Set db = CurrentDb
Set qryDef = db.CreateQuerydef("MyQuery", strSQL) ' create the query, name it
' MyQuery

You may then need to refresh the database window to see it, but it'll be
there.

I really think you're making this job MUCH MUCH harder than it needs to be!

John W. Vinson [MVP]

 

[Beginner]

Hmm.. Okay.. How about if I just make the query with query design, is there
any way to refresh the query? I am doing this because I retrieve data every
day from a server to the database, and I need to have everything in the
query. If I would be able to just refresh it, I wouldn't have to create and
delete it every time in code.

"Perhaps you've come from some programming environment where everything has
to be done in code. That's not Access's style."

Yeah, that is the case, though I'm not a guru.. I'm so new to Access and
VBA that I don't think I have adapted the idea of VBA programming fully yet..
I think I'm doing some things the hard way For example this!

-Beginner-

 

[John W. Vinson]

Hmm.. Okay.. How about if I just make the query with query design, is there
any way to refresh the query? I am doing this because I retrieve data every
day from a server to the database, and I need to have everything in the
query. If I would be able to just refresh it, I wouldn't have to create and
delete it every time in code.

There's no need to "refresh" it.

The query has no independent existance; it's just a View of the data in the
table. When you open the query, it displays the data that exists in the table
at that moment.

John W. Vinson [MVP]

 

[Beginner]

Okay! Thank you for the answers!

-Beginner-

There's no need to "refresh" it.

The query has no independent existance; it's just a View of the data in the
table. When you open the query, it displays the data that exists in the table
at that moment.

John W. Vinson [MVP]