Does a big query cause problem?

[Shu]

Hello,
I wrote some big queries in Access 2000 and output the
result to Excel report. after my program created about 6
Excel files, the next reports can not be created
regularlly. So I have to exit my program, run it again,
creating the remaining reports. my query on this reports
is about 10 pages long with normal font size in Word. Is
it possible they affect the performance my programm, if
not, what can cause the problem? Thanks very much!

 

[Jeff Boyce]

"Shu"

10 pages of Word?! That IS a big query. Is there a chance that your query
is so long because you have a large number of fields in a table?

If you would provide a description of the type of data you are handling, and
the data structure (general description), the 'group readers may be able to
offer alternative suggestions.

Good luck

Jeff Boyce
<Access MVP>

 

[Shu]

Hi Jeff,
I joined five tables, each table has about ten fields. I
need to use outer join to catch all infomation matched in
two table and also keep those not matched both in two
tables. Since there is only left join and right join, can
only keep either the left table or ritht table's
information, so I have to use Union to get all information
existing in two tables. for the next level, I regard the
query just created as a table, join this with another
table again. the structure is simple, I just keep using
LEFT JOIN, RIGHT JOIN and UNION among those tables, but
same sub-query appears several times, so finally the query
becomes very big.
Can you give me a better solution for this? Thanks very
much.

regards
Shu

 

[Jeff Boyce]

From your description, nothing leaps out at me (but other 'group readers may
see something). Could you provide a description of the data
organization/table structure?

Jeff Boyce
<Access MVP>

 

[Shu]

Hi Jeff,
my data structure is like:
table1: source system data(system ID,Date, Product ID,
accoutID1, accountID2.....)
table2: source system adjustment data(system ID,Date,
Product ID, accoutID1, accountID2.....)
table3" GL data(Date, Product ID, accoutID1,
accountID2.....)
table4: GL adjustment data(Date, Product ID, accoutID1,
accountID2.....)
table5: SIF adjustment data(Date, Product ID, accoutID1,
accountID2.....)
I need to use the 5 table to create a GL reconciliation
report, the requirement is output all balances in source
sytem, source system adjustment, GL, GL adjustment and SIF
adjustment of a product. if a product is not from a
system, show it in GL, GL adjustment, SIF part(if any),
vice versa, if a product does not exist in GL, show the
balance in system part(if any).

so my query structure is like this:
1.first UNION data in source system part
select * from table1 left join table2
UNION
select * from table1 right join table2

2.second UNION data in GL part
selcet * from table3 left join
(select * from table4 left join table5
UNION
select * from table4 right join table5)
UNION
selcet * from table3 right join
(select * from table4 left join table5
UNION
select * from table4 right join table5)

3. UNION data on system part and GL part.
select * from
(select * from table1 left join table2
UNION
select * from table1 right join table2) left join
(selcet * from table3 left join
(select * from table4 left join table5
UNION
select * from table4 right join table5)
UNION
selcet * from table3 right join
(select * from table4 left join table5
UNION
select * from table4 right join table5))

UNION

select * from
(select * from table1 left join table2
UNION
select * from table1 right join table2) right join
(selcet * from table3 left join
(select * from table4 left join table5
UNION
select * from table4 right join table5)
UNION
selcet * from table3 right join
(select * from table4 left join table5
UNION
select * from table4 right join table5))

I ignore some key words, Can you see some sub query is
written more than once.
Is there a better way not to use UNION to combine the left
join and right join? Thanks very much

regards
Shu

 

[Jeff Boyce]

Shu

I may still not fully comprehend your data structure, but I noticed that you
have multiple repeating fields, in which some "intelligence" or meaning is
embedded in the field name (this is a BAD thing in a relational database,
but totally necessary in a spreadsheet). For example, your Table1 appears
to have "accountID1, accountID2, ..." -- this is what I mean by repeating
fields.

Moreover, your Table2 field names appear to be identical to your Table1
field names. It is also a BAD idea to use identical tables with differing
table names to store meaning. And again, while necessary in a spreadsheet,
where you might have a dozen sheets, all with the same structure, in Access
or other relational databases, this approach to data structure causes
enormous headaches and make for much more work than necessary. As one
example, "committing spreadsheet on Access" can result in very large,
complex SQL expressions (?!).

My first recommendation is to step back from the current structure you have
and consider the topic of normalization. Paper and pencil are often the
tool of choice in initial design of a relational table structure. While I
am not as intimately familiar with your data as are you, I'll hazard a guess
that you are working with accounting data, related to systems, products and
accounts.

If so, a more normalized structure for your data would embody these
"natural" entities. You might have:

tblSystem
SystemID
SystemName

tblProduct
ProductID
ProductName
?ManufacturerID (if there is only one per Product -- otherwise, you'll
need a one-to-many relationship)

tblAccount
AccountID
AccountName
AccountContact
AccountContactPhone
...

tlkpAccountingCode (this table would hold your Source, SourceAdjustment, GL,
GLAdjustment, and SIFAdjustment)
AccountingCode
AccountingCodeDescription

trelTransaction
TransactionID
TransactionDate
TransactionAmount
SystemID
ProductID
AccountID
AccountingCode

Please consider these as rough ideas, based on only my spotty notion of your
situation. Notice that any combination of Date, System, Product, Account,
and/or AccountingCode could be derived from the trelTransaction table.

Or have I totally misunderstood your business need?

Good luck

Jeff Boyce
<Access MVP>

 

[Shu]

Thanks very much, Jeff, now I know where is my problem.

-----Original Message-----
Shu

I may still not fully comprehend your data structure, but I noticed that you
have multiple repeating fields, in which

some "intelligence" or meaning is