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
257 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

770 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