Creating Lookup Fields

[ASoldiersBride]

I work for the YMCA and we run a quilt project for kids with deployed
parents. I'm tryin to make the system much simpler and put all parts in one
place.

I have a table called quilts under parents name I have a lookup field where
I can choose which family the quilt is for.

I'd like the next field to be a multivalued children's name field but I only
want it to lookup the children's name based on the data that's in that
particular parent's name record in the families table.

I haven't played in access in YEARS!!! so I'm already kinda lost and have
been just fidgeting my way through this. Any help y'all can give with exact
instructions would be phenominal ~~ thank you in advance !!!!!!!

 

[Jeanette Cunningham]

Hi,
quilts! - I love looking at patchwork quilts.
Are the kids making the quilts for themselves?
Can you have more than one quilt per family ( more than one child? or step
families?)

Access has changed over the years. Multi valued fields are new - meant for
people who are connecting with Share point server.
For us ordinary developers, mulit valued fields cause pain, grief and
aggravation.
Lookup fields in tables create too many problems as well and are best
avoided.

Replace a lookup field with a separate table with the lookup data in it.

Here are some suggested tables:

A table for family names
FamilyNameID >> primary key autonumber
FamilyName


a table for individual names
IndivNameID >> primary key autonumber
FamilyNameID >> foreign key from table for family names
LastName
FirstName
IndivTypeID

a table for individual types
IndivTypeID >> primary key autonumber
IndivTypes child, mother, father, stepmother, guardian, ...

a table for quilts - if more than 1 quilt per family name is possible
QuiltID >> primary key autonumber
QuiltDescr
FamilyNameID >> foreign key

a table for quilt makers - if more than one child would work on a quilt
QuiltMakerID >> primary key autonumber
IndivID >> foreign key

Jeanette Cunningham

 

[ASoldiersBride]

Thanks for the help!! Actually we have quilter volunteers who make the
quilts for the families. Some families have as many as 5 kids who have a mom
or dad deployed. I appreciate the suggestions but they're not going to work.
I need to have the tables in place that I do and I'm sure there's a way to
select the family name then select the child or children that particular
quilter is working on for my quilt table, there's got to be a way. There's
another post down the way a bit where someone kinda wants to do what I do and
he was told to "=Forms!SomeFormName!SomeComboName" and I have no idea what
that means LOL.

 

[John W. Vinson]

Thanks for the help!! Actually we have quilter volunteers who make the
quilts for the families. Some families have as many as 5 kids who have a mom
or dad deployed. I appreciate the suggestions but they're not going to work.
I need to have the tables in place that I do and I'm sure there's a way to
select the family name then select the child or children that particular
quilter is working on for my quilt table, there's got to be a way. There's
another post down the way a bit where someone kinda wants to do what I do and
he was told to "=Forms!SomeFormName!SomeComboName" and I have no idea what
that means LOL.

It means that they are using a Form rather than a table datasheet. That's what
the Forms! reference means.

This can be done using a Form.
This CANNOT be done using a table datasheet.

Sorry, but that's just the way Access was developed!!!

What you *can* do is create a continuous Form and make it look very much like
a table datasheet, if you really like that appearance; on the form you *CAN *
use dependent combo boxes.

 

[ASoldiersBride]

Thank you for the reply John. Doing what I need to do in a form is EXACTLY
what I'm after. In one box on the form I want the user to be able to select
the family from a drop down menu (I know how to do that part) then in the
next box I want them to be able to select the child or children from that
family that the particular quilter is quilting for.

In order to do this, when I set up the table I believe I need to make fields
for child 1, child 2, child 3, etc am I correct?

Please give me exact directions on how to accomplish this mission ~~ Thank
you SOOOOO much for your help in advance

:::: off to scour the help files for dependent combo boxes::::

 

[John W. Vinson]

Thank you for the reply John. Doing what I need to do in a form is EXACTLY
what I'm after. In one box on the form I want the user to be able to select
the family from a drop down menu (I know how to do that part) then in the
next box I want them to be able to select the child or children from that
family that the particular quilter is quilting for.

In order to do this, when I set up the table I believe I need to make fields
for child 1, child 2, child 3, etc am I correct?

No. As Jeannette said, DON'T use the multivalue field type. Rather than a
multivalue field, or (worse!) multiple fields, for children use multiple
RECORDS for children. You should have a table with fields for the FamilyID,
the child's name, and any other information about that child. Each child would
have her own record in this table. Rather than having a combo box on the form
to store the child or children, you will need a Subform to store multiple
records.

You haven't posted your tablenames, form or control names so I'm worried that
if I post detailed instructions they'd just be confusing since I'd have to
make my own guesses at names. What is your actual table structure? What do
you have so far in the way of a form?

 

