select most recent date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

select most recent date

Author

Topic

querious
Starting Member

2 Posts

Posted - 2006-02-01 : 23:49:50

I am trying to write a select statement that for each SKU will give me the current cost. If a SKU is no longer active, I want to return the cost on the last date when that SKU was active.

My cost change table has the following format:
Company, SKU, Cost, Date_Active, Date_Inactive.

For example:
A 11111 $0.50 1/1/05 1/15/05
A 11111 $0.53 1/15/05 2/15/05
A 11111 $0.56 2/15/05
A 22222 $5.00 1/1/05 1/30/05

My problem is that the query I wrote (see below) does not work when there are future costs since LastActiveDate > Now. Please help. I am using MS Access 97. Thanks.

PARAMETERS [Provide a date or select OK for current data] DateTime;
SELECT [TblCost].Company, [TblCost].SKU, Max([TblCost].[DateActive]) AS LastActiveDate
FROM [TblCost]
GROUP BY [TblCost].Company, [TblCost].SKU
HAVING (((Max([TblCost].[DateActive]))<[Provide a date or select OK for current data] Or (Max([TblCost].[DateActive]))<Now()));

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-02-02 : 09:45:13

There are subtle and significant differences between TSQL and MS Access SQL. I suggest you post your problem on an Access forum, such as the one found at dbforums.com

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-02 : 09:57:34

What is the primary key of your table?

querious
Starting Member

2 Posts

Posted - 2006-02-02 : 10:07:24

There is no primary key. If I needed a unique identifier, I could contenate Company+SKU+DateActive

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-02 : 10:44:08

well, can't do much to help you. If your table doesn't have a PK, then there aren't too many sql statemetns you can write that will return good results, since your data will have no integrity and rows will not be uniquely identifiable.

If you go ahead and physically create the PK on those 3 columns, then we might be able to come up with something.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-02-02 : 12:32:42

I'm thinking you can just change your HAVING clause to a WHERE clause:

PARAMETERS [Provide a date or select OK for current data] DateTime;
SELECT	[TblCost].Company,
	[TblCost].SKU,
	Max([TblCost].[DateActive]) AS LastActiveDate
FROM	[TblCost]
GROUP BY [TblCost].Company,
	[TblCost].SKU
WHERE	[TblCost].[DateActive]<[Provide a date or select OK for current data]
	Or [TblCost].[DateActive]<Now();

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2006-02-02 : 13:50:48

As Dr Smith stated

Always.....Always......Always use a PK in ACCESS.

Jim
Users <> Logic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources