Immediately copying data from one table to another via a relationship

[Peter]

I have two tables. One is an equipment listing (primary
key is the serial number), then I have another table which
is the maintenance history for the equipment. I have a
relationship set up in a One-to-Many style with Cascading
Updates selected (not Deletions).

I would like for this serial number to immediately be
copied from the listing table to the maintenance table.
The problem I have is that I don't necessarily have any
data to go into the maintenance history right away, but
unless I do, that equipment won't show up on a query or a
report that I wish to run.

In short, I would like for this data to come up in a query
or report even though all of the fields are null. I have
tried to insert a default value in one of the fields of
the maintenance table, but unless you actually type
something in, it doesn't get recorded.

Thanks for any help!!

Peter

 

[Immanuel Sibero]

Hi Peter,

If I understand correctly, you wish to automatically create at least one
record on the maintenance table for each equipment listing. The reason for
this is your query/report excludes equipments for which you dont have
maintenance records. If this is correct, the answer should be fixing the
query so that it includes all equipments regardless of whether or not they
have maintenance records.

Can you post your query?

Immanuel Sibero

 

[Guest]

Immanuel,

You've got it exactly right!! I've been fooling with this
in the MS Access program proper, not in Visual Basic. I
have a lot of experience with programming (Java, C++,
etc), but I haven't fooled around with VB at all, and
don't quite understand it when it comes up. So I try to
avoid it.

If you can tell me what it is that you need, I'm sure that
I can post it for you to take a look at.

Thanks in advance!!

Peter

 

[Immanuel Sibero]

Hi Peter,


What I'm looking for is the SQL text for the query that's missing the
equipment without maintenance records.
If you go into the database window, select the query and click on design,
right click on the upper window where the table fields and relationships are
shown, select SQL View. This will give you the text for the SQL command for
that query. You can then copy and paste this SQL text and post it here.

What I'm really looking for is the type of JOIN used in your query. If you
relate the equipment table to maintenance table using INNER JOIN, then the
query result would only give you equipments which has corresponding
maintenance records. If this is the case, then change they query to LEFT
JOIN.

HTH
Immanuel Sibero

 

[Guest]

Immanuel,

It worked!!! Just one thing, now in my query, the serial
numbers don't appear, and I would like them to. The spots
are there for them, but the fields are blank. Anyway
around this?

Also, and this is a minor issue, the serial numbers don't
appear in my Maintenance table. But I realize that is
minor.

Here is my SQL code, with the change of INNER JOIN to LEFT
JOIN. There's another table mentioned in here, Service
Agreements, that shouldn't have anything to do with this
issue.

Thanks again!!

Peter

SELECT [Service Agreements].[Customer], [Service
Agreements].[Job Number], [Service Agreements].[Coverage],
[Service Agreements].[Contract Comments], [Service
Agreements].[Account Manager], [Equipment - Listing].
[Store Number], [Equipment - Listing].[Equipment Model],
[Equipment - Listing].[Equipment Manufacturer], [Equipment
Maintenance].[Equipment Serial], [Equipment Maintenance].
[Filter Selection], [Equipment Maintenance].[Filter
Quantity], [Equipment Maintenance].[Belt], [Equipment
Maintenance].[Belt Quantity], [Equipment Maintenance].[ECT
Last Performed], [Equipment Maintenance].[Maintenance
Comments], [Equipment Maintenance].[Shutdown Parts],
[Equipment Maintenance].[Oil Type], [Equipment
Maintenance].[Oil Quantity], [Equipment Maintenance].[Last
Oil Change], [Equipment - Listing].[Secondary Tech],
[Equipment - Listing].[Lead Technician], [Equipment -
Listing].[Comments], [Equipment - Listing].[January],
[Equipment - Listing].[February], [Equipment - Listing].
[March], [Equipment - Listing].[December], [Equipment -
Listing].[May], [Equipment - Listing].[June], [Equipment -
Listing].[July], [Equipment - Listing].[August],
[Equipment - Listing].[September], [Equipment - Listing].
[October], [Equipment - Listing].[November], [Equipment -
Listing].[April]
FROM [Service Agreements] INNER JOIN ([Equipment -
Listing] LEFT JOIN [Equipment Maintenance] ON [Equipment -
Listing].[Equipment Serial]=[Equipment Maintenance].
[Equipment Serial]) ON [Service Agreements].[Customer]=
[Equipment - Listing].[Customer];

 