[ASoldiersBride]

As of right now I have no family table as I've been waiting to figure out the
best way to do it. The information I'll have in the family table is the
first and last name of the parent that's here in the states with all the
normal contact info, servicemember's name, servicemember's unit, then the
child(ren)'s name(s) and age(s)

Once I know the proper way to do this table in order for the quilt table to
work then I'll build the quilt table which will house parent's name (lookup
field), child's name (one quilt record per child in the family ~ this is
where I'm askin for y'alls help), quilter (lookup field) then fields for
different dates having to do with the quilt

Hope that's what you were askin for ~~ feel free to email me directly if you
need to ASoldiersBride@yahoo

Thank you sooooooooo much John !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 

[John W. Vinson]

As of right now I have no family table as I've been waiting to figure out the
best way to do it. The information I'll have in the family table is the
first and last name of the parent that's here in the states with all the
normal contact info, servicemember's name, servicemember's unit, then the
child(ren)'s name(s) and age(s)

You need TWO TABLES. You're still thinking that you can put multiple children
into one field, or into one record in the family table. Even with a Multivalue
field you *CAN'T* - a multivalue field is actually a concealed second table!!!

What you need is a table of Families, with FamilyID as its primary key, name
and contact information, etc. The FamilyID might be an Autonumber or, if
available and appropriate, the service member's military ID (since that can be
counted on to be unique and stable).

You would then have a SEPARATE Children table, with ChildID (probably
autonumber) as its primary key, and a FamilyID field (not unique and not the
primary key) as a link to the Families table.

Once I know the proper way to do this table in order for the quilt table to
work then I'll build the quilt table which will house parent's name (lookup
field), child's name (one quilt record per child in the family ~ this is
where I'm askin for y'alls help), quilter (lookup field) then fields for
different dates having to do with the quilt

Again: I'll yell it, since I have to do so to get through the misleading
Microsoft propaganda.

NEVER USE LOOKUP FIELDS.

Never. Ever.

They are confusing, misleading, obnoxious, all but useless... and the Hot New
Thing from Microsoft, so they keep pushing them.

It is *never under any circumstances* necessary to use a lookup field. It is
possible to use a lookup field, and (he grudgingly admits) can even save a few
moments' work in setting up forms - but their small advantages are, in my
opinion, vastly outweighed by the confusion they cause, which is exactly what
you are experiencing!!!!

I would recommend that you NOT store the child's name or other information in
the Quilt table; a quilt is not a child, and a child is not a quilt! Each type
of entity should have its own table. If a Quilt belongs to a Child, you would
have the ChildID as a foreign key (bound to a combo box on a form, but NOT a
Lookup Field in the table!!).

Similarly, if each Quilt has multiple associated Dates, use a Dates table with
a QuiltID (a link to the quilts table, indicating which quilt you're talking
about); an EventType specifying what kind of date this record contains; an
EventDate (don't use the reserved word Date as the fieldname). Each kind of
event for which you're tracking dates would get an additional record in this
Dates table, which would be tied in to the Quilts table by the QuiltID.

 

[ASoldiersBride]

Thank you again John Guess I'm more Access stupid than I thought I was
cause you lost me at "FamilyID field (not unique and not the > primary key)
as a link to the Families table." LOL sorry to say I have no idea what any of
that means or how to do it. LOL and to think I'm actually a computer geek
who everyone comes to in order to fix their problems and show them how to do
stuff. Think I'll just do up a family table, put the kids in a text box then
hand do each quilt with the kids name on it. This is all startin to make my
eyes cross. Thank you for tryin to help me. I really do appreciate it.

 

[ASoldiersBride]

Well I tried it, thought I had it but nope LOL

I went ahead and made the families table and form with everything except the
child info
Made a child table with id as an auto then the next field was family id
which was a relationship with the family table, I also made a relationship
with child last name and family last name.

Put the child table as a subform on the family form. Worked GREAT for the
1st family. Put in the family, it put family #1 in the child table and auto
put in the last name, I filled out the family stuff, filled out the stuff for
each of the children and shazam I thought I was good to go.

Went on to family #2 and it errored out on me.

 

[ASoldiersBride]

John !! I DID IT !! I DID IT !!!! I got the child table to work WOOHOO
!!!!!!!!!!!! It works perfect. Now I just need to apply what I learned here
to the quilt table and hopefully I'll be alright

Thank you sooooooo much !!!!

 

[John W. Vinson]

John !! I DID IT !! I DID IT !!!! I got the child table to work WOOHOO
!!!!!!!!!!!! It works perfect.

Now I just need to apply what I learned here
to the quilt table and hopefully I'll be alright

Once you get the principle you'll see how universal it actually is!