Selecting data from table to query

  • Thread starter Thread starter Strong-Metal
  • Start date Start date
S

Strong-Metal

Hi,

If I have a table that has product number, price, and date, and for each
product number, there could be multiple entries of price, how can I set up a
query that will show the part number and it's price, qualified by the latest
date?

I guess what I'm trying to say is if I enter a new price for an exisiting
part number, how can I set up a query to show only the new price (latest
date).

Any help would be greatly appreciated. Thank you.
 
SELECT [product number], [price], Max([date])
FROM [YourTable]
GROUP BY [product number], [price];
 
Thanks, Karl.

Being a newbie, not sure how that would be set up. Would I be setting this
up in a select query, crosstab? I tried PN, Price, Max([date]) in a regular
select query and got all the prices. If you can elaborate a bit more, it
would be greatly appreciated.

Thanks.

KARL DEWEY said:
SELECT [product number], [price], Max([date])
FROM [YourTable]
GROUP BY [product number], [price];
--
KARL DEWEY
Build a little - Test a little


Strong-Metal said:
Hi,

If I have a table that has product number, price, and date, and for each
product number, there could be multiple entries of price, how can I set up a
query that will show the part number and it's price, qualified by the latest
date?

I guess what I'm trying to say is if I enter a new price for an exisiting
part number, how can I set up a query to show only the new price (latest
date).

Any help would be greatly appreciated. Thank you.
Click to expand...
Click to expand...
 
Change the names in the [ ] to your field names, in query grid change view
to sql and paste the code in there. Run, see what happens.
Strong-Metal said:
Thanks, Karl.

Being a newbie, not sure how that would be set up. Would I be setting this
up in a select query, crosstab? I tried PN, Price, Max([date]) in a
regular
select query and got all the prices. If you can elaborate a bit more, it
would be greatly appreciated.

Thanks.

KARL DEWEY said:
SELECT [product number], [price], Max([date])
FROM [YourTable]
GROUP BY [product number], [price];
--
KARL DEWEY
Build a little - Test a little


Strong-Metal said:
Hi,

If I have a table that has product number, price, and date, and for
each
product number, there could be multiple entries of price, how can I set
up a
query that will show the part number and it's price, qualified by the
latest
date?

I guess what I'm trying to say is if I enter a new price for an
exisiting
part number, how can I set up a query to show only the new price
(latest
date).

Any help would be greatly appreciated. Thank you.
Click to expand...
Click to expand...
Click to expand...
 
I tried. Even made a new table with sample data, just 3 part numbers, each
part number having 3 different prices with 3 different dates associated with
it. Ran the code in sql and got all 9 entries. Tried it again by deleting 2
of the part numbers, leaving just 1 part number with 3 different prices and
dates. Ran the code and got 3 entries.

Not sure what I'm doing wrong here.

Pete D. said:
Change the names in the [ ] to your field names, in query grid change view
to sql and paste the code in there. Run, see what happens.
Strong-Metal said:
Thanks, Karl.

Being a newbie, not sure how that would be set up. Would I be setting this
up in a select query, crosstab? I tried PN, Price, Max([date]) in a
regular
select query and got all the prices. If you can elaborate a bit more, it
would be greatly appreciated.

Thanks.

KARL DEWEY said:
SELECT [product number], [price], Max([date])
FROM [YourTable]
GROUP BY [product number], [price];
--
KARL DEWEY
Build a little - Test a little


:

Hi,

If I have a table that has product number, price, and date, and for
each
product number, there could be multiple entries of price, how can I set
up a
query that will show the part number and it's price, qualified by the
latest
date?

I guess what I'm trying to say is if I enter a new price for an
exisiting
part number, how can I set up a query to show only the new price
(latest
date).

Any help would be greatly appreciated. Thank you.
Click to expand...
Click to expand...
Click to expand...
 
Sorry, I should of read this closer. First do a create table query.

SELECT Table1.PartNumber, Max(Table1.DateSold) AS MaxOfDateSold INTO
MyNewTable
FROM Table1
GROUP BY Table1.PartNumber;

Then use this with join to original table to get whole record.

SELECT Table1.*
FROM Table1 INNER JOIN MyNewTable ON (Table1.DateSold =
MyNewTable.MaxOfDateSold) AND (Table1.PartNumber = MyNewTable.PartNumber);



Strong-Metal said:
I tried. Even made a new table with sample data, just 3 part numbers, each
part number having 3 different prices with 3 different dates associated
with
it. Ran the code in sql and got all 9 entries. Tried it again by deleting
2
of the part numbers, leaving just 1 part number with 3 different prices
and
dates. Ran the code and got 3 entries.

Not sure what I'm doing wrong here.

Pete D. said:
Change the names in the [ ] to your field names, in query grid change
view
to sql and paste the code in there. Run, see what happens.
Strong-Metal said:
Thanks, Karl.

Being a newbie, not sure how that would be set up. Would I be setting
this
up in a select query, crosstab? I tried PN, Price, Max([date]) in a
regular
select query and got all the prices. If you can elaborate a bit more,
it
would be greatly appreciated.

Thanks.

:

SELECT [product number], [price], Max([date])
FROM [YourTable]
GROUP BY [product number], [price];
--
KARL DEWEY
Build a little - Test a little


:

Hi,

If I have a table that has product number, price, and date, and for
each
product number, there could be multiple entries of price, how can I
set
up a
query that will show the part number and it's price, qualified by
the
latest
date?

I guess what I'm trying to say is if I enter a new price for an
exisiting
part number, how can I set up a query to show only the new price
(latest
date).

Any help would be greatly appreciated. Thank you.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top