Thank you very much. There are two issues here: (1) helping me sort out my
forms, and (2) the design.
I am happy to change my table design to three fields. I think it is much
easier for the 'designer' to follow a three table approach. And as you are
kindly seeing me through this, I will of course follow your advise.
As a side note, and somewhat out of reverence to those who advised me on
this in the Microsoft.public.tabledesign, I feel I should attempt to defend
(or try to regurgitate what I have been told) about the two table approach:
A dog (with an ID of DogID), because it is born to a litter 'belongs to' a
litter, and therefore can be on the many side of a litter table, and so have
a LitterFK. A litter (with a LitterID), also belongs to a dog, and so can
have a DogFK. This gives a curious 'circular' reference which seemed wrong
to me, hence my posting on the Table.Design webpage. If I understood
correctly, Tim Ferguson assured me that I should not be bothered by this
circular thing. You write 'a litter can belong to only one dog'. I think
having Litter.DogFK respects this. You write: 'and since there can be more
than one pup born in a litter, you'll need a third table to store that
information.' I agree that you and me as human beings would conclude that a
third table is the most natural approach. However, you can still have many
dogs in table dog belonging to that same litter. Another point, in the
three table design, you have in the third table (tblLitters) a field called
lngDogID. You describe this 'lngDogID [PK w/ lngLitterID]'. I understand
what PK stands for, but I cannot follow the notation 'w/ lngLitterID.' I
assume this field has a relationship with DogID in the dog table table.
This relationship is a 1-2-1 relationship, and this is what caused me to
suspect that maybe I have one table too many. Anyway, right now my priority
is to get the form working, and if it is simpler to get a form working with
three tables, then that is fine with me, even if in theory i have one table
too many.
http://www.google.com/groups?hl=en&...cache%245igtqh%24eno%241%40lnews.actcom.co.il
Now to the substance: You write 'Are cmbDog and cmbDate fields in the >table
or query< driving your subform?
Linkfields are table/query fields, not controls ... clear this up first.'
I don't think this is relevant any more, unless you are willing to bear with
me trying to get my form working in the two-table design. But the answer is
that the cmbDog and cmbDate are the combo box values on the form where the
user selects his (mother) dog, and the (litter) date. I am not sure exactly
how the Linkfields work, but the ides is that in the query extracting all
the pups, the subform datasheet will only display those records where the
mother DogID is equal to 'cmbDog' and the litter date is the value of the
date in the cmbDate. (I should write a further query restricting the
available litter dates in the combo box, so that only litters belonging to
the mother bitch will be displayed.)
SELECT DogFK, DateApplied, DogID
FROM tblLitter INNER JOIN tblDog ON tblLitter.LitterID=tblDog.LitterFK;
But now to try to implement your advice. While I have a fairly good
understanding of table design, and a limited understanding of queries, I am
really at a loss when it comes to forms.
In my form, I would like the user to select the (mother) dog, then the
litter date, and then the user should be able to view the existing puppies
to the litter, and amend them or add new puppies. My preferred choice is to
have the user select the mother bitch and the date from a combo box. I
think you are advising me to reach the same result, but through a different
approach. I will try to follow it.
A question, in your tblLitters, do I have any relationships with the parent
forms? You state in tblLitter that lngBitch is a foreign key to table dog.
But in your litters table, you do not specify that lngLitterID and lngDogID
are foreign keys. Do you want a link there?
You instruct: 'build your form so that tblLitter is the main form'. I am
not sure I understand how to do this. I think you are telling me that if I
create the form using the forms wizard, then I should select a few fields
from tblLitter. OK, I have selected two fields from the Litter table, the
'lngBitch' field, and the dteLitterDate. The wizard asks me what king of
grid I want (column, grid). I trust it does not matter.
Next step. "tblLitters would be the underlying table to your subform." Here
to I think that the way I state that the underlying form is tblLitters is
clicking on subform from the toolbox, placing the subform on the main form
and then, when the wizard asks me on what to base the table, I select
tblLitters and select at least one field. I have selected the 'lngDogID'.
Now the Access SubForm wizard is asking me 'would you like to define which
fields link your main form to this subform yourself, or chose from the list
below?' It asks that, but the 'button' for choosing from the list is greyed
out, and I have to define my own. So I select that, and drag the single
field available from the pulldown menu, 'lngDogID'. I then get the message
'Parts of the link with missing pair fields will be ignored'. This is not
very reassuring. I assume I have to select some sort of relationship here.
The main form should be the 'one' side, and the subform the many side. So I
think I will add lngLitterID to the list of fields to appear on the subform,
and I will link this to the lngLitterID on the main form.
OK, now let's try it so far. ... Hmmm , not bad.
I have a subform which shows me the puppies belonging to the litter. The
subform also shows me the 'lngLitterID' and I don't really want to see this
in the form.
You instruct: 'and you'd have a combo on the subfrom that is linked to
tblDog'. Now I am getting a little lost. On the main form, I currently
have the mother dog field and the litter date. Presumably i have to remove
the mother dog field, and replace it with the combobox linked to tblDog.
The table dog field right now is on the main form. The combobox is to go on
the subform. Now I am a little unsure how to proceed. Then you write 'Your
query to return all the pups in a litter would be a three-way join of all
tables'. Presumably you want me to write a query for this. I can write the
query, but then how do I link it to the form. To which form do I link it.
I am a bit lost again. Hopefully, you will be able to tell me where I went
astray, for I wrote down every single step I took.
Many thanks
Ilan
Scott McDaniel said:
Are cmbDog and cmbDate fields in the >table or query< driving your subform?
Linkfields are table/query fields, not controls ... clear this up first.
Your error comes about, I believe, because of the way you're storing and
joining the primary and foreign keys. I believe you need to rethink your
table design. You shouldn't be storing the LitterID with the dog; a bitch
can certainly have more than one litter in her breeding lifetime. Since a
bitch can have more than one litter, then there is a 1-M relationship with
Dogs and Litters ... a litter can ONLY belong to one dog, but one dog can
have MANY litters. So you store the ID of the bitch in the Litter table ....
and since there can be more than one pup born in a litter, you'll need a
third table to store that information. Note that this design uses tblDogs to
store ALL info about the dogs, whether they are bitches, pups, etc etc. You
may wish to break that out, although I don't see the reason to do so.
tblDog
==========
lngDogID [PK]
strName
etc etc
tblLitter
===========
lngLitterID [PK]
lngBitch [FK to tblDob]
dteLitterDate
etc etc
tblLitters
=======
lngLitterID [PK w/ lngDogID]
lngDogID [PK w/ lngLitterID]
Now, build your form so that tblLitter is the main form ... tblLitters would
be the underlying table to your subform, and you'd have a combo on the
subfrom that is linked to tblDog (so that you can select the pups belonging
to that litter). Your query to return all the pups in a litter would be a
three-way join of all tables (if you need it for, say, reporting purposes).
in message news:
[email protected]...
I am attempting to keep track of puppies born to bitches.
I am having problems with the form for entering new puppies born on a
litter.
I have the following two tables
tblDog
DogID [pk]
LitterFK
tblLitter
DogFK [PK1]
DateApplied [PK2]
OtherFields
LitterID [autonumber, for relationship with tblDog]
I have one query designed to return the puppies born in a litter:
SELECT DogFK, DateApplied, DogID
FROM tblLitter INNER JOIN tblDog ON tblLitter.LitterID=tblDog.LitterFK;
I then have a form as follows
1. a combobox, for the user to select the mother dog (cmbDog)
2. a combobox for the user to select the litter date (cmbDate)
3. a subform.
The subform has the following:
A. The 'Record Source' has the sql above query pasted into it. This is in
the Form object of the subform.
B. On the main form, the subform has the property 'Link Child Fields' of
"DogFK; DateApplied", and the 'Link Master Field' of 'cmbDog;cmbDate'
When I run the form, the user can select the mother dog, and then the date
of the litter, but then Access gives me the following error (without any
number)
"This expression is typed incorrectly, or is too complex to be
evaluated...."
Clearly I have a type, because nothing I am doing is 'too complex'. However
I tried redoing this a number of times from scratch, and every time I get
the same error. So I am suspecting that I am probably not entering the
SELECT statement in the right place, or that I am doing something
structurely wrong.
Any suggestions?
Many thanks
