How To Find Missing Text Data?

[Lythandra]

Hi there,

I have a database system here that is supposed to create certain Cost Codes
for every Job/Phase that is created. This is an automated process and
generally works great. Sometimes tho, It will not create the Cost Codes
needed and this causes errors later usually discovered when importing in
Labor from our workers for Payroll.

What I am trying to do is find all missing Cost Codes (Which will be 2,
"3V-000" and "3V-100") for each Job/Phase.

There can be multiple Phases per Job.

This isn't a find unmatched as there isn't another table to compare it to. I
know there is a way to do this but my brain is coming up blank on the correct
way.

A gentle prod in the right direction would be appreciated.

Thanks

 

[Jerry Whittle]

Need more information. Do you have a Job table and a Phases table? Also a
Cost Code table? How are they all joined? Provide some sample data.

 

[Lythandra]

There are 3 tables related to this.

Table 1: (tbl Project ID) Contains the Project ID (Job ID) and related info

Table 2: (tbl Phase ID) Contains the Phase ID and related info (also has a
field for Project ID, can have multiple Phases per Project ID thus many
entries)

Table 3: (tbl Cost Codes) Contains the Cost Codes (also has fields for
Project ID and Phase ID), each Cost Code will be 2 distinct records in this
table per Project/Phase. One entry for 3V-000 and another for 3V-100. This is
the table where the Cost Codes are missing from.

btw I ride a 99 GT Xizang frame, If I didn't adore my frame so much I would
love to pick up an old Bontrager frame.

 

[Jerry Whittle]

I was afraid that it was a "everything in one table" problem. Correct me if
I'm wrong, but is the Project ID and Phase ID combination in tbl Cost Codes
the foriegn key to tbl Phase ID? I hope not as it's much easier or at least
accurate to deal with a single field foreign key.

See if the following works. Make sure to double check the field and table
names.

SELECT *
FROM [tbl Phase ID]
WHERE [tbl Phase ID].[ Project ID] & [tbl Phase ID].[ Phase ID] Not In
(SELECT [tbl Cost Codes].[ Project ID] & [tbl Cost Codes].[ Phase ID]
FROM [tbl Cost Codes]) ;

A TI mtb! Sweet. I ride a CF Trek roadie mostly.

 

[Lythandra]

Aye, It is everything in one table. This is actually from a Timberline
Database that I am interfacing with. Timberline is buggy fyi.

I did reproduce your example with the correct table and field names and I do
see the logic you are using. I got a "Data Type Mismatch In Criteria
Expression" error which frankly It shouldnt have got (same job fields and
phases). Regardless you have put me on the right track and I am confident
that I can fine tune it to work. Your help is much appreciated.

I rolled out a new Timekeeping system (which also interfaces with
Timberline) for the millwork shop here 2 days ago and today I am being
swamped with small requests for added features (which is expected) now that
they are actually using it. I am going to have to put this Cost Code check on
hold for a day or two while I do my quick updates.

Thanks Again

I was afraid that it was a "everything in one table" problem. Correct me if
I'm wrong, but is the Project ID and Phase ID combination in tbl Cost Codes
the foriegn key to tbl Phase ID? I hope not as it's much easier or at least
accurate to deal with a single field foreign key.

See if the following works. Make sure to double check the field and table
names.

SELECT *
FROM [tbl Phase ID]
WHERE [tbl Phase ID].[ Project ID] & [tbl Phase ID].[ Phase ID] Not In
(SELECT [tbl Cost Codes].[ Project ID] & [tbl Cost Codes].[ Phase ID]
FROM [tbl Cost Codes]) ;

A TI mtb! Sweet. I ride a CF Trek roadie mostly.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

There are 3 tables related to this.

Table 1: (tbl Project ID) Contains the Project ID (Job ID) and related info

Table 2: (tbl Phase ID) Contains the Phase ID and related info (also has a
field for Project ID, can have multiple Phases per Project ID thus many
entries)

Table 3: (tbl Cost Codes) Contains the Cost Codes (also has fields for
Project ID and Phase ID), each Cost Code will be 2 distinct records in this
table per Project/Phase. One entry for 3V-000 and another for 3V-100. This is
the table where the Cost Codes are missing from.

btw I ride a 99 GT Xizang frame, If I didn't adore my frame so much I would
love to pick up an old Bontrager frame.