Multiple table query only returns partial data

[AC5FF]

I'm stuck and I'm hoping to find a good answer here...

I have a query set up that needs to pull data from three tables. Table "Quota" Table "Product" and Table "Work"
Quota tabe contains product ID, Month (date field), and Contracted
Product Table contains productID, costs, part#,etc
Work Table contains productID, noun, serial#, dates in, out, repair action, etc..
ProductID is a Key for the product table (can't be repeated).
The query I started links all 3 productID fields

Okay.. so I need to pull down all the information from the 'work' table for a given month. I use the product table to get noun information from and the quota table for quota's on the particular unit for the month.
If I build a query to give everything EXCEPT for the quota information, I get exactly what I need. However, as soon as I add in the quota table the only information displayed is the products that have quotas. There are 200+ products, but any given month may only have 5-10 products w/a quota.

SQL:

SELECT lru.Abbr, [Enterprise Repair Collaboration].Quota AS Contracted, Sum(IIf([work]![status]=4 And [work]!

=11 And [work]![tin date] Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())+1,0),1,0)) AS Repaired, ([Repaired]-[Contracted]) AS [Over/Short], Sum(IIf([work]![status]=1,1,0)) AS AWM, Sum(IIf([work]![status]=2,1,0)) AS AWP, Sum(IIf([work]![status]=3,1,0)) AS DWP
FROM (lru INNER JOIN [Work] ON lru.ID = Work.[LRU ID]) INNER JOIN [Enterprise Repair Collaboration] ON lru.Abbr = [Enterprise Repair Collaboration].LRU
WHERE (((lru.Enterprise)=True) AND (([Enterprise Repair Collaboration].Month) Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())+1,0)))
GROUP BY lru.Abbr, [Enterprise Repair Collaboration].Quota
ORDER BY [Enterprise Repair Collaboration].Quota DESC;
[/quote]
I have tried left joins, right joins, inner join, etc... Everything i try gives either totally wrong information (i.e. 5 products have quotas then all products display 5 times w/each quota amount) or displays only the items with quotas.
I have tried intermediate queries.  Take the work/product table, form that information and then another query to link the quota table to the first query.  Same results as above.

I know this has got to be a simple misstep someplace; but for the life of me I cannot find it!

I hope that someone out there might realize what i'm doing wrong and can help turn this o'l rusty lightswitch on in my head! :)

Thank You!

 

[AC5FF]

Oops; sorry all!

I wanted to paste the SQL in from my query (although I am terrrible reading/writing SQL) for an idea of what I had.

I didn't think about giving a short description of my tables to lable them as they'd be seen in the SQL. My Bad!

Quota = Enterprise Repair Collaboration table
Product = LRU table
Work = Work table --- I got one right!

 

[Remou]

Perhaps:

SELECT lru.Abbr, Quota.Quota AS Contracted, 
   Sum(IIf([work]![status]=4 And [work]![code]=11 
   And [work]![tin date] 
   Between DateSerial(Year(Date()),Month(Date()),1) 
   And DateSerial(Year(Date()),Month(Date())+1,0),1,0))  
   AS Repaired, 
   ([Repaired]-[Contracted]) AS [Over/Short], 
   Sum(IIf([work]![status]=1,1,0)) AS AWM, 
   Sum(IIf([work]![status]=2,1,0)) AS AWP, 
   Sum(IIf([work]![status]=3,1,0)) AS DWP
FROM (lru INNER JOIN [Work] ON lru.ID = Work.[LRU ID])
LEFT JOIN 
  (Select LRU,Quota 
   From [Enterprise Repair Collaboration] q 
   WHERE q.[Month] Between DateSerial(Year(Date()),Month(Date()),1) 
   And DateSerial(Year(Date()),Month(Date())+1,0)) Quota
ON lru.Abbr =Quota.LRU
WHERE lru.Enterprise=True
GROUP BY lru.Abbr,Quota.Quota
ORDER BY Quota.Quota DESC

Month in your table seems to be a date, is that correct?

Month is a reserved word, it may cause problems.

 

[AC5FF]

Remou...
Pretty new to this Access stuff But learning fast!
Month may very well be a reserved word (It is a date); i'm not sure, but the queries/etc I have gotten through have run w/out problems.
However, to make things easier I will go back in and change this field name.

As for your code. Formatting it like this makes it SO much easier to read! I am going to cut/paste it over and see where it will lead me.. Thanks!

 

[AC5FF]

Remou;

Didn't work. Not sure why. Just like I thought my query would work, this is still only returning the items listed in the quota table, not all of the items listed in the lru table that are flagged.

No problem though; it might not be the 'best' way to accomplish what i need, but I used three queries (delete/append/update) to basically build a table with the data I need to display. Works like a champ.

 

[cmmrfrds]