[Tim Ferguson]

I would like for this serial number to immediately be
copied from the listing table to the maintenance table.

The easy way to do this kind of thing is using a subform, which takes care
of all the linking stuff for you.

The problem I have is that I don't necessarily have any
data to go into the maintenance history right away,

In that case, there is no record to write surely? In a well designed
database there is never any reason to create blank records "in case". In
any case, assuming that there will be a new maintenance record for each
repair/ service for a given equipment item, you need a ServiceDate to
create the record too.

but
unless I do, that equipment won't show up on a query or a
report that I wish to run.

Yes you will: that is what outer joins are for. Try this

SELECT Equipment.Serial,
Equipment.Description,
Maintenance.ServiceDate,
Maintenance.ServiceType

FROM Equipment LEFT JOIN Maintenance
ON Equipment.Serial = Maintenance.ItemID

ORDER BY Equipment.Serial ASC, Maintenance.ServiceDate DESC;


and you will get a list of all the services, with NULL NULL against those
that have never had a service.

Hope that helps


Tim F

 

[Peter]

Tim,

It does, thanks very much. However, in my Maintenance
table, the serial numbers are not copied over, hence
those fields are blank. There are spots for them, but is
there any way to actually copy the serial numbers over?

The real reason for my desire to do this is to create a
report, in which I have all of our pieces of equipment
listed (by serial number as that is the primary key for
the maintenance table), complete with blank spaces so we
can fill them in later. This is going up as a large
poster on a wall for scheduling purposes, and I need all
pieces of equipment listed, regardless if there is any
maintenance done on them yet or not. Right now, when I
run that report, only equipment with a maintenance
history comes up. And now, with your help, blank spaces
come up where there should be serial numbers.

Thanks for any help!!

Peter

 

[John Vinson]

The real reason for my desire to do this is to create a
report, in which I have all of our pieces of equipment
listed (by serial number as that is the primary key for
the maintenance table), complete with blank spaces so we
can fill them in later. This is going up as a large
poster on a wall for scheduling purposes, and I need all
pieces of equipment listed, regardless if there is any
maintenance done on them yet or not. Right now, when I
run that report, only equipment with a maintenance
history comes up. And now, with your help, blank spaces
come up where there should be serial numbers.

You can create a Query linking the Equipment table to the Maintenance
table *by a Left Outer Join*. In the query grid, select the join line
and choose Option 2 (or 3) - "Show all records in Equipment and
matching records in Maintenance". Base your report for the wallchart
on this query.

The Serial Number should probably *NOT* be the primary key of the
maintenance table - a Primary Key is, by definition, unique in the
table; this would mean that one item of equipment will be allowed one
and only one maintenance event, ever! Is this what you intend? I'd see
the Serial Number as a foreign key, nonunique, instead.

 

[Guest]

John,

Perhaps I should clarify. I do have a subform to link the
tables. It's how I enter data in the first place, so I
think that's what you're talking about. Also, the serial
number is the primary key of the Equipment Listing table,
not the Maintenance table. I hope i didn't mistype....
Right now, my Maintenance table has no primary key (I'll
fix that later).

