Complicated Query

[cableguy47905 via AccessMonster.com]

This has been very frustrating because I know I should be able to get this.
I am just going around in circles with it though.
I have three different tables.
The only common field is the Project field.
One table has all Pending Projects sent by my group.
One table has all Reported Projects by all groups.
One table has all Resolved Projects by all groups.

Of course all of the Resolved Projects are in the Reported table, but there
are also some resolved projects that are not in the Pending table.

Some Reported Projects are in both of the other two tables, but there are
also some that are not in either table.

Basically what I want is a query that will show all of the projects with the
correct status. If the project is in the Resolved table, then it should only
show once with the status of "Resolved". If the project is only in the
Reported table, then it should only show once with the status of "Reported".
And the same for the Pending table projects. However, there are instances
where the project is in more than one table in I want it to only show up once
with the correct status. The hierarchy is Resolved, Reported, Pending.

How can I accomplish this? I know SQL, but it has been a little while and I
am a bit rusty at it. Can anyone help me with this?

Thanks in advance,
Lee

 

[Guest]

Use one table only with a field for status.

If you need to track when it changed status then use a one-to-many
relationship between a project table and status table.

Your query would be for the latest dated status.

 

[cableguy47905 via AccessMonster.com]

Thanks Karl,

The problem is that these actually start out as emails that are sent to the
different folders. My team starts out the process with the pending project,
then the help desk will send out an email to everyone once they find it to be
an actual problem, then if they have a resolution, they send out yet another
email and finally if it was cancelled, we get another email.

When I get them, I have to manipulate the data so that I can pull out the
Project number. Each email will have different information, so I can't use
the Project number as a PK. I like the idea of the status table, but I am
not sure how I would accomplish this.

Does this make any sense?

Use one table only with a field for status.

If you need to track when it changed status then use a one-to-many
relationship between a project table and status table.

Your query would be for the latest dated status.

This has been very frustrating because I know I should be able to get this.
I am just going around in circles with it though.

[quoted text clipped - 23 lines]

Thanks in advance,
Lee

 

[Guest]

Your status table would have a foreign key (number - integer) to match the PK
of the project table. You set the one-to-many relationship by clicking on
TOOLS - Relationships.
Then you use a form for project and subform for status. They are link on
thePK-FK as Master-Child. The subform would be data sheet format to be able
to view multiple status at one time.

--
KARL DEWEY
Build a little - Test a little

Thanks Karl,

The problem is that these actually start out as emails that are sent to the
different folders. My team starts out the process with the pending project,
then the help desk will send out an email to everyone once they find it to be
an actual problem, then if they have a resolution, they send out yet another
email and finally if it was cancelled, we get another email.

When I get them, I have to manipulate the data so that I can pull out the
Project number. Each email will have different information, so I can't use
the Project number as a PK. I like the idea of the status table, but I am
not sure how I would accomplish this.

Does this make any sense?

Use one table only with a field for status.

If you need to track when it changed status then use a one-to-many
relationship between a project table and status table.

Your query would be for the latest dated status.

This has been very frustrating because I know I should be able to get this.
I am just going around in circles with it though.

[quoted text clipped - 23 lines]

Thanks in advance,
Lee