Add records if do not exist?

S

SusanV

Hi all,

I have a table with the following fields:

PlanID
PlanName
LOM
(plus other fields to be copied into the missing records, and a couple
fields to be left blank)

For each PlanID, there should be records for 4 LOMS: L1, L2, L3, and L4.
However, the data I received is fudged and for about a third of the Plans,
there are only records for L4 and L3, or only L4 and L2. It's a mess.

So basically I need to populate additional records of PlanID, PlanName, and
LOM = L2 (or whatever LOM is missing). Not ALL the Plans are missing LOMs,
and I can't quite get my head around how to loop through the table to
determine which ones are missing which LOMs. With around 300 PlanIDs I don't
want to do this manually. I'm sure it's not so tough as I'm feeling it is,
but I just can't seem to wrap my head around it. Without the missing records
everything comes to a screeching halt. <sigh>
Gotta love bad data. =(

Thanks in advance,

Susan
 
M

[MVP] S.Clark

If the data is like this:

PlanID
PlanName
LOM1
LOM2
LOM3

Then you could normalize the LOM's to a child table, which save the
head-wrecking of the loop you desire.

(If not, please ignore this post, and think about yummy ice cream.)

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
[email protected]
www.fmsinc.com/consulting
 
D

Dale Fye

Susan,

Where do you plan on getting the records that don't exist? Is it in another
database?

If all you want to do is identify which records are missing, then create a
new table (tbl_LOM) with one text field (LOM). Populate this table with 4
records (L1, L2, L3, L4).

Create a new query. Add the other table (I'll call it tbl_Plan) and tbl_LOM
to the query grid. Join the two tables on the LOM field, then modify the
join so that it returns all records from tbl_Plan (should be a LEFT join if
you drew your join line from tbl_Plan to tbl_LOM).

Add the PlanID field from tbl_Plan, and the LOM field from tbl_LOM to the
query grid, and in the Criteria line for the LOM field, enter IS NULL. When
you run this query, It will give you a list of all the PLANs and the LOMs
that are missing.

HTH
Dale
 
S

SusanV

Yummm, ice cream.... <grin>
No it's a single field, LOM stands for Level of Maintenance (4 levels, L1 -
L4) and no the data is not normalized, and I don't have control of the data.
This is data someone sent me in an excel file that I need to use to
manipulate some OTHER data (also incomplete) that they sent last week. In
other words, basically I have a mess to sort out using messy data to sort it
with.

<thinking of ice cream... or at least coffee!>
 
S

SusanV

Thanks I'll give that a try. The null fields won't matter in the result but
my code is gagging on the lack of matching records.

Appreciate your help,

Susan
 
S

SusanV

Good morning Dale,

I *believe* I did this as you described, but when I add the Is Null it
returns no records. Here's the query with the Is Null:

SELECT tblPMOPlans.MaintenancePlanId, tblLOM.LOM
FROM tblPMOPlans LEFT JOIN tblLOM ON tblPMOPlans.LEVEL = tblLOM.LOM
WHERE (((tblLOM.LOM) Is Null))
ORDER BY tblPMOPlans.MaintenancePlanId;


If I remove the Is Null, I can see where LOMs are missing, for example:
MaintenancePlanId LOM
0 L1
0 L2
0 L3
0 L4
2 L2
2 L3
2 L4
3 L2
3 L3
3 L4



So your query should have returned, looking just at this sample data:

MaintenancePlanId tblLOM
2 L1
3 L1

Any idea why is this not working?
 
D

Dale Fye

Susan,

My fault, probably a brain cramp

Try this.

Query1.

SELECT PMO.MaintenancePlanID, LOM.LOM
FROM (SELECT Distinct MaintenancePlanID FROM tblPMOPlans) as PMO,
LOM

This query should give you all of the combinations of MaintenancePlanID and
LOM

Query2

SELECT Q1.MaintenancePlanID, Q1.LOM
FROM Query1 Q1
LEFT JOIN tblPMOPlans PMO
ON Q1.MaintenancePlanID = PMO.MaintenancePlanID
AND Q1.LOM = PMO.LOM
WHERE PMO.LOM IS NULL

This will identify the combinations of maintenance plani id and LOM that do
not have a matching value in tblPMOPLans

HTH
Dale
 
S

SusanV

Thanks Dale. I already went in and did this manually (fun stuff, took about
an hour) but I will keep this thread in my Access notes folder for future
reference!

Susan
 
Top