Another Impromptu Query

[mrpauly]

Hi again,

I have a query that runs fine until I add fields from another table. What I want is to have one unique record for each tracking number (I work at FedEx) shipment. The problem is one table contains one or more relatated records (same tracking number) in another table. The number of records in the query will be multipled on how many records are in that table. Example, 3 records in other table will report 3 records when all I want is 1 record.

Ultimately, I would like to see one record always for each tracking number and field showing the count of records for that one table. More specifically, the count of records when a field = 'Y' for that one table. Can this be done??

Thanks in advance.

Paul Millard
Senior Developer Anlayst
FedEx Freight

 

[griffindm]

Hi Paul,

You need to do one of two things:

1) You can create a hotfile from the second table and store the tracking number and the count of rows for each tracking number. You can then join (or outer join if it is possible that tracking numbers from the first table may not exist in the second table, but still need to be included) this hotfile to the first table for your report.

or

2) create a view on the second table in SQL that accomplishes the same result. The view can be joined or outer joined in the same fashion. The syntax is not difficult and examples exist here in this forum. Use the Search Keyword tab to find them.

In either case you ensure that each tracking row from the first table sees a maximum of one and only one row in the second table, thus avoid your '1-to-many' conundrum.

The advantage of the SQL view is that there is no need to refresh a hotfile to pickup new data in the second table. The disadvantage of the SQL view may be that it requires the ability to create a data structure in the database, which may not be possible in some security environments.

Hope this is helpful.

Regards,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"

http://www.decisionsupportgroup.com

 

[mrpauly]

Dave,

As always, thanks for your quick and helpful response to my question. You've helped me and others out numerous times and your valuable work does not go unnoticed.

Best wishes,
Paul