Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Repeated values in query

Status
Not open for further replies.

Cassiopeia

Technical User
Sep 30, 2001
9
FI
Hi,

I am wondering if someone here is able to help me in this matter. To the point: I have a table with some "missing" lines and I want to produce an output that always have the same rows (the missing rows should be included). E.g.
SH_203_e9e1ho.jpg

As you can see the production for product2 is missing, no production for that product and there is no way to get a line with 0 from the production system. It simply leaves a zero line unreported.

How can I write a query that generates the four lines for every product regardless of if they are in the initial data or not? I have tried to do another table with the four keys and join that but I can't figure out how the query should be written so that all lines were printed repeatedly for the missing lines...

Thanks, Cas
 
Is that the representation of your data (one table) shown in ‘initial data’ picture?
Or do you have your data normalized and in more than just one table?


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.
 
Sounds like an outer join is probably needed if tables are joined to produce a record set.

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
The data is in just one table. And what comes to outer join I also tried that but I only got something like this:
Capture_rcpb1m.jpg


The wanted "empty" line is missing (Production for Product2) and all I got was the extra rows in the beginning. And as I mentioned, I want all of the four keys to be listed for every product no matter if they exist in the initial data or not.
 
How about a cartesian query.

First Query contains two tables ([InitialData] and [HelpTable])
Second Query joins [Cartesian] with [InitialData]

Code:
SELECT InitialData.Product, HelpTable.Key
FROM HelpTable, InitialData
GROUP BY InitialData.Product, HelpTable.Key;

Code:
SELECT qryCartPlan.*, InitialData.[Quantity]
FROM qryCartPlan 
LEFT JOIN InitialData 
ON (qryCartPlan.InitialData.[Product]=InitialData.[Product]) 
AND (qryCartPlan.HelpTable.[Key]=InitialData.[Key]);

If you need sorted a particular way other than alphabetical, would need to add a sorting scheme.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top