Re: join method

Tech-Archive recommends: Fix windows errors by optimizing your registry

Sorry, I'm not following your description.

Please post the SQL statement of your query.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

"NTC" <NTC@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:02085CFD-3B85-49A9-A017-95C44152A0D4@xxxxxxxxxxxxxxxx
no query has all; so while your suggestion will give 'all' of 1 query - -
it
can't give overall 'all' because so single query contains the all....

can make a list of all by joining all customers w/ employees and implement
what you suggest...it does work...just strikes me as a messy solution
though.....

"Jeff Boyce" wrote:

Open the query in design view.

Highlight one of the join lines and right-click it. The pop-up window
will
(probably) indicate that you are looking for records where the values
match
in BOTH tables.

I believe you want the choice that lets you see all the records in ONE
table, and any that match in the OTHER.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

"NTC" <NTC@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6FAF2C76-78C1-44A8-B137-483B7A8ACB92@xxxxxxxxxxxxxxxx
have 7 queries on 7 tables, 1:1 for explanation can use just 2:
sales,
expenses : the query is date range & geo region.... all work fine.

need a master query to join (for a report source) where a record
contains
unique customer/employee data joined

all 7 queries always have a customerID and employeeID along with
appropriate
other values i.e. sales amount, expense amount, salescalls, etc.

if I join on CustomerID field I can get this:

CustA Sales$100 Empl1 CustA Expenses$20 Empl1
CustA Sales$100 Empl1 CustA Expenses$15 Empl2
(first 3 fields from sales query, second 3 fields from expenses query)
which is correct Employee 1 did have a sales of $100 and expenses of
$20;
while employee 2 also did have expenses of $15 but had no sales in
this
time
period from CustA. There is no CustA $0 sales entry for Empl2 of
course;
so
the problem being that this sales amount listed with employee B is
misleading...but I do understand the results from this join

if I join on both CustomerID and EmployeeID I only will get the first
line;
because as an AND there is no employ2 sales so the employ2 expense
doesn't
get joined into the results.

what is needed is:
CustA Sales$100 Empl1 CustA Expenses$20 Empl1
CustA Expenses$15 Empl2

normally I could rely on the Report itself to display this via
sorting/grouping; however there are alot of other calculated fields
that
need
manipulation not being discussed here suffice to say that it would be
more
ideal to have this data set established in a query result.

none of the 7 queries will have every customer/employee combination;
so
there is no "lead" or master dataset to drive a join to all the other
queries. I could create one from the customerListtable and
employeeListtable
that would establish every customer/employee combination and then make
the
join from this on both customer field and employee field. I feel
certain
it
would return the correct results but seems very inefficient and that
there
is
a better method - but even scripting sql join for every And/Or
possiblity
among the 7 sets of data seems awfully messy too.

would welcome a sanity check....tia









.

Relevant Pages

  • Re: finding overlaps
    ... Create a query into this table. ... Drag MeetingDate and CustomerID into the grid. ... >> Accept Group By in the Total row under these fields. ... Drag EmployeeID into the grid. ...
    (microsoft.public.access.queries)
  • Re: Group By?!
    ... If I were to take out that huge group by clause, ... Let's try and demonstrate the principle with a simpler query. ... Now lets group by CustomerID and EmployeeID, ...
    (microsoft.public.access.devtoolkits)
  • Re: finding overlaps
    ... Create a query into this table. ... Drag MeetingDate and CustomerID into the grid. ... Accept Group By in the Total row under these fields. ... Drag EmployeeID into the grid. ...
    (microsoft.public.access.queries)
  • Q: How to make correlated subquery do what I want?
    ... Here's a query to run against Northwind. ... select employeeid, customerid, orderdate ...
    (microsoft.public.sqlserver.programming)
  • Re: Advanced Calculated Queries
    ... Create a new query. ... Add the Bookings table (I assume this contains CustomerID). ... Microsoft Office/Access MVP ...
    (microsoft.public.access.queries)