469,616 Members | 1,823 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,616 developers. It's quick & easy.

Select from multiple queries/tables?

Can I select from multiple tables/queries in a single SQL statement?

For example:

SELECT [this], [that], [otherThing]
FROM qryThis, qryThat, tblOtherThing

Does this work only with Jet, or does SQL just work like this?

The specific problem I'm trying to solve is compiling a RecordSource for a
report that includes a formatted Name-Address block that looks like
something like this:

Mr. John Person
1234 South Street
Anytown, CA 96723

In the below SQL statement, [qryNameAddressBlock] gathers all the address
info (as [Block]) with the help of a function to skip any blank lines and
insert carriage returns in the right place.

SELECT [tblTxJournal].[Amount], DateValue([tblTxJournal].[TxDate]) AS
InvoiceDate, [tblTxJournal].[Entity_ID], [tblTxJournal].[Tx_ID],
[qryNameAddressBlock].[Block] AS Customer
FROM tblTxJournal, qryNameAddressBlock
WHERE ([tblTxJournal].[Tx_ID] = 12504);

Will this work if the database is upsized to SQL Server?

Feb 4 '06 #1
2 2846
>From a design view in a query, can you link your queries and tables
together? Find a common link between all your data sources. It seem
tblTxtJournal should have a customer ID and link it to the
qeyNameAddressBlock.

Have extensive experience in developing accounting software. I would
put a copy of your customer address in your invoice history file. At
the time of the invoice, the address was this. If you change their
address, change the customer master, the old invoices should print with
the old address, the new invoices should have the new address.

Feb 4 '06 #2
> >From a design view in a query, can you link your queries and tables
together? Find a common link between all your data sources. It seem
tblTxtJournal should have a customer ID and link it to the
qeyNameAddressBlock.
Yes, but my main question was regarding the syntax of the SQL statement:

SELECT [this], [that], [otherThing]
FROM qryThis, qryThat, tblOtherThing

Which I assume is perfectly fine (since it works), but I'm curious if this
is something peculiar to Jet - or is it acceptable SQL grammar which will
also work with other databases, such as MS SQL Server, Oracle?
Have extensive experience in developing accounting software. I would
put a copy of your customer address in your invoice history file. At
the time of the invoice, the address was this. If you change their
address, change the customer master, the old invoices should print with
the old address, the new invoices should have the new address.


Good advice, to be sure, and that's exactly how it works:

All addresses are stored in a separate table. Each customer record can have
any number of addresses, one of which is selected as the primary, or billing
address. Invoices are generated dynamically (using the selected address),
automatically converted to PDF and emailed, and a hyperlink to the pdf file
is automatically inserted into another table which is displayed in a subform
datasheet. So old invoices are a click away and will show whatever address
was selected when the invoice was created.
Feb 4 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

17 posts views Thread by kalamos | last post: by
3 posts views Thread by ivanov.alexei | last post: by
6 posts views Thread by BobRoyAce | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.