Selecting last 3 dates in query

[ryne23]

I need to create a query that will select the last three dates entered. If the last three dates are not consecutive days is where I am having problems.

Thanks
Mike

 

[lespaul]

what is the SQL for the query you already have? how is is not working?


Leslie

 

[ryne23]

I am just creating the query now. I have three fields.

Date
AvgWeight
AvgRange

The data entered in will skip dates, that is where I don't know whow to write the code to pick the last three dates entered.

Thanks
Mike

 

[lespaul]

OK, so switch to the SQL view and you'll see something like:


SELECT Date, AvgWeight, AvgRange FROM SomeTableName WHERE SomeConditions = 'Something'

What are your table names? Is your field really named Date? I would change that to something meaningful like OrderDate or InvoiceDate (just 'Date' will cause you trouble later on since it's a reserved word in Access and most programming languages).

Are you designing this database? Is it in 3NF? If not, I would review 'The Fundamentals of Database Design' -

http://www.alphabetcitydataworks.com

in the Developer's section.



Leslie

 

[ryne23]

Here is the SQL. The date names is actually EntryDate.

SELECT SpcTbl.EntryDate, Avg(SpcTbl.ItemNumber) AS AvgOfItemNumber, Avg(SpcTbl.WeightAverage) AS AvgOfWeightAverage, Avg(SpcTbl.RangeAverage) AS AvgOfRangeAverage
FROM SpcTbl
GROUP BY SpcTbl.EntryDate;

 

[lespaul]

SELECT EntryDate, Avg(ItemNumber) AS AvgOfItemNumber, Avg(WeightAverage) AS AvgOfWeightAverage, Avg(RangeAverage) AS AvgOfRangeAverage
FROM SpcTbl WHERE EntryDate IN (SELECT TOP 3 EntryDate from SpcTlb Order by EntryDate DESC)
GROUP BY SpcTbl.EntryDate;


Leslie

 

[ryne23]

Thanks a lot. I will give this a try.

Mike

 

[ryne23]

Hello again.

I used this code and it works to pull a set number of date entries. Now what I need is to pull a set number of date entries into a query that will include a field that is called ItemNumber, and when ran will include all of the item numbers in the query with the specified number of date entries.

Thanks
Mike

 

[lespaul]

Sorry Mike, I'm not sure what you are asking for. Could you give me some more details? So the query I gave you gets you what you are looking for and now you need to do something more with this data?

Leslie

 

[ryne23]

I have a table where all of the products that we produce are stored. What I need is to be able to pull all of the item numbers and the averages for the last three dates for each of the item numbers. Does this make sense?

Thanks
Mike

 

[lespaul]

ok, let's start at the beginning.

You have a table of items, what are the fields in this table? You have dates that correspond to these items, are they in the same table or a different table? If a different table, what is the name of that table and the fields in it. How are the two tables related? You want to average something, what table is it in? If it's in a different table, I'll need that table and field name information.

Thanks!

Leslie

 

[ryne23]

The item table, MainSpecTbl, is separate from the entry table, MainEntryTbl.

In the item table I have Item#, Description, UnitOfMeasure. In the entry table I have EntryDate, Lane6Avg, Lane6Standard, ItemNumber. The two tables are linked togethere by the two Item number fields. What I want to average is the Lane6Avg and Lane6Standard, and show all of the averages for the last 3 dates for all of the item numbers.

thanks
Mike

 

[lespaul]

So let's say that Item#1 has these entries in tblEntry. You want to find the last three dates in tblEntry:

EntryDate Lane6Avg Lane6Standard ItemNo
5/10/04 15 7 1
5/1/04 12 6 1
4/15/04 10 8 1
4/10/04 15 3 1
4/1/04 16 9 1

You would want the average of Lane6Avg for the dates 5/10, 5/1 and 4/15?
So your results would look like:

ItemNO Item Desc AvgLane6Avg AvgLane6Standard
1 Item#1Desc 12.333 7


And you want these averages for all items in tblItems even if there is no entry for them in tblEntry (or will all the items have entries?)?



Leslie

 

[ryne23]

You have it right. Yes every item number will have entries but not all will be from the same dates.

 

[lespaul]

Ok, try this (I'm a beginner at correlated sub-queries, so this may not be quite right).

SELECT E.ItemNo, I.ItemDesc, Avg(E.Lane6Avg) AS AvgOfLane6Avg, Avg(E.Lane6Standard) AS AvgLane6Standard

FROM tblEntries E INNER JOIN tblItems I on E.ItemNo = I.ItemNo

WHERE E.EntryDate IN (SELECT TOP 3 E2.EntryDate from tblEntries E2 WHERE E.ItemNo = E2.ItemNo Order by E2.EntryDate DESC)
GROUP BY E.ItemNo, I.ItemDesc;

HTH

Leslie

 

[ryne23]

I will give it a try and let you know.

I really appreciate all your help with this.

Thanks
Mike

 

[ryne23]

When I run the query as I have it below it is averaging all entries. Any ideas?

SELECT Avg(EntryQry.Item) AS AvgOfItem, Avg(EntryQry.Last72Avg6Lane) AS AvgOfLane6Avg, Avg(EntryQry.Last72Standard6Lane) AS AvgLane6Standard, MainSpecTbl.Description
FROM (MainSpecTbl INNER JOIN EntryQry ON MainSpecTbl.[Item#] = EntryQry.Item) INNER JOIN MainEntryQry ON MainSpecTbl.[Item#] = MainEntryQry.[Item#]
WHERE (((MainEntryQry.entryDate) In (SELECT TOP 3 MainEntryQry.Date from EntryQry MainEntryQry WHERE EntryQry.Item = MainEntryQry.Item Order by MainEntryQry.entryDate DESC)))
GROUP BY MainSpecTbl.[Item#], [MainSpecTbl.description];

Thanks
Mike

 

[lespaul]

that's because you are trying to average the ItemNo? You will need to have ItemNo as a field in the SELECT for this to work? Why would you want to average the item number anyway?

If all you have are the two tables you mentioned (tblItem and tblEntries) what are MainEntryQry and MainSpecTbl and EntryQry?

You should be able to change the table and field names in the query I wrote and it should work without any other changes!

The only reason I joined into the Item table was to get the item description.

Try this query against your TblEntries. Not your queries, just your table (if your tblEntries is set up like you said above):

SELECT E.ItemNo, Avg(E.Lane6Avg) AS AvgOfLane6Avg, Avg(E.Lane6Standard) AS AvgLane6Standard

FROM tblEntries E 
WHERE E.EntryDate IN (SELECT TOP 3 E2.EntryDate from tblEntries E2 WHERE E.ItemNo = E2.ItemNo Order by E2.EntryDate DESC)
GROUP BY E.ItemNo;

Leslie

 

[ryne23]

Sorry, I was working with your code on two different queries. I copied and pasted your code into the query and ran the query. It still looks like it is averaging all the dates.

Thanks
Mike