I dont't know how to tell what the problem is

[Guest]

Hello,
I am going crazy trying to figure this one out. I have a table with 3000
records. I want to find the price that coresponds to the id number. the
problem some of the prices have changed every quarter, other are the same
siince 1998. When I try a query with the dates between I am getting the
multiple for the ones that have changed every so often. Is there any way to
get the record and a price just once. Any help will save my sanity. Thank
you.

 

[Jason Lepack]

Why not just take the most recent price?

tbl_mac:
part_id - number
part_price - currency
date_modified - date/time

qry_mac:
SELECT A.part_id, A.part_price, A.date_modified
FROM tbl_mac AS A
INNER JOIN [SELECT part_id, max(date_modified) AS LastMod
FROM tbl_mac
GROUP BY part_id]. AS B
ON (A.date_modified = B.LastMod) AND (A.part_id = B.part_id);

This query will find the most recent date modified for each part and
then match it up with it's price.

Cheers,
Jason Lepack

 

[Guest]

Jason,
Thank you so much, I got it to work but I am not getting all the records. My
records total 6397 and I am only getting 2814. I cannot figure out why and it
is driving me crazy. Any idea what I am doing wrong? Again thank you for
your help.

--
thank you mac

Why not just take the most recent price?

tbl_mac:
part_id - number
part_price - currency
date_modified - date/time

qry_mac:
SELECT A.part_id, A.part_price, A.date_modified
FROM tbl_mac AS A
INNER JOIN [SELECT part_id, max(date_modified) AS LastMod
FROM tbl_mac
GROUP BY part_id]. AS B
ON (A.date_modified = B.LastMod) AND (A.part_id = B.part_id);

This query will find the most recent date modified for each part and
then match it up with it's price.

Cheers,
Jason Lepack

Hello,
I am going crazy trying to figure this one out. I have a table with 3000
records. I want to find the price that coresponds to the id number. the
problem some of the prices have changed every quarter, other are the same
siince 1998. When I try a query with the dates between I am getting the
multiple for the ones that have changed every so often. Is there any way to
get the record and a price just once. Any help will save my sanity. Thank
you.

 

[Jason Lepack]

How many distinct parts do you have?

Use this query:

SELECT DISTINCT part_id FROM tbl_mac

If it returns 2814 then the query is doing exactly what I think it
should be doing.

I don't think you are doing anything wrong, you just have 3583 outdated
prices.

Cheers,
Jason Lepack

Jason,
Thank you so much, I got it to work but I am not getting all the records. My
records total 6397 and I am only getting 2814. I cannot figure out why and it
is driving me crazy. Any idea what I am doing wrong? Again thank you for
your help.

--
thank you mac

Why not just take the most recent price?

tbl_mac:
part_id - number
part_price - currency
date_modified - date/time

qry_mac:
SELECT A.part_id, A.part_price, A.date_modified
FROM tbl_mac AS A
INNER JOIN [SELECT part_id, max(date_modified) AS LastMod
FROM tbl_mac
GROUP BY part_id]. AS B
ON (A.date_modified = B.LastMod) AND (A.part_id = B.part_id);

This query will find the most recent date modified for each part and
then match it up with it's price.

Cheers,
Jason Lepack

 

[Jason Lepack]

You are going to have to give me a few rows of sample input and the
expected output because I'm not following you.

Hi,I
I am not explaining myself very well. I have 6000 records(some of which
have the same id) and I need to get the latest price for the 6000 records.
The fields I am using are facility, plan, service, rx, description, ID,
quanity, date. price.
When I run the query I want to get the lastest price for all 6000 records .
Does this help? Again thank you I have spent too much time and not getting
anywhere. Thank you

--
thank you mac

How many distinct parts do you have?

Use this query:

SELECT DISTINCT part_id FROM tbl_mac

If it returns 2814 then the query is doing exactly what I think it
should be doing.

I don't think you are doing anything wrong, you just have 3583 outdated
prices.

Cheers,
Jason Lepack

Jason,
Thank you so much, I got it to work but I am not getting all the records. My
records total 6397 and I am only getting 2814. I cannot figure out why and it
is driving me crazy. Any idea what I am doing wrong? Again thank you for
your help.
--
thank you mac
:
Why not just take the most recent price?
tbl_mac:
part_id - number
part_price - currency
date_modified - date/time
qry_mac:
SELECT A.part_id, A.part_price, A.date_modified
FROM tbl_mac AS A
INNER JOIN [SELECT part_id, max(date_modified) AS LastMod
FROM tbl_mac
GROUP BY part_id]. AS B
ON (A.date_modified = B.LastMod) AND (A.part_id = B.part_id);
This query will find the most recent date modified for each part and
then match it up with it's price.
Cheers,
Jason Lepack
mac wrote:
Hello,
I am going crazy trying to figure this one out. I have a table with 3000
records. I want to find the price that coresponds to the id number. the
problem some of the prices have changed every quarter, other are the same
siince 1998. When I try a query with the dates between I am getting the
multiple for the ones that have changed every so often. Is there any way to
get the record and a price just once. Any help will save my sanity. Thank
you.

 

[John Vinson]

I need to get the latest price for the 6000 records.

When I run the query I want to get the lastest price for all 6000 records .

It sounds like that's exactly and precisely what you're asking for.

Each record has one and only one price in that record. That price is
ipso facto "the latest price" for that record.

Maybe you're not clearly expressing what you want - do you want the
latest price *for each ID* (in which case Jason's query is in fact
returning the correct result)? Or do you want the latest price (a
single record) in the entire table?

John W. Vinson[MVP]

 

[Guest]

Hello,
I am sorry not to have answered you. I am just going crazy. I tried to put
it on a back burner but the powers that be want it now. I have and id # that
has different categories 1 throug 6. The price can change on any of the
catagories at different times for example 1 can change in Januarty cat 2 can
change in December. I want to be able to get the latest change for each
category. I have tried the formula that was posted but it does not give me
all categories. I am at a loss. Any help will be greatly appreciated.
thank you mac

 

[John W. Vinson]

Hello,
I am sorry not to have answered you. I am just going crazy. I tried to put
it on a back burner but the powers that be want it now. I have and id # that
has different categories 1 throug 6. The price can change on any of the
catagories at different times for example 1 can change in Januarty cat 2 can
change in December. I want to be able to get the latest change for each
category. I have tried the formula that was posted but it does not give me
all categories. I am at a loss. Any help will be greatly appreciated.
thank you mac

Mac, You can see your database. We cannot. I do not know the names of
the fields in your table, their datatypes, etc.

Jason's code should work, if you make the appropriate changes to your
table's fieldnames (which Jason couldn't see either). You WILL get
fewer rows in the result than your entire table - after all, you have
6000 rows, but lots of them *ARE NOT THE LATEST* row; you specifically
want to *exclude* those rows, and see only the latest row for each
category. If you want to see 6000 rows, then either your expectations
aren't realistic, or neither Jason nor I have correctly intuited your
table structure.

Please post the definition of your table in the format:

Tablename
Fieldname Datatype
Fieldname Datatype
Fieldname Datatype

for at least the relevant fields.

This is a very straightforward problem, and we'll be glad to help if
you'll give us the information that we need in order to be able to do
so.

John W. Vinson [MVP]

 

[Guest]

I don't know what I am doing wrong. I am getting messeage "Syntax
error(missing operator) in query expression" Below is the query as i did it.
Thank you again for your help!!!!!

SELECT A.ndcid, A.price, A.effectivedate
FROM tbl_OmniAS A
INNER JOIN [SELECT ndc id, max(date_modified) AS LastMod
FROM tbl_Omni
GROUP BY ndc id]. AS B
ON (A.date_modified = B.LastMod) AND (A.ndc id = B.ndc id);
--
thank you mac

Why not just take the most recent price?

tbl_mac:
part_id - number
part_price - currency
date_modified - date/time

qry_mac:
SELECT A.part_id, A.part_price, A.date_modified
FROM tbl_mac AS A
INNER JOIN [SELECT part_id, max(date_modified) AS LastMod
FROM tbl_mac
GROUP BY part_id]. AS B
ON (A.date_modified = B.LastMod) AND (A.part_id = B.part_id);

This query will find the most recent date modified for each part and
then match it up with it's price.

Cheers,
Jason Lepack

Hello,
I am going crazy trying to figure this one out. I have a table with 3000
records. I want to find the price that coresponds to the id number. the
problem some of the prices have changed every quarter, other are the same
siince 1998. When I try a query with the dates between I am getting the
multiple for the ones that have changed every so often. Is there any way to
get the record and a price just once. Any help will save my sanity. Thank
you.

 

[John Spencer]

The first thing I see is that you need a space between tbl_Omni and AS.
And you have spaces in NDC ID field. Hopefully, the field is really ndcId
with no spaces.

If the field does have spaces, then you cannot do this in Access SQL with
only one query. Access requires brackets [] around field names with spaces,
but it does not allow [] within a subquery used in the from clause.

So if the field is [Ndc Id] then youou will have to use two queries. The
first query to get the ndc ID and max date and saved as query. And then
join that saved query with the table to get your results.

SELECT A.ndcid, A.price, A.effectivedate
FROM tbl_Omni AS A
INNER JOIN [SELECT ndcid, max(date_modified) AS LastMod
FROM tbl_Omni
GROUP BY ndcid]. AS B
ON (A.date_modified = B.LastMod) AND (A.ndc id = B.ndc id);

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I don't know what I am doing wrong. I am getting messeage "Syntax
error(missing operator) in query expression" Below is the query as i did
it.
Thank you again for your help!!!!!

SELECT A.ndcid, A.price, A.effectivedate
FROM tbl_OmniAS A
INNER JOIN [SELECT ndc id, max(date_modified) AS LastMod
FROM tbl_Omni
GROUP BY ndc id]. AS B
ON (A.date_modified = B.LastMod) AND (A.ndc id = B.ndc id);
--
thank you mac

Why not just take the most recent price?

tbl_mac:
part_id - number
part_price - currency
date_modified - date/time

qry_mac:
SELECT A.part_id, A.part_price, A.date_modified
FROM tbl_mac AS A
INNER JOIN [SELECT part_id, max(date_modified) AS LastMod
FROM tbl_mac
GROUP BY part_id]. AS B
ON (A.date_modified = B.LastMod) AND (A.part_id = B.part_id);

This query will find the most recent date modified for each part and
then match it up with it's price.

Cheers,
Jason Lepack

Hello,
I am going crazy trying to figure this one out. I have a table with
3000
records. I want to find the price that coresponds to the id number.
the
problem some of the prices have changed every quarter, other are the
same
siince 1998. When I try a query with the dates between I am getting
the
multiple for the ones that have changed every so often. Is there any
way to
get the record and a price just once. Any help will save my sanity.
Thank
you.