Quering to find a status

[dbuck]

I have a table where the lay out is
Project # - Name - Role - Status - Status Date

Two types of Status can be listed in the Status column either open or closed. Status dates are set for each entry into the table by project number. I have no trouble getting the Closed projects, however when there are two entries on two different dates one for 'open' and 'one closed' both entries will show up in a report that should only list projects that are 'open'. The query currently is picking each status up on it's own date and dropping the 'open' date onto the open report and the 'closed' date on to the closed report making it look like the project has two entries, when it should only have one. (either open or closed)

I'm sure there is a way to get only Open projects, but it is eluding me.

[jzwp11]

Welcome to the forum!

Technically speaking, your database is not structured properly. If a project can have multiple statuses over time that describes a one(project)-to-many(statuses) relationship, which requires a separate but related table. Something along these lines would be more appropriate:


tblProjects
-pkProjectID primary key, autonumber
-txtProjectNo
other project fields

tblProjectStatus
-pkProjStatusID primary key, autonumber
-fkProjectID foreign key relating back to tblProjects
-statusDate
-status

In terms of your report you will need some queries. If a project can have multiple statuses, you need to find the most current status or in other words, for each project, you need to find the record in tblProjectStatus that has the most recent date in the statusDate field. To do this you need an aggregate query. This is what the query would look like in SQL view (SQL view is easier to show in a post compared to design view)

SELECT fkProjectID, Max(statusDate)
FROM tblProjectStatus
GROUP BY fkProjectID

You would then join this query back to the tblProjectStatus (using another query) via the fkProjectID field. This will allow you to bring in the status field.

Although you can use the same idea as I describe above with your current table structure, I would strongly recommend that you make the change to your table structure. Having the correct table structure is critical to a successful relational database, and it will save you a lot of headaches in the long run.


Just a couple general recommendations, the word "name" is a reserved word in Access, so it should not be used as a table or field name, so I suggest you change it to something else. Also, it is generally recommended to not have spaces or special characters (!,#, $,& etc.) in your table or field names. As a matter of fact, the # sign is a date delimiter in Access.

[dbuck]

Thank you! Still working the query suggestion but have not managed to return the dataset I would have expected.

Question regarding table structures. I 'inherited' this particular db and its to put it nicely faults. The two main tables were never assigned primary keys. Aside from using a simple record ID number as the key any other recommendations short of rebuilding each table?

[jzwp11]

You still might be able to accomplish what you are after with your current structure.

First the query to get the most recent status date for each project

query name: qryMostRecent
SELECT [Project #], Max([Status Date]) as MostRecentStatusDate
FROM yourtable
GROUP BY [Project #]

Now join the above query back to your project table; join by both project number and status date

query name: qryCurrentStatusOfAllProjects
SELECT yourtable.[Project #], yourtable.[Name], yourtable.Role, yourtable.Status, yourtable.[Status Date]
FROM yourtable INNER JOIN qryMostRecent ON yourtable.[Project #]=qryMostRecent.[Project #] AND yourtable.[Status Date]=qryMostRecent.MostRecentStatusDate

You can now use the above query to create 2 queries, one for open projects and one for closed projects

SELECT *
FROM qryCurrentStatusOfAllProjects
WHERE qryCurrentStatusOfAllProjects.Status="open"


SELECT *
FROM qryCurrentStatusOfAllProjects
WHERE qryCurrentStatusOfAllProjects.Status="closed"


Alternatively, you can make 1 query that references a control on an unbound form. The user would type either the word opened or closed in the control and then run the query below (via a command button)

SELECT *
FROM qryCurrentStatusOfAllProjects
WHERE qryCurrentStatusOfAllProjects.Status=forms!formnam e!controlname

[dbuck]

The problem was solved with last post. Thank you! Now if you could get the my users to input their data correctly! Thank you again.

[jzwp11]

Now if you could get the my users to input their data correctly!

I have the same problem! I try to use combo and list boxes whenever possible to present the user with preset choices that way I control some things--at least.