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
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
259 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

809 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