Maybe an explanation will help you get these records.

If you have 3 tables, say a
work table with workid, (pk) productid (fk)
product table with productid (fk)
quote table with productid (pk) quoteid (pk)

If the scenario is the work table holds activity, that may have a quote record, and both always contain product.

Then.

Select
w.workid,
w.productid,
q.quoteid
from work as w
Inner Join product as p
On w.productid = p.productid
Left Join quote q
On w.productid = q.productid

If there is no where clause it will give all the work records including those with no quote.
If the where clause references the quote record then only records with quotes will appear. In this case, if the work records with no quotes are needed then they must be explicitly included in the where clause.

What happens with a left join is that when there are no records on the right hand table in this example quote, then the database engine creates a null record for the right hand table. So, if you have Where quoteid = 1 then you can see that none of the null record have a quoteid = 1 so they are eliminated.

To ask for nulls use.

Where quoteid = 1 or quoteid is null
This will return the work records with no quote along with the ones where quoteid = 1.

 

[AC5FF]

If there is no where clause it will give all the work records including those with no quote.

The way I understand Inner/Left/Right joins is by the properties of the join line in design view of the query.
Inner would only include rows where the join fields from both tables are equal
Left would include all records from Table1 and only those from table 2 where equal
Right would include all records from Table2 and only those from table 1 where equal

However, if table 1 contains 5 records and table 2 contains 50 records, only 5 records will be displayed with a inner or a left join. If it is a right join I get 250 records..

 

[lespaul]

check out Understanding SQL Joins, you may find it helpful.

You say above:

If I build a query to give everything EXCEPT for the quota information, I get exactly what I need. However, as soon as I add in the quota table the only information displayed is the products that have quotas

Can you post THAT query? the one that works without the Quota information?



Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[cmmrfrds]

"Left would include all records from Table1 and only those from table 2 where equal"

This is wrong, all the records from table1 are included, which is the reason for the left join. The point of my example is that you can override this when the "where clause" is added if put criteria on the right hand table.

 

[cmmrfrds]

I miss spoke on the wrong part. You are correct all the records in table1 are included, but some records from table1 can be missed by applying criteria on table2. This is all I was hoping to make clear because it is easy to get caught on this.

 

[AC5FF]

LesPaul;
Here's the SQL for the part of the query that works. It is re-written a little; so that it appends a table instead of just getting the data.

Results from this query is 35 records; If i run this query (normal not append) and link this with my quota table (contains LRU, Quota, QMonth) (Qmonth is my new name for month); all I will get is 5 records (the # of LRUs that have a quota for the month). That's regardless of inner, right, or Left joins..... Okay, not totally accurate. I think it was 'right' join that returned 5*35 (175)records. Each of the original 35 records was listed 5 times with each quota amount...

SQL:

INSERT INTO [Enterprise Repair Status] ( LRU, Repaired, AWM, AWP, DWP, Area )
SELECT lru.Abbr,
Sum(IIf(work!status=4 And work!code=11 And work![tin date] Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())+1,0),1,0)) AS Repaired,
Sum(IIf(work!status=1,1,0)) AS AWM,
Sum(IIf(work!status=2,1,0)) AS AWP,
Sum(IIf(work!status=3,1,0)) AS DWP,
lru.Team
FROM lru INNER JOIN [Work] ON lru.ID = Work.[LRU ID]
WHERE (((lru.Enterprise)=True))
GROUP BY lru.Abbr, lru.Team;

 

[lespaul]

and which fields from the Quota table do you need? And you also need to return the LRUs that have NO quota?

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[AC5FF]

LesPaul
That's correct.. I basically just am trying to pull the Quota field from the Quota table. But I need ALL the LRUs from the LRU table (flagged Enterprise=true).

I need all of them because we also track repairs on those units; not just the ones we've got a quota on.

 

[lespaul]

and you need the quota field from the quota table where the productid matches the LRU.productID?

How about we start at an easier place. What happens if you run the query:

SELECT * FROM 
LRU L
LEFT JOIN QUOTA Q ON L.ProductID = Q.ProductID
WHERE L.Enterprise = true

does that get you all the LRUs and only those that have quotas? Does each LRU only have ONE quota?

Leslie

 

[AC5FF]

LesPaul

Did something get left out here or format errors? Not sure just because it seems to be missing information...

At anyrate... PLEASE don't kill yourself over this! I have got what I need working. It's probably not the "best" way, but it does work. I created a table to contain the information I needed, created a delete query to empty the table, I load the table with all the LRU work data and then I update the table with the quota information. 3 queries, but it does give me the list that i need. Okay, truth be told, 6 queries... (Had to do it twice, one for current and one for history data) I'm going to work on making this a single table/query group; but that's later down the road... I'm just happy I can display data now how Mgmt wants it...