I'm sorry, but I don't understand what procedure you're
talking about in your first paragraph (talking about left
outer join and option 2 or 3). I am familiar with SQL, so
I understand the terminology, but not the "options".
Based on what another posting recommended (in this same
thread) I did change one of my statements from a JOIN to a
LEFT JOIN. Here it is if you would like to take a look at
it (it's very near the end). As a side note, there is
another table called Service Agreements that's farther
upstream that doesn't really apply to this problem. Just
to clarify the SQL.

I sincerely appreciate your help in this. Here's the SQL:

SELECT [Service Agreements].[Customer], [Service
Agreements].[Job Number], [Service Agreements].[Coverage],
[Service Agreements].[Contract Comments], [Service
Agreements].[Account Manager], [Equipment - Listing].
[Store Number], [Equipment - Listing].[Equipment Model],
[Equipment - Listing].[Equipment Manufacturer], [Equipment
Maintenance].[Equipment Serial], [Equipment Maintenance].
[Filter Selection], [Equipment Maintenance].[Filter
Quantity], [Equipment Maintenance].[Belt], [Equipment
Maintenance].[Belt Quantity], [Equipment Maintenance].[ECT
Last Performed], [Equipment Maintenance].[Maintenance
Comments], [Equipment Maintenance].[Shutdown Parts],
[Equipment Maintenance].[Oil Type], [Equipment
Maintenance].[Oil Quantity], [Equipment Maintenance].[Last
Oil Change], [Equipment - Listing].[Secondary Tech],
[Equipment - Listing].[Lead Technician], [Equipment -
Listing].[Comments], [Equipment - Listing].[January],
[Equipment - Listing].[February], [Equipment - Listing].
[March], [Equipment - Listing].[December], [Equipment -
Listing].[May], [Equipment - Listing].[June], [Equipment -
Listing].[July], [Equipment - Listing].[August],
[Equipment - Listing].[September], [Equipment - Listing].
[October], [Equipment - Listing].[November], [Equipment -
Listing].[April]
FROM [Service Agreements] INNER JOIN ([Equipment -
Listing] LEFT JOIN [Equipment Maintenance] ON [Equipment -
Listing].[Equipment Serial]=[Equipment Maintenance].
[Equipment Serial]) ON [Service Agreements].[Customer]=
[Equipment - Listing].[Customer];


Peter

 

[Tim Ferguson]

Also, the serial
number is the primary key of the Equipment Listing table,
not the Maintenance table. I hope i didn't mistype....
Right now, my Maintenance table has no primary key (I'll
fix that later).

Well, without a Primary Key it's not a table, and if it's not a table it
means that the whole thing is not a database; so prima facie all bets are
off. Still, we can continue looking.

This is the query, slightly legibilised:

SELECT [Service Agreements].[Customer],
[Service Agreements].[Job Number],
[Service Agreements].[Coverage],
[Service Agreements].[Contract Comments],
[Service Agreements].[Account Manager],
[Equipment - Listing].[Store Number],
[Equipment - Listing].[Equipment Model],
[Equipment - Listing].[Equipment Manufacturer],
[Equipment - Listing].[Secondary Tech],
[Equipment - Listing].[Lead Technician],
[Equipment - Listing].[Comments],
[Equipment - Listing].[January],
[Equipment - Listing].[February],
[Equipment - Listing].[March],
[Equipment - Listing].[April]
[Equipment - Listing].[May],
[Equipment - Listing].[June],
[Equipment - Listing].[July],
[Equipment - Listing].[August],
[Equipment - Listing].[September],
[Equipment - Listing].[October],
[Equipment - Listing].[November],
[Equipment - Listing].[December],
[Equipment Maintenance].[Equipment Serial],
[Equipment Maintenance].[Filter Selection],
[Equipment Maintenance].[Filter Quantity],
[Equipment Maintenance].[Belt],
[Equipment Maintenance].[Belt Quantity],
[Equipment Maintenance].[ECT Last Performed],
[Equipment Maintenance].[Maintenance Comments],
[Equipment Maintenance].[Shutdown Parts],
[Equipment Maintenance].[Oil Type],
[Equipment Maintenance].[Oil Quantity],
[Equipment Maintenance].[Last Oil Change],

FROM [Service Agreements] INNER JOIN
([Equipment - Listing] LEFT JOIN [Equipment Maintenance]
ON [Equipment - Listing].[Equipment Serial]=
[Equipment Maintenance].[Equipment Serial]
)
ON [Service Agreements].[Customer]=[Equipment - Listing].[Customer];

There are a number of design flaws here.

The most glaring is this January, February, March, etc business -- this
really needs to dragged into 2nd normal form.

I am also not convinced by the Maintenance table: there is nothing that I
can see to distinguish one maintenance record from another for the same
piece of equipment. How are they sorted? How do you know which record is
the uptodate or most relevant one? And if it doesn't have a primary key,
how would you expect the database to know which row you want to look at
anyway?

The other thing is that although the inside join is a LEFT OUTER, which
will theoretically display all the [Equipment Listing] records, you have
placed it in part of an INNER join, so that [Equipment Listing] records
will disappear if they don't have valid Customer values. You have not
included [Equipment Listing].[Equipment Serial] in the fields list either,
so equipment items that don't have any maintenance records are simply going
to appear as NULL. Fixing this is probably the answer to the original
problem.

My view, then, is that there is still a lot of db design work to be done.
The Maintenance table needs to be normalised and properly keyed; and the
Listing table requires a major overhaul. Then the relationships need to be
fixed between the three tables as, although you don't say so, I get the
impression that there is no ref integrity set up. It's a general rule that
difficult SQL, form, or report problems are usually the result of design
errors -- when the design is right then everything nearly always just drops
into place by itself!

Hope that helps


Tim F

 

[Immanuel Sibero]

Hi Peter

I originally replied to your post, I had been out of pocket the last few
days and noticed that your issue had taken on another life
I categorically agree with Tim Ferguson's comments regarding database design
(ie. 2NF, Primary Key issue, INNER JOIN issue related to Equipment Listing).
One of the reasons I asked for the SQL text for the query is that it may
expose design issues such as what we're apparently seeing.

I would take Tim's advice of pulling back and fixing the design. The way it
is now, it would be a permanent problem. We may force our way and find a way
to patch, get around, even solve the problem now, but other problems will
come up later (ie. temporary solution to a permanent problem).

My suggestion would even go so far as starting another thread in this forum
(ie. tablesdesign). Give a short, concise narative of what your trying to
do, and show what tables, fields you already created and how they are
related to one another. Should there be any design flaw, you wouldn't
believe how quickly the experts in this forum will catch them.


HTH,
Immanuel Sibero

John,

Perhaps I should clarify. I do have a subform to link the
tables. It's how I enter data in the first place, so I
think that's what you're talking about. Also, the serial
number is the primary key of the Equipment Listing table,
not the Maintenance table. I hope i didn't mistype....
Right now, my Maintenance table has no primary key (I'll
fix that later).

I'm sorry, but I don't understand what procedure you're
talking about in your first paragraph (talking about left
outer join and option 2 or 3). I am familiar with SQL, so
I understand the terminology, but not the "options".
Based on what another posting recommended (in this same
thread) I did change one of my statements from a JOIN to a
LEFT JOIN. Here it is if you would like to take a look at
it (it's very near the end). As a side note, there is
another table called Service Agreements that's farther
upstream that doesn't really apply to this problem. Just
to clarify the SQL.

I sincerely appreciate your help in this. Here's the SQL:

SELECT [Service Agreements].[Customer], [Service
Agreements].[Job Number], [Service Agreements].[Coverage],
[Service Agreements].[Contract Comments], [Service
Agreements].[Account Manager], [Equipment - Listing].
[Store Number], [Equipment - Listing].[Equipment Model],
[Equipment - Listing].[Equipment Manufacturer], [Equipment
Maintenance].[Equipment Serial], [Equipment Maintenance].
[Filter Selection], [Equipment Maintenance].[Filter
Quantity], [Equipment Maintenance].[Belt], [Equipment
Maintenance].[Belt Quantity], [Equipment Maintenance].[ECT
Last Performed], [Equipment Maintenance].[Maintenance
Comments], [Equipment Maintenance].[Shutdown Parts],
[Equipment Maintenance].[Oil Type], [Equipment
Maintenance].[Oil Quantity], [Equipment Maintenance].[Last
Oil Change], [Equipment - Listing].[Secondary Tech],
[Equipment - Listing].[Lead Technician], [Equipment -
Listing].[Comments], [Equipment - Listing].[January],
[Equipment - Listing].[February], [Equipment - Listing].
[March], [Equipment - Listing].[December], [Equipment -
Listing].[May], [Equipment - Listing].[June], [Equipment -
Listing].[July], [Equipment - Listing].[August],
[Equipment - Listing].[September], [Equipment - Listing].
[October], [Equipment - Listing].[November], [Equipment -
Listing].[April]
FROM [Service Agreements] INNER JOIN ([Equipment -
Listing] LEFT JOIN [Equipment Maintenance] ON [Equipment -
Listing].[Equipment Serial]=[Equipment Maintenance].
[Equipment Serial]) ON [Service Agreements].[Customer]=
[Equipment - Listing].[Customer];


Peter

-----Original Message-----


You can create a Query linking the Equipment table to the Maintenance
table *by a Left Outer Join*. In the query grid, select the join line
and choose Option 2 (or 3) - "Show all records in Equipment and
matching records in Maintenance". Base your report for the wallchart
on this query.

The Serial Number should probably *NOT* be the primary key of the
maintenance table - a Primary Key is, by definition, unique in the
table; this would mean that one item of equipment will be allowed one
and only one maintenance event, ever! Is this what you intend? I'd see
the Serial Number as a foreign key, nonunique, instead.


.