Query that doesn't show all records from multiple tables/queries

G

GaryDennis

Using Access 2000, I am trying to make query for production based upon two
variables; "Location" and "Employees." Each location and each employee is
assigned to a specific market, and the location and individual could be in
the same market. I created an inner join, trying all possible combos of join
type, on the common field of "market," but I keep getting the query that
shows every record for individual production by market along with every
record for location production by market (or some variation depending upon
the join) So, if I have one employee, and one location, my query shows the
employee and production, then location production which also includes the
employee's production again. This is what the query result looks like:

Market employee location Employee production
Location Production
LA Jones 5

LA Mid City 5
100

Any assistance is greatly appreciated. Thank you.
Gary
 
G

GaryDennis

In Query 1, "Location Production", which is based upon the Table "Location"
I used an expression to get location production for 4 days:
[DayOne.Avail]+[DayTwo.Avail]+[DayThree.Avail]+[DayFour.Avail] and these
daily totals come from 4 seperate queries which calculate the totals for
each of the four days and are joined inside of the "Location Production
Query." The fields in the " "Location Production" query include each of the
four separate day's totals, then the sum of all four with the above
expression.

In Query 2, "Employee Production", which is based upon the Table "Employee",
the total is calculated with the expression Nz([Field & Sup
Capacity2])+Nz([Field & Sup Capacity3])+Nz([Field & Sup Capacity4]). Only
the four day sum field ifor "Employees" is included in the joined query of
"Location Production." The queries "Location Production" and "Employee
Production are joined by "Market". The other, single day totals queries
inside the "Location Production" query are "Location Name" and "Location ID."

The "Location Production" query is a bit trickier for me because it was
designed by someone else and scrapes it's data from a mainframe, thus, the
underlying queries for separate days, and the underlying queries are involved
and themselves linked to other tables. The "Employee Production" data is
input manually into a form.

I appreciate your time and any assistance you can provide.

Thank you
 
K

KARL DEWEY

I asked for your SQL - anyway --

You say your Query! is based on 4 queries. How are they joined?

I can not follow this. Please post the data source structure that provides
the data, like --
Table1 --
Location - text
Production Date - date
Hours Worked - number - single

Query1 --
Location - text
Work Date - date
SumOfHours - number

etc. --

Query 1 - Location Production --
SELECT Table1.Location, Table1.[Production Date], etc..

GaryDennis said:
In Query 1, "Location Production", which is based upon the Table "Location"
I used an expression to get location production for 4 days:
[DayOne.Avail]+[DayTwo.Avail]+[DayThree.Avail]+[DayFour.Avail] and these
daily totals come from 4 seperate queries which calculate the totals for
each of the four days and are joined inside of the "Location Production
Query." The fields in the " "Location Production" query include each of the
four separate day's totals, then the sum of all four with the above
expression.

In Query 2, "Employee Production", which is based upon the Table "Employee",
the total is calculated with the expression Nz([Field & Sup
Capacity2])+Nz([Field & Sup Capacity3])+Nz([Field & Sup Capacity4]). Only
the four day sum field ifor "Employees" is included in the joined query of
"Location Production." The queries "Location Production" and "Employee
Production are joined by "Market". The other, single day totals queries
inside the "Location Production" query are "Location Name" and "Location ID."

The "Location Production" query is a bit trickier for me because it was
designed by someone else and scrapes it's data from a mainframe, thus, the
underlying queries for separate days, and the underlying queries are involved
and themselves linked to other tables. The "Employee Production" data is
input manually into a form.

I appreciate your time and any assistance you can provide.

Thank you


KARL DEWEY said:
Post your SQL for the query.
Click to expand...
Click to expand...
 
G

GaryDennis

Sorry, Karl. Is this what you needed?

Thank you


SELECT DriveIns.DIName, DriveIns.DICode, DayOne.Avail, DayTwo.Avail,
DayThree.Avail, DayFour.Avail,
[DayOne.Avail]+[DayTwo.Avail]+[DayThree.Avail]+[DayFour.Avail] AS [Four Day],
[Tech Capacity Query].[Four Day], [PRO Capacity Query].[Four Day Capacity]
FROM (((((((DriveIns LEFT JOIN DayZero ON DriveIns.DIName = DayZero.DIName)
LEFT JOIN DayOne ON DriveIns.DIName = DayOne.DIName) LEFT JOIN DayTwo ON
DriveIns.DIName = DayTwo.DIName) LEFT JOIN DayThree ON DriveIns.DIName =
DayThree.DIName) LEFT JOIN DayFour ON DriveIns.DIName = DayFour.DIName) LEFT
JOIN DayFive ON DriveIns.DIName = DayFive.DIName) LEFT JOIN DaySix ON
DriveIns.DIName = DaySix.DIName) INNER JOIN ([PRO Capacity Query] INNER JOIN
[Tech Capacity Query] ON [PRO Capacity Query].Market = [Tech Capacity
Query].Market) ON DriveIns.Market = [Tech Capacity Query].Market;


KARL DEWEY said:
I asked for your SQL - anyway --

You say your Query! is based on 4 queries. How are they joined?

I can not follow this. Please post the data source structure that provides
the data, like --
Table1 --
Location - text
Production Date - date
Hours Worked - number - single

Query1 --
Location - text
Work Date - date
SumOfHours - number

etc. --

Query 1 - Location Production --
SELECT Table1.Location, Table1.[Production Date], etc..

GaryDennis said:
In Query 1, "Location Production", which is based upon the Table "Location"
I used an expression to get location production for 4 days:
[DayOne.Avail]+[DayTwo.Avail]+[DayThree.Avail]+[DayFour.Avail] and these
daily totals come from 4 seperate queries which calculate the totals for
each of the four days and are joined inside of the "Location Production
Query." The fields in the " "Location Production" query include each of the
four separate day's totals, then the sum of all four with the above
expression.

In Query 2, "Employee Production", which is based upon the Table "Employee",
the total is calculated with the expression Nz([Field & Sup
Capacity2])+Nz([Field & Sup Capacity3])+Nz([Field & Sup Capacity4]). Only
the four day sum field ifor "Employees" is included in the joined query of
"Location Production." The queries "Location Production" and "Employee
Production are joined by "Market". The other, single day totals queries
inside the "Location Production" query are "Location Name" and "Location ID."

The "Location Production" query is a bit trickier for me because it was
designed by someone else and scrapes it's data from a mainframe, thus, the
underlying queries for separate days, and the underlying queries are involved
and themselves linked to other tables. The "Employee Production" data is
input manually into a form.

I appreciate your time and any assistance you can provide.

Thank you


KARL DEWEY said:
Post your SQL for the query.

:

Using Access 2000, I am trying to make query for production based upon two
variables; "Location" and "Employees." Each location and each employee is
assigned to a specific market, and the location and individual could be in
the same market. I created an inner join, trying all possible combos of join
type, on the common field of "market," but I keep getting the query that
shows every record for individual production by market along with every
record for location production by market (or some variation depending upon
the join) So, if I have one employee, and one location, my query shows the
employee and production, then location production which also includes the
employee's production again. This is what the query result looks like:

Market employee location Employee production
Location Production
LA Jones 5

LA Mid City 5
100

Any assistance is greatly appreciated. Thank you.
Gary
Click to expand...
Click to expand...
Click to expand...
 
Top