query to compare values inthe same field + a String

[Santiago Gomez]

Hello,
I have field called tblProjetcts.ProjectNumber. I need a query that shows
any records that contain a "P" inside the string (char) (example: 04444P).
That's easy.
The hard part, is that some projects with a P in it have a corresponding
number with no P (example: 04444), and that means the project is now active
and thus I do not want to see it (either the 0444P or the 0444) in my
results.

I was thinking about creating using vb to go though each record (with a P),
and searching another complete recordset to find a match for the same
number but no P, then discarding that record, and then MoveNext.

kinda like this:
rs.open "select * from tblProjects where ProjectNumber like '%P%'"
rsCompare.open "select * from tblProjects"

do while not rs.eof
if rs("ProjectNumber") = 'some loop that goes through all the rsCompare
records
.movenext
else ' if there is no match
'add this to another recordset or write to a temporary table
enf if
..movenext
loop

Any help would be greatly appreciated.
Thanks

 

[Tom Ellison]

Dear Santiago:

First, I would suggest you not use a recordset for this if you can do
it in a query. And I think you can do it in a query.

Next, this whole question seems to center around
tblProjects.ProjectNumber. Is it the case that, when a ProjectNumber
contains a letter, it is always at the end? Or could you have a
ProjectNumber "044P44"? If so, how would you define the "number" part
of it?

I'm going to assume that the ProjectNumbers all have 5 digits that
form the "number" portion followed by a single letter suffix.

Since there is apparently a relationship between rows with the same
"number" portion but different suffixes, what is happening here is
that you have a combination of two functionally separate columns
recorded together in one column.

A good approach, short of fixing the table and breaking some existing
software, would then be to write a query that simply separates this
column into two columns so you can base future work on that "properly
normalized" query rather than the table which is not normalized.

Using my understanding of the composition of the ProjectNumber column,
that would be:

SELECT LEFT(ProjectNumber, LEN(ProjectNumber) - 1) AS ProjectNum,
RIGHT(ProjectNumber, 1) AS ProjectSuffix, <other columns go here>
FROM tblProjects

Saving this as qryProjectNorm, you can then do this:

SELECT *
FROM qryProjectNorm T1
INNER JOIN qryProjectNorm T2 ON T2.ProjectNum = T1.ProjectNum
WHERE T1.ProjectSuffix = "P"

Please let me know if this gets you toward your goal.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[John Vinson]

Hello,
I have field called tblProjetcts.ProjectNumber. I need a query that shows
any records that contain a "P" inside the string (char) (example: 04444P).
That's easy.
The hard part, is that some projects with a P in it have a corresponding
number with no P (example: 04444), and that means the project is now active
and thus I do not want to see it (either the 0444P or the 0444) in my
results.

This would be a HECK of a lot easier if you weren't violating first
normal form by storing two pieces of information in the same field.
Could you consider using a two-field primary key, with the
ProjectNumber in one field and the status in another?

I was thinking about creating using vb to go though each record (with a P),
and searching another complete recordset to find a match for the same
number but no P, then discarding that record, and then MoveNext.

kinda like this:

MUCH more complex than needed.

A Subquery will do the job:

SELECT * FROM Projects
WHERE ProjectNumber LIKE "*P"
AND NOT EXISTS
(SELECT ProjectNumber FROM Projects AS P
WHERE P.ProjectNumber LIKE Left([Projects.ProjectNumber], 5) & "*");

 

[Santiago Gomez]

It worked. thanks.
SQL is so versatile.

Hello,
I have field called tblProjetcts.ProjectNumber. I need a query that shows
any records that contain a "P" inside the string (char) (example: 04444P).
That's easy.
The hard part, is that some projects with a P in it have a corresponding
number with no P (example: 04444), and that means the project is now active
and thus I do not want to see it (either the 0444P or the 0444) in my
results.

This would be a HECK of a lot easier if you weren't violating first
normal form by storing two pieces of information in the same field.
Could you consider using a two-field primary key, with the
ProjectNumber in one field and the status in another?

I was thinking about creating using vb to go though each record (with a P),
and searching another complete recordset to find a match for the same
number but no P, then discarding that record, and then MoveNext.

kinda like this:

MUCH more complex than needed.

A Subquery will do the job:

SELECT * FROM Projects
WHERE ProjectNumber LIKE "*P"
AND NOT EXISTS
(SELECT ProjectNumber FROM Projects AS P
WHERE P.ProjectNumber LIKE Left([Projects.ProjectNumber], 5) & "*");