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, [qryNameAddressB lock] 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],
[qryNameAddressB lock].[Block] AS Customer
FROM tblTxJournal, qryNameAddressB lock
WHERE ([tblTxJournal].[Tx_ID] = 12504);
Will this work if the database is upsized to SQL Server? 2 2989
>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
qeyNameAddressB lock.
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.
> >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 qeyNameAddressB lock.
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Beyonder |
last post by:
I have five tables in my database, there are actually NO common fields
between them, not even a KEY or ID or anything like that, except for
the "body" of a blob field. and that text is not identical, just a
portion of that text is identical.
each table has 5 fields, all different except the blob, which is
called "message", so normally I use something like:
select * from table1 where message like '%apple%';
|
by: Michael |
last post by:
I have two tables with a 1-many relationship. I want to write a
select statement that looks in the table w/many records and compares
it to the records in the primary table to see if there are any records
that do not match based on a certain field.
Here is how my tables are setup:
Task Code Table
TCode,TCodeFName,Active
|
by: kalamos |
last post by:
This statement fails
update ded_temp a
set a.balance = (select sum(b.ln_amt)
from ded_temp b
where a.cust_no = b.cust_no
and a.ded_type_cd = b.ded_type_cd
and a.chk_no = b.chk_no
group by cust_no, ded_type_cd, chk_no)
|
by: serge |
last post by:
Using "SELECT * " is a bad practice even
when using a VIEW instead of a table?
I have some stored procedures that are
identical with the difference of one statement
in the WHERE clause. If I create a single View
and specify also in this View the WHERE clause
that is common in these stored procedures, I
will have the new stored procecures changed to
be like:
|
by: rayone |
last post by:
Hi folks.
I need advice.
2 options, which do you think is the better option to
display/retrieve/report on the data.
Keep in mind reporting (Crystal), SQL Performance, VB Code, usability,
architecture.
Case 1: On a web page I would like to render a dropdown list
| |
by: andro |
last post by:
Hi everybody!
I have several tables from which I want to exract the SAME value (along with
other referenced data).
All the values are in the same column within the tables.
How can I achieve this?
TIA.
Andro
|
by: ivanov.alexei |
last post by:
It's my first post and first DB application.
Environment: MFC DAO, MS Access database
I want to have several tables which have links to the fields to other
tables. The following example explains the basic idea.
CREATE TABLE husbands (
id COUNTER,
name TEXT,
age LONG,
|
by: shriil |
last post by:
Hi
I have this database that calculates and stores the incentive amount
earned by employees of a particular department. Each record is
entered
by entering the Date, Shift (morn, eve, or night) and the 'employee
name'. There is another table which assigns an ID to the Shifts, i.e.
1,2 and 3 for morn, eve & night shifts respectively. From the mother
table, the incentive is calculated datewise for each employee as per
his shift duty. In...
|
by: BobRoyAce |
last post by:
Let's say that I am performing a bunch of insert/update queries within
a transaction that is created as follows:
Dim cnn As New
SqlClient.SqlConnection(My.Settings.GRPConnectionString)
cnn.Open()
Dim trx As SqlClient.SqlTransaction = cnn.BeginTransaction()
Further suppose that, in the middle of all of the insert/update
queries, I want to execute a SELECT query against one of the tables
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |