Solved

I have a query that returns multiple instances of the same record, but I need each subsequent instance to modify its key in the result set

Posted on 2006-06-21
10
261 Views
Last Modified: 2008-02-01
I have a Products table, a Sections table, & because each product could appear in more than one section, a ProductSections table. These tables all have a relationship (would that be a threesome?)

I also have a Query that is based on the ProductSections table, which will form the exported list of products in sections. I need to include the ProductID from the Products table, but I need each subsequent instance of the product to modify it slightly to avoid duplicates, as shown below:

 If ProductID = ABC123, then first instance = ABC123, second instance = 1!ABC123, third instance = 2!ABC123 etc.

How would I go about this?

Thanks
0
Comment
Question by:PaulCutcliffe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
10 Comments
 
LVL 58

Expert Comment

by:harfang
ID: 16955954
Hello PaulCutcliffe

This does not seem to be the way to go. The candidate key of ProductSections is the combination of the prouctID and the sectionID, no need for additional numbering.

    Products
    ------------
    ProductID * key field
    ProductName
    etc...

    Sections
    -----------
    SectionID * key field
    SectionName
    etc...

    ProductSections
    -----------------------
    ProductID * key field, also foreign key of Products
    SectionID * key field, also foreign key of Sections

Is that your setup?

If not, it would mean you have an additional (mostly useless) field in ProductSections being the "instance number" you want prefixed. But I fail to see the point...
(°v°)
0
 

Author Comment

by:PaulCutcliffe
ID: 16956017
That is exactly my set up, and I don't have an InstanceNumber field in the ProductSections table.

But the software I will be exporting to allows products to be duplicated, but requires that the ProductID is unique, with any duplicated products having their ProductID in the format:

First Instance:        ABC123
Second Instance: 1!ABC123
Third Instance:    2!ABC123

etc.

So I am not trying to make a unique ProductID for key purposes within the table, but to satisfy the software my query will be exporting to.
0
 
LVL 58

Accepted Solution

by:
harfang earned 300 total points
ID: 16956412
Is this absolutely fixed? Nothing for the first, then numbers 1-n?

If you use SectionID + '!' + ProductID, you could get:

    12!ABC123
    44!ABC123
    45!ABC123

Would that be accepted? Note that if the SectionID is not numeric, you can easily add an autonumber to the Sections table to get a unique number assigned to each, and use that number for your exports.

If you need your exact specifications, it's going to be messy...

    SELECT ProductID, SectionID
        (   Select Trim(Sum(1))
            From ProductSections TMP
            Where ProductID = ProductSections.ProductID
                And SectionID < ProductSections.SectionID
        )+'!' & ProductID AS TryThat
    FROM ProductSections
    ORDER BY ProductID, SectionID;

Good luck!
(°v°)
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 7

Expert Comment

by:ala_frosty
ID: 16956820
My 2¢

Suffix the productIDs. Trying to search for them after you prefix them is going to make for horrible sorts!

0
 

Author Comment

by:PaulCutcliffe
ID: 16958185
harfang: Fantastic! That works a treat, so the points are yours.

Before we close up though, would you mind explaining briefly how it works? I've upped the points by 50.

Thanks
0
 

Author Comment

by:PaulCutcliffe
ID: 16958191
Increase Points to 300
0
 

Author Comment

by:PaulCutcliffe
ID: 16958211
ala_frosty: If you meant 'stick the duplicate identifier on the end of the ProductID instead of the start', then I can't, as I am exporting to a software product that requires duplicates to be shown in this way.
0
 

Author Comment

by:PaulCutcliffe
ID: 16958643
I've added another question - similar to this one, but not quite similar enough that I can do it myself - see http://www.experts-exchange.com/Databases/MS_Access/Q_21895296.html
0
 
LVL 58

Expert Comment

by:harfang
ID: 16960366
PaulCutcliffe

The technique is called "subqueries". You can define a field content as being the result of a one-record, single field subquery, which can use the "parent" queries fields as criteria. This is the (Select ...) portion inside of the main query.

Here, the subquery basically says: count all identical products where the section number is smaller than the current one. Then I used a few tricks.

Count(*) would return 0 for the first product (the one with the lowest section number). Sum(1) on the other hand returns Null. Trim() is used to convert the number to a string, while still allowing the Null value to come through.

Then I use '+' to add the bang after the number, because '+' will work only if both sides are non-Null:

    '1' + '!'   ---> '1!'
    Null + '!'   ---> Null

The other operator is '&', and behaves differently:

    '1!' & 'ABC123'   ---> '1!ABC123'
    Null & 'ABC123'   ---> 'ABC123'

So it's a matter of: counting the number of "previous" products, but getting Null instead of Zero; adding the bang only if non-Null; and adding the ProductID in any case.

Does that explain? Feel free to ask if it's still unclear.

Cheers!
(°v°)
0
 

Author Comment

by:PaulCutcliffe
ID: 16960668
harfang: You're a star! That explains perfectly, not only how it works, but also why I couldn't see how it worked.

Thanks.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question