attributes out of order

[Dornenhexe]

hey there,
I'm currently taking care of a little multiuser access database that tracks applications of students.

The people using the database run queries on a regular basis - setting them up with help of the design view; and here is my issue:

when the queries are setup, the user determines a specific order of the attributes, i.e.:

ssn
firstName
lastName
address
...

however, when the query is executed, the attributes come up in the wrong order, like:

address
firstName
lastName
ssn
...

checking the sql in the back, everything looks correct and the way it should be.

Does anyone have an idea what I could do to bring the queries back to the correct order?

[jzwp11]

Welcome to the forum!

How are the users viewing the query results, through a form or the query results directly?

Can you provide the SQL text of the query?

[Dornenhexe]

The users are viewing the results directly in table format.

The code that the design view generates is:

SELECT summer.SSN, summer.Alumni, summer.FirstName, summer.LastName, summer.ProgramAttending, summer.Scholarship, summer.BalanceDue, summer.ReportCardFinal, summer.Health, summer.ReleaseForm
FROM summer
WHERE (((summer.ProgramAttending)="ichime") AND ((summer.EMBHSSC)=No))
ORDER BY summer.LastName, summer.FirstName;

the results however are displayed as:

SELECT summer.Alumni, summer.FirstName, summer.LastName, summer.SSN, summer.Scholarship, summer.BalanceDue, summer.ReportCardFinal, summer.Health, summer.ReleaseForm, summer.ProgramAttending 
FROM ...

this is just one example, there are several that work (or don't work) in a similar fashion.

[jzwp11]

The order in which the fields are displayed is governed by the SELECT clause. If they are not being displayed in that order then the query is being altered which brings me to my next question. You say this is a multiuser database. Is the database split into front and backends? Can your users see your tables?

[Dornenhexe]

The database is split into front and backend, the users only make use of the forms and queries.

I don't write the queries, the users do that on their own using the design view, since they don't know sql.
However, when I test the queries, in the result the attributes are displayed in a different order than in the SELECT statement.

[jzwp11]

Does each user have their own copy of the front end on their local hard drives?
I sounds like your users have access to the database window (query design view) and thus the tables, this is not recommended in a multiuser environment.

[Dornenhexe]

Each User has their own copy of the frontend on their machine.
But now you confused me, isn't it normal that the fronend users have access to the queries?
Anyways, the only queries that are run are SELECT statements. Any kind of INSERT, UPDATE, DELETE is done by using forms.

[jzwp11]

Normally, the user only interacts with the database through forms. I would normally provide a form with a button and the button invokes the query. There are ways to provide a query builder using a form, but I have not used one personally.

Now regarding the query where the order of the fields returned is different than the SELECT statement, are you able to duplicate this behavior on the user's system? on your system? Is there any VBA code anywhere tied to the query?

[Dornenhexe]

The SELECT statements pull their information all directly out of the main table. There all very basic statements that don't involve any VBA code. There is some VBA used for the forms but those parts shouldn't intertwine with the info that is pulled in the queries.

The same behavior exists on basically all systems, even a non split version of the database that I keep on my system.

When I rearrange the attributes in the Datasheet View they will stay in order for a while but eventually revert back.

[jzwp11]

I'm at a loss, the order in which the fields are displayed should stay as originally designed. Would it be possible to post a copy of your database with any sensitive data removed?