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
253 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Combobox issue 4 27
MS Access Query Help For Graph Preparing 13 16
Access Date Query 28 29
How to calcualate lateness in Access 2010 11 28
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

760 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

20 Experts available now in Live!

Get 1:1 Help Now