Extracting information

[Prayder]

Stack ID= bundle of wood
Location = place for wood
Move Date= date stack id was moved
Move Time= time stack id was moved

I have a query that pulls each of these fields(stack id, location, move date, move time) from a table and displays the info into a report. The problem I am having is that if a stack id is moved to two different locations in the same date.. the report shows the stack id in two different locations instead of just the last one. How can I get the report to show the stack id once and in the last location it was?

 

[Brianwarnock]

Max time,

You would need to run a totals query grouping on Id and date and max on time

Then join this back to the table to pick up the location.

Brian

 

[Prayder]

Hmmm,
Not sure if I follow what your saying,
This is the current SQL statement I have for the query:

   SELECT Location.[Stack ID], Location.Location, Location.[Move Date], Location.[Move Time]
FROM Location
WHERE (((Location.[Move Date])=[Please enter the date]));

 

[Brianwarnock]

Ok so you are restricting the date.

It is best not to have spaces or special characters in object and field names then not only do you not need [] but access can use intellisense to help you as you progress, especially in VBA .

Query1

Select stackid, movedate, max(movetime) as requiredtime
From location
Where movedate = [ Enter move date]
Group by stackid,movedate

Query2 the one you run the system runs query1
Select query1.stackid, location.location,query1.movedate,location.movetime
From location innerjoin query1 on location.stackid=query1.stackid and location.movedate=query1.movedate and location.movetime= query1.reuiredtime


I think that's it give or take the odd typo

The idea is to select the max time for the grouping and then go back and get the other data, in this case the location

Brian

 

[Prayder]

Wow.... I guess I would have something like 6 queries kick off.

Here is a word document that shows how when clicking to run the report it kicks off the first query which in turn kicks off the next and so forth. When I click on the command to run the report I get a box that comes up saying "Please enter the date"

I type in the date I am looking for and I get another box that comes up asking Location Query-daily.MaxOfMove Date

So many queries... very confusing as to what they are all doing..

took over this db from someone else who left the company 5-6 years ago and they have been "managing" since..

 

[Prayder]

On that second query:

   SELECT qryLocationtest.[Stack id], location.location, qryLocationtest.[Move Date], location.[Move Time]

FROM location innerjoin qrylocationtest on location. [stack id] = qrylocationtest.[stack id] AND location.[move date] = qrylocationtest.[move date] AND location.[move time] = qrylocationtest.requiredtime

I keep getting a syntax error right after the innerjoin on the from clause?

 

[JHB]

innerjoin shall be in two words = Inner Join.
Then you have a space between "location.[stack id]" must be = location.[stack id]

 

[Prayder]

Ok,

I created the two queries but unfortunately when they run, they still pull duplicate location data for the stack id. I have attached an excert of what I am referring to.

Stack ID: 299794 shows up in the dry storage and the plant because it was moved twice on 7/8. I am trying to make it to where it will only shows up for the last location which in this instance would be plant.

 

[JHB]

I can't open none of your attachments, I've only Excel 2000 and Word 2000, but maybe your have got the problem solved in meantime.

 

[Brianwarnock]

I also could not open the file in 2002, the message was that it was not in a recognisable format, odd as it is XLS.

Brian