CODEWITHSUNDEEP
sqlms-accessms-access-2007
higgsy
higgsy

Reputation: 2011

Access join on first record

I have two tables in an Access database, tblProducts and tblProductGroups.

I am trying to run a query that joins both of these tables, and brings back a single record for each product. The problem is that the current design allows for a product to be listed in the tblProductGroups table more than 1 - i.e. a product can be a member of more than one group (i didnt design this!)

The query is this:

select tblProducts.intID, tblProducts.strTitle, tblProductGroups.intGroup 
from tblProducts 
inner join tblProductGroups on tblProducts.intID = tblProductGroups.intProduct 
where tblProductGroups.intGroup = 56 
and tblProducts.blnActive 
order by tblProducts.intSort asc, tblProducts.curPrice asc

At the moment this returns results such as:

intID | strTitle | intGroup
1     | Product 1 | 1
1     | Product 1 | 2
2     | Product 2 | 1
2     | Product 2 | 2

Whereas I only want the join to be based on the first matching record, so that would return:

intID | strTitle | intGroup
1     | Product 1 | 1
2     | Product 2 | 1

Is this possible in Access?

Thanks in advance Al

Upvotes: 0

Views: 6564

Answers (4)

VapoRizer
VapoRizer

Reputation: 341

This works for me. Maybe this helps someone:

SELECT 
    a.Lagerort_ID,
    FIRST(a.Regal) AS frstRegal,
    FIRST(a.Fachboden) AS frstFachboden, 
    FIRST(a.xOffset) AS frstxOffset, 
    FIRST(a.yOffset) AS frstyOffset, 
    FIRST(a.xSize) AS frstxSize, 
    FIRST(a.ySize) AS frstySize, 
    FIRST(a.Platzgr) AS frstyPlatzgr, 
    FIRST(b.Artikel_ID) AS frstArtikel_ID, 
    FIRST(b.Menge) AS frstMenge, 
    FIRST(c.Breite) AS frstBreite, 
    FIRST(c.Tiefe) AS frstTiefe, 
    FIRST(a.Fachboden_ID) AS frstFachboden_ID, 
    FIRST(b.BewegungsDatum) AS frstBewegungsDatum, 
    FIRST(b.ErzeugungsDatum) AS frstErzeugungsDatum
FROM ((Lagerort AS a) 
LEFT JOIN LO_zu_ART AS b ON a.Lagerort_ID = b.Lagerort_ID)
LEFT JOIN Regal AS c ON a.Regal = c.Regal
GROUP BY a.Lagerort_ID
ORDER BY FIRST(a.Regal), FIRST(a.Fachboden), FIRST(a.xOffset), FIRST(a.yOffset);

I have non unique entries for Lagerort_ID on the table LO_zu_ART. My goal was to only use the first found entry from LO_zu_ART to match into Lagerort.

The trick is to use FIRST() an any column but the grouped one. This may also work with MIN() or MAX(), but I have not tested it.

Also make sure to call the Fields with the "AS" statement different than the original field. I used frstFIELDNAME. This is important, otherwise I got errors.

Upvotes: 2

JK0124
JK0124

Reputation: 395

It's not very optimal, but if you're bringing in a few thousand records this will work:

  1. Create a query that gets the max of tblProducts.intID from one table and call it qry_Temp.
  2. Create another query and join qry_temp to the table you are trying to join against, and you should get your results.

Upvotes: 0

HansUp
HansUp

Reputation: 97131

Create a new query, qryFirstGroupPerProduct:

SELECT intProduct, Min(intGroup) AS lowest_group
FROM tblProductGroups 
GROUP BY intProduct;

Then JOIN qryFirstGroupPerProduct (instead of tblProductsGroups) to tblProducts.

Or you could do it as a subquery instead of a separate saved query, if you prefer.

Upvotes: 1

garnertb
garnertb

Reputation: 9594

This option runs a subquery to find the minimum intGoup for each tblProducts.intID.

SELECT tblProducts.intID
, tblProducts.strTitle
, (SELECT TOP 1 intGroup 
   FROM tblProductGroups 
   WHERE intProduct=tblProducts.intID 
   ORDER BY intGroup ASC) AS intGroup
FROM tblProducts 
WHERE tblProducts.blnActive 
ORDER BY tblProducts.intSort ASC, tblProducts.curPrice ASC

Upvotes: 4

Related Questions