Is this a complicated query?

  • Thread starter Thread starter sw
  • Start date Start date
S

sw

I have fields from 2 tables that I am extracting into a
query, from Table 1 - Account No. (primary key), Account
Name and then some address fields.
In Table 2 I also have the Account No and therefore am
extracting an Invoice Number and Value.
When I run the query I see correct data however if the
Account No appears more than once in table 2 (as it may
well be if there are different invoice values) then I see
this on individual rows e.g.

Acc No. Account Name Invoice No Value
100 A 1 25
100 A 2 34
101 B 1 14
101 B 2 23
Is there any way of bringing the data in so that it finds
all the relating records for that Account No and groups
the individual invoice no's and amounts on the same row?
e.g.

Acc No Account Name Invoice No Value Invoice No Value
100 A 1 25 2 34
101 B 1 14 2 23

any help or advice would be appreciated.
 
Why do you need the data in this fashion? What bigger task are you trying
to accomplish?
This is not a standard database style operation, so what's the gig?

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
Is there any way of bringing the data in so that it finds
all the relating records for that Account No and groups
the individual invoice no's and amounts on the same row?
Click to expand...

What would you like to see if there were one invoice for one account,
two invoices for the next, and 37 invoices for the third? How wide is
your printer paper?

Yes. It's a complicated query, if you want the above kind of
flexibility. You'll need to create a query joining the Invoice table
to the Accounts table *MULTIPLE TIMES* - as many copies of the Invoice
table as you want repeats; put a criterion on the first instance of 1
on the invoice number, 2 on the second and so on. Use "Left Outer
Joins" for all the join lines (select the join line and choose the
option "show all records in Accounts and matching records in
Invoices"). If you join five instances of Invoices, and have an
account with six, you'll just not see the sixth invoice.


You might want to consider that a Query is not really a display tool,
and instead build a Report based on a simple join query; use the
Report's sorting and grouping feature to group by the account
information and display the invoices like:

AccountNo AccountName
100 XYZ Corp
InvoiceNo Value
1 $343
2 $220
AccountNo AccountName
101 ...
 
If you have access to any version of S2k
this problem is very easy with the RAC utility.
No complicated sql, just a few parameters:).

RAC v2.2 and QALite @
www.rac4sql.net
 
Back
Top