INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Repeated values in query

Repeated values in query

(OP)
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.

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

RE: Repeated values in query

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.

RE: Repeated values in query

Sounds like an outer join is probably needed if tables are joined to produce a record set.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Repeated values in query

(OP)
The data is in just one table. And what comes to outer join I also tried that but I only got something like this:


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.

RE: Repeated values in query

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close