UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Parts / Prices Query, Access 2016    
 
   
faca
post Jul 11 2018, 06:47 AM
Post#1



Posts: 27
Joined: 21-May 18



Hello!

I have 2 tables:

Part:
- PartID
- PartNumber
- PartCode
- PartName
- Unit

Price:
- PriceID
- PartID
- SellingPrice
- NetPrice
- PurchasePrice
- PriceValidFrom


Problem is that i want to make query where part will be displayed with the lastest price (latest PriceValidFrom date)

Example:



Any suggestion how to do this? I tried to set PriceValidFrom total to Max but still all prices are displayed..
Go to the top of the page
 
cheekybuddha
post Jul 11 2018, 07:25 AM
Post#2


UtterAccess VIP
Posts: 10,343
Joined: 6-December 03
From: Telegraph Hill


welcome2UA.gif

Try a query like:
CODE
SELECT
  p.PartID
  p.PartNumber
  p.PartCode
  p.PartName
  p.Unit,
  pr.SellingPrice
  pr.PurchasePrice
  pr.NetPrice
  pr.PriceValidFrom
FROM Part p
INNER JOIN (
  Price pr1
  INNER JOIN (
    SELECT
      pr2.PriceID,
      MAX(pr2.PriceValidFrom)
    FROM Price pr2
    GROUP BY
      pr2.PriceID,
      pr2.PartID
   )
          ON pr1.PriceID = pr2.PriceID
) pr
        ON p.PartID = pr.PartID
ORDER BY
  p.PartID;

(Note: untested!)

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
BruceM
post Jul 11 2018, 07:33 AM
Post#3


UtterAccess VIP
Posts: 7,638
Joined: 24-May 10
From: Downeast Maine


Is the Price table related to the Parts table (one part, many prices over time)? If not, it should be. "Many" just means there could be more than one.

The problem with doing a grouping query is that you need to get the Max (latest) date, and the price associated with that record. However, grouping is performed on every unique combination of fields. The Max date is the latest for the combination of all the rest of the fields.

A better option may be a subquery. Here is an example. You would be doing TOP 1 rather than TOP 3.

There may be other options, but I'll leave it at that for now. Is the result to be read-only, or do you need to be able to edit the recordset?
Go to the top of the page
 
faca
post Jul 11 2018, 07:36 AM
Post#4



Posts: 27
Joined: 21-May 18



Part - (one-to-many) - Price

I only need to read this not edit..
This post has been edited by faca: Jul 11 2018, 07:38 AM
Go to the top of the page
 
BruceM
post Jul 11 2018, 07:55 AM
Post#5


UtterAccess VIP
Posts: 7,638
Joined: 24-May 10
From: Downeast Maine


Have you tried David's suggestion? To help understand what is going on you could take the SELECT Price pr2... section within the parentheses, paste it into SQL view of a query, and view the result. Just to be clear, "Price pr2" is the same as "Price As pr2". Because the same table is used several times, each separate instance needs its own alias just as pr2.

You could save that SQL as a query, and join to the query instead. The point is that the maximum date is being determined for each PartID. Those record are then joined to the same records in another instance of the table to determine the price on that date. Finally that result is combined with the Part fields.
Go to the top of the page
 
faca
post Jul 11 2018, 08:16 AM
Post#6



Posts: 27
Joined: 21-May 18



I tried but when i want to save query i get error

Go to the top of the page
 
cheekybuddha
post Jul 11 2018, 08:23 AM
Post#7


UtterAccess VIP
Posts: 10,343
Joined: 6-December 03
From: Telegraph Hill


Oh yeah, I managed to forget to add commas when copy/pasting - doh!
CODE
SELECT
  p.PartID,
  p.PartNumber,
  p.PartCode,
  p.PartName,
  p.Unit,
  pr.SellingPrice,
  pr.PurchasePrice,
  pr.NetPrice,
  pr.PriceValidFrom
FROM Part p
... etc ...


Warned you it was untested!!

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
faca
post Jul 11 2018, 08:28 AM
Post#8



Posts: 27
Joined: 21-May 18



I know you warned me and i saw that commas are missing but when i add them then there is "Syntax error in FROM clause"
Go to the top of the page
 
cheekybuddha
post Jul 11 2018, 08:39 AM
Post#9


UtterAccess VIP
Posts: 10,343
Joined: 6-December 03
From: Telegraph Hill


Wow! I really ought to have some more coffee!
CODE
SELECT
  p.PartID,
  p.PartNumber,
  p.PartCode,
  p.PartName,
  p.Unit,
  pr.SellingPrice,
  pr.PurchasePrice,
  pr.NetPrice,
  pr.PriceValidFrom
FROM Part p
INNER JOIN (
  Price pr1
  INNER JOIN (
    SELECT
      PriceID,
      MAX(PriceValidFrom)
    FROM Price
    GROUP BY
      PriceID,
      PartID
  ) pr2
          ON pr1.PriceID = pr2.PriceID
) pr
        ON p.PartID = pr.PartID
ORDER BY
  p.PartID;


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
faca
post Jul 11 2018, 08:47 AM
Post#10



Posts: 27
Joined: 21-May 18



Yes you really need coffee (still same error) laugh.gif laugh.gif and i'm stupid too beacuse i can't figure out myself what's wrong (i mean your access level of knowledge is higher than mine but still) big_grin.gif
Go to the top of the page
 
cheekybuddha
post Jul 11 2018, 09:32 AM
Post#11


UtterAccess VIP
Posts: 10,343
Joined: 6-December 03
From: Telegraph Hill


OK, last attempt otherwise I'm going to take the afternoon off and go back to bed!
CODE
SELECT
  p.PartID,
  p.PartNumber,
  p.PartCode,
  p.PartName,
  p.Unit,
  pr.SellingPrice,
  pr.PurchasePrice,
  pr.NetPrice,
  pr.PriceValidFrom
FROM Part p
INNER JOIN (
  SELECT
    pr1.*
  FROM Price pr1
  INNER JOIN (
    SELECT
      PartID,
      MAX(PriceValidFrom) AS LatestPriceValidFrom
    FROM Price
    GROUP BY
      PartID
  ) pr2
          ON pr1.PartID = pr2.PartID
         AND pr1.PriceValidFrom = pr2.LatestPriceValidFrom
) pr
        ON p.PartID = pr.PartID
ORDER BY
  p.PartID;

--------------------


Regards,

David Marten
Go to the top of the page
 
BruceM
post Jul 11 2018, 09:36 AM
Post#12


UtterAccess VIP
Posts: 7,638
Joined: 24-May 10
From: Downeast Maine


I don't understand how pr.SellingPrice, pr.PurchasePrice, etc. are available for selection since they aren't among the fields in Price pr.

Edit: Never mind. There they are.
Go to the top of the page
 
faca
post Jul 11 2018, 09:39 AM
Post#13



Posts: 27
Joined: 21-May 18



Oh god cheekybuddha you're the best!! it's actually working .. thank you ! notworthy.gif
Go to the top of the page
 
cheekybuddha
post Jul 11 2018, 09:54 AM
Post#14


UtterAccess VIP
Posts: 10,343
Joined: 6-December 03
From: Telegraph Hill


@Bruce - I know, I'm lazy using .* wink.gif

@faca, yw.gif Got there in the end! whew.gif

--------------------


Regards,

David Marten
Go to the top of the page
 
BruceM
post Jul 11 2018, 12:08 PM
Post#15


UtterAccess VIP
Posts: 7,638
Joined: 24-May 10
From: Downeast Maine


I won't tell if you don't wink.gif
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd September 2018 - 01:28 AM