Query on one account only

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My database has 4 tables
They are:
1. Customer's Database basic info
2. Tanktest results for gas stations owned by these customers
3. Leaktest results for gas stations owned by these customers
4. Linetest results for gas stations owned by these customers
Any Customer may own several gas stations, therefore I may do different work
for the same customer at different locations.
The tables are linked through the Customer's ID number.
How do I make a query and print a report for a single customer's records of
tests.
In other words, I want to be able to print a report that would show a
history of all test at all diferent locations for a specific customer.
Right now I am able to show a report with the information I am seeking but
it is showing all customers. I want to be able to hand my customer a report
with his data only.
Thanks in advance
 
use the customer's id as criteria. in the select query,
which should be the record source for the report, put the
customer's id in the cirteria pane. if you want to be
prompted for the id put [Enter Customer's ID]
 
Bikermandude,
Just a thought:
If the test result data points for the tank, leak and line are similar (i.e.
Leaks, leakrate, LeakLocation, etc.), you would be better served by using
one "TestResult" table with a field to identify the test type.
The reason for this is that with the structure you have now, you are pretty
much stuck with doing a union query to get the distinct records for a
particular client or doing three separate queries and manipulating the
results. Your Union query will be something like
Select Field1, Field2, Field3, ... from TankTest where Customerid = <CustID>
UNION
Select Field1, Field2, Field3, ... from LeakTest where Customerid = <CustID>
UNION
Select Field1, Field2, Field3, ... from LineTest where Customerid = <CustID>

In addition, the field lists in the three select statements must match as to
type and size order or the query will fail, which again points to thinking
about consolidating the tables.

If the data were in one table you could "select Field1, Field2, Field3, ...
from TestResult where Customer = <CustID>" to get all the tests for a
customer or "select Field1, Field2, Field3, ... from TestResult where
Customer = <CustID> and TestType = <SomeTest>" to get the particular tests
for a customer.
HTH,
CF
 
Back
Top