473,320 Members | 1,900 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Pass Throughs and Reports - Record Order?

Have worked for many years with the above both toegther and separately
(as in Jet and reports), I was always under the impression that the
order by clause of a query had no real bearing on the order of records
shown in a report based on the query. Sorting & grouping has to be
specified.

However, in A2003, I've been working on a large app for the past 6
months and have finally gotten around to creating and manuipulating
reports. I'm finding to my puzzlement that even without sorting and
grouping specified, the order of appearance of records on the reports
based on PTQs seems to be as one specifies in the PTQ.

IS this normal for PTQs? The Oracle SQL I'm generating is anywhere from
8 to 25 pages long (disgustingly monotonous union queries - it's a very
bad database design, though not my own) and it would really be lovely if
it was NOT necessary to parse through the various select clauses and
figure out what to plop into the sorting & grouping properties of a
report...

Thanks to anyone with any perspective on this.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Feb 24 '06 #1
2 1685
On Fri, 24 Feb 2006 18:00:08 -0330, Tim Marshall
<TI****@PurplePandaChasers.Moertherium> wrote:

I have no specific knowledge about Oracle as a back-end, but as you
know, all recordsets are ordered. Even in the absence of an ORDER BY
clause, the trained eye can see an order. Often a PK.

I think it's still good practice to NOT specifically order your report
queries, and to use the Grouping and Sorting to do its thing.

I feel your pain about the db design. I once worked on a project where
the design was so bad I created a layer of queries that essentially
was the "correct" (IMHO) design. Then I would use those queries as the
base "tables" for my other queries. Once you are used to a normalized
design, it's hard to work with a messy one.
Have you noticed that most good db designers agree on the "correct" db
design? We may argue in the margins, but essentially there is only one
"correct" design. 3NF yeah!

-Tom.
Have worked for many years with the above both toegther and separately
(as in Jet and reports), I was always under the impression that the
order by clause of a query had no real bearing on the order of records
shown in a report based on the query. Sorting & grouping has to be
specified.

However, in A2003, I've been working on a large app for the past 6
months and have finally gotten around to creating and manuipulating
reports. I'm finding to my puzzlement that even without sorting and
grouping specified, the order of appearance of records on the reports
based on PTQs seems to be as one specifies in the PTQ.

IS this normal for PTQs? The Oracle SQL I'm generating is anywhere from
8 to 25 pages long (disgustingly monotonous union queries - it's a very
bad database design, though not my own) and it would really be lovely if
it was NOT necessary to parse through the various select clauses and
figure out what to plop into the sorting & grouping properties of a
report...

Thanks to anyone with any perspective on this.


Feb 25 '06 #2
Tom van Stiphout wrote:
I think it's still good practice to NOT specifically order your report
queries, and to use the Grouping and Sorting to do its thing.
I think you're right. Just to be sure, I'm dropiing any order by clause
for PTQs I give to reports. Still, it rather shocked me when I first
saw the reports giving stuff in the correct order.
I feel your pain about the db design. I once worked on a project where
the design was so bad I created a layer of queries that essentially
was the "correct" (IMHO) design.


Yup. 8) I was going to try to describe the horrific design of our
system, but I just droned on for too long. Suffice to say that that to
propely pull information out of this I need a union query with up to
four very large separate queries, with each of these having a union
query with multiple parts inside. It can balloon up to an select
statement that in 10 arial font occupies 24 pages in a Word document.
That's pretty big in my experience, and there's absolutely no way Jet
could handle it using ODBC linked tables (there's lots of subqueries,
anyway, which I've found Jet doesn't do too well with when I've got lots
of records) which is why I stick to PTQs...
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Mar 1 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: marwa mohamed | last post by:
salamo alikom, i'm working with arachaeological sites database and i want to show the picture of each site in its report.every thing goes fine in the preview of the reports(on screen).But in...
4
by: Mike Thomas | last post by:
Access 2000 - Is there a 3rd party tool available, or perhaps some method built into Access 2000, which could isolate unused Access reports, queries, forms, etc? I have a 5 year old app which...
1
by: jj | last post by:
Ugh, I had to change the parameters of an ODBC connection and refreshed my linked tables which isn't so bad. But I noticed the pass-through queries didn't refresh and I've got like 20 of them. So...
4
by: Rich Moser | last post by:
My organization is in the midst of making a reporting tool decision. On the table are Crystal and the reporting functionality in Access. - Report distribution is not a factor - it's all local -...
2
by: misscrf | last post by:
I have a search form that is great. I have modified it in such a way, that when search results come up I can bring it back to a useful spot, say an entry form or a report. Here is my lemon (...
7
by: Zlatko Matić | last post by:
Let's assume that we have a database on some SQL server (let it be MS SQL Server) and that we want to execute some parameterized query as a pass.through query. How can we pass parameters to the...
0
by: Zlatko Matić | last post by:
Hi everybody! Recently I was struggling with client/server issues in MS Access/PostgreSQL combination. Although Access is intuitive and easy to use desktop database solution, many problems...
1
by: Hexman | last post by:
Hello Again, I have a table that contains data that needs to be ranked. There are actually 2 columns that need to be ranked. I have already completed the task but did it in a "Brute Force" way....
1
by: jeganrd | last post by:
i have problem using distinct query. I have table it has 22 fields. call_id is repeted because of maintain the history. when i insert the same record as another record some filelds should...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.