Query results not correct (1 Viewer)

[mafhobb]

Hi all,

I've got a database that I have been developing for a little while. About 12 different users have been entering data in it for the last two years.

The main table I have is called "Project Main" where project details get specified. Tied to this table I have another one that is called "Project History", where the development of the project is recorded. One of the fields in that table includes current project status, such as "In progress" or "shipping" or "waiting for production"

Elsewhere in the database I have a query to help me find out what the current status of any project is. To do it, this query basically looks for the "last" history comment, last date and last status from the Project History table along with some other info from the Project Main table (see attached file). I use a combo box to define the status I am searching for.

This works well, except that on occasion, it seems like a particular project gets a status assigned to it at one point in time, but even if a new history comment and a new status is assigned to it later on, the query still returns the earlier status and comment, as if it did not see the new comment and status, which I have verified is in the Project History table. This means that I may add a comment to a project saying "product received" and change the status to "shipping" but if I run the query and look for projects that are "shipping", it may not find it. However if I run the query looking for projects that are "waiting for production" (earlier status) it will show up.

This only happens with a few projects not all projects, and it seems like when this happens with any particular project, it does not matter how many times the status is updated...the query just does not find it under any new status, only the old one.

It seems totally random what projects get affected by this, but once they are affected once, they are affected forever...

What is happening?

mafhobb

 

[pbaldy]

You probably want Max rather than Last:

http://support.microsoft.com/kb/208190

Also, see if this helps any:

http://www.baldyweb.com/LastValue.htm

 

[mafhobb]

Thanks for the suggestion. Here is what I have found:

Method #1 seemed to be closer to what I needed, so I created a query that calls for Project ID# and max of Comment Autonumber. This is the query at the top of the image.

The result of this query gives me exactly what I need and it is correct (the last entry for each ID #), but I only get that, the ID# and the Max Autonumber for that ID. Now I need to go get the rest of the info (comment, date, who made the comment...) that matches that Autonumber and ID#.

I am doing that with a 2nd query (the one below) that uses the 1st one as a basis. However, when I do this, the 2nd query does not limit its search to the results of the first query, and I end up with autonumbers that do not match the ones on the previous query...and so the wrong comments (comments that do belong to the ID #, but not the last ones for that ID#) are shown.

Why is the 2nd query not limiting its results to the autonumber results of the 1st one?

mafhobb

 

[pbaldy]

Do you have the joins between the 1st query and the table? Can you post the db?

 

[mafhobb]

I can try, but it is very large and it is secured (I am the admin).

I'll see if I can create a new workgroup and send you all the links/files. I doubt that it will fit in the space allotted for attachments here.

If you can enlighten me as to what you mean by "Do you have the joins between the 1st query and the table?" I could try that first

mafhobb

 

[pbaldy]

In my example:

FROM VehicleMiles INNER JOIN qryMaxDates
ON (VehicleMiles.CarNum = qryMaxDates.CarNum)
AND (VehicleMiles.DorDate = qryMaxDates.MaxDate)

In the design grid it/they would be represented by lines between the 2. Can you post a screen shot of the query in design view or the SQL of it?

 

[mafhobb]

Here is a screen shot of the queries.

 

[pbaldy]

Just as mine had joins on both the date and car number, I would expect yours to join on both ID field and that max of autonumber field.

By the way, the spaces and symbols in your names are not worth the bother in the long run, and the field named date is frowned on:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;209187

 

[mafhobb]

OK, I have now linked the autonumber field on the 2nd query and that seems to have done it!

Thanks for your help!!

BTW, I created this db a long time ago with limited knowledge. I have learned quite a bit since I did and at some point in the future I'll create a V2 version to leave behind all the stuff that is "not the norm" or "frowned upon".

Thanks for your comments as that helps me figure out what I should avoid.

mafhobb