One to many data entry

  • Thread starter Thread starter Anthony Bollinger
  • Start date Start date
A

Anthony Bollinger

I have a query joined in a one-to-many relationship, and I am using the
query on a form for data entry. Rather than having the "one" record on top
of the form and the "many" in a subform below, I have repeating rows (like a
grid) of the many table. What I want is that when the "one" table does not
have a record with keys that match the many table, I need a record to also
be automatically inserted into that table, just like it is into the "many"
table while I do data entry.

Sorry if this is too confusing. I can give an example if needed.

Thanks for any input,
Tony
 
Thank you for the feedback. I don't believe you understand what I need to
do. I use subforms routinely. I am also skilled with data normalization
and relation database techniques. What I am asking for is functionality
that is commonplace in other database environments, but I cannot get it to
work in Access. Let me give an example, so it will be apparent why a
subform is inappropriate.

This database tracks orders. At the top of the form, as user selects order
number and the customer. Please be aware that one order number, which will
be in bulk, encompasses many customers. Therefore, a single item ordered
could appear on the same order for many customers. The best way to
eliminate duplicate entry of the price specific to each order is to have (1)
a table that tracks all of the items for a customer/order, with key
order/buyer/item, and (2) a table that tracks item price for each order,
with key order/item. So, I use these tables and link buyer/item in each
table using a query. However, when I enter a new item for a buyer, it does
not also enter an entry for the price, as I expect it to. Most other
database environments will do this automatically. Am I missing something?

Thanks,
Tony
 
I'm sorry, but I'm having trouble following the example below, so bear with
me if I've got this wrong. But it sounds like you have a many-to-many
relationship rather than a one-to-many. In this case Order is the linking
table between Customers and Products. You can implement a M:M relationship
in a Form/Subform by basing the main form on Order and basing the subform on
a Join of Order and Products.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ImplementingM2MRelationship.mdb" which illustrates how to do
this.

Again, as I said if this is off the mark, please forgive me, but otherwise,
I don't see what you're driving at.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




I can't imagine what functionality you have in other database environments
that you don't have in Access, unless you are referring to triggers.
 
Most other
database environments will do this automatically.

Actually, I don't know any that do that. In fact, it was not that long ago
that even sql server allowed multi-able queries that were updatable.
virtually all sql engines allow you to join in many tables together...but
the number that allowed those tables to be updated is much less. And even
less so is engines that add the child record for you and setup the foreign
key

It turns out that you can get ms-access to do this (and, I think it was one
the first products to do this). However, you MUST force a disk write on the
parent record first to generate the pk. Then, the child record can be
edited..and the FK (foreign key) will be correctly set/added by ms-access if
no child record yet exits. You also MUST use a left join (but, as I explain
below...90%+ of my sql quires are left join anyway...

So, likely you will have to use a continues form in place of a
datasheet..since you want code to "fire" to write the current record to
write to disk....

And...here is a blurb on left joins....

A left join means that a query will return the "parent" records when the
child table HAS NO correspond record.


To make a left join, you drop in the tables (in the query builder, or the
relationship designer), and draw the join line to the appropriate filed
between each table. You then double click on the join line. You then click
on the join type button

You get three options:

Only include rows where the joined fields from both tables are equal
(this standard default inner join)

Include ALL records from "Customers" and only those records from
"Invoices" where the joined fields are equal

(this is our left join. So, our main table Customers will be returned in
this query, REGARDLESS if the child records (invoices in this example)
exist, or not!. This is left join

Include ALL records from "Invoices" and only those records from
"Customers" where the joined fields are equal
This sis obviously a right join....

For forms, and sub-forms, and related tables, left joins are quite
important.

If you look at the following screen shot, you can see that most relations
ships are this left join, and RI is enforced.

http://www.members.shaw.ca/AlbertKallal/Articles/PickSql/Appendex2.html

tblBgroup (booking group) for example may, or may not have payments made
(tblPayments). Thus, you can add a booking group, and NOT have to add child
records. However, full RI is enforced, and you can see the side ways 8
"omega" sign AND THE ARROW HEAD. The simple lookup fields are simply just a
arrow drawn, and no "1", or omega sign exists (tblPayments to tblHowpaid for
example is a simple lookup). It is GREAT that I can look at the ER diagram,
and instantly know if child records are required, or they are not!!

The tables that MUST have a child records can also clearly be seen. If you
go from the tblBgroup to the its parent table, you will see table
tblBooking. You can easily see that there is a 1 to many here also, but NO
ARROW head exists. Thus, when I create a booking, my designs will ALWAYS
ASSUME that a child records in tblBgroup (booking group) will exist (ie: I
must code, and assume that when I add a tblBooking records, my code also
assumes that a tblBGroup will also have to be added). In plain English this
means that when I make a booking (reservation), my code assumes that
you MUST have people in that booking. However, I most certainly allow
people to be booked, but not yet have made any payments. So, your
relationship(s) if done right should reflect the rules you as a developer
want to maintain. I should point out that a left join, or a standard
(inner join) both allow child records to NOT exist, but you still
should correctly set this relationship, since when it comes to making
reports, and writing code...I will know what my assumptions
were at the time (ie: do I HAVE to add those child records
for the software to function correctly. So, if I write code to
make a booking, all of my code thus assumes that people
are also to be added to the booking. Break that assuming
of mine, and likely my code will break).

So, the ER diagram can convey a lot about your designs. Down the road, I can
now look at that diagram, and when writing code, I will know if the design
can, and does assume if child records are required. If you look at that
table, it is VERY RARE that I require the child record. That application has
about 60 tables, and I think only 1 or 2 in the whole thing is NOT a left
join. Hence, you most certainly should set the relation in the window for
future reference, and also it will help you when you create a query, or a
report.
 
Roger and Albert,

Thank you for your comments. They seem to make sense, so I am going to work
through them today. I will get back to you if I need further input.

Thanks,
Tony
 
Back
Top