N
NTC
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
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