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
254 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
  • 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
 
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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…

930 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now