Need help setting up a table!

[mabyn]

Can someone help me think this out? I keep messing up my program. I am
ultimately trying to organize Registrations with Entry Names (plus the names
of the people who are registering) according to their Lineup Area. (This is a
parade.)

I need help in deciding how to set up the table, I think. (From there I have
to query these three tables.) However, I first need help figuring out what is
the best way to do the Lineup Area table.

Table 1 Attendees (has names of people)
Table 2 Lineup Area (need to recreate)
I need these options to show up (in dropbox?) to choose between:
Head of Parade
Band Staging Area
Commercial
Group 1
Table 3 Registrations (has registration # and an Entry Name)

 

[tina]

so you have Registrations (or perhaps more properly, Registrants), that
being entries in the parade, such as floats, bands, etc, correct? and you
have LineupAreas, that being areas where the registrants will be grouped
according to where they're going to march in the parade, correct? (reminds
me of grade school plays: "Okay, all the stars go stand by the bleachers.
All the stripes go stand by the door." <g>)

and you want to...assign each registrant to a lineup area? if so, then i'd
say your LineupAreas table should be just a list of the available areas, as

AreaID AreaName
1 Head of Parade
2 Band Staging Area
3 Commercial
4 Group 1


add a field to the Registrations table: AreaID. that field is foreign key,
which links back to the primary key field AreaID of the LineupAreas table.
*don't* make it a Lookup field, just a regular field, with the same data
type as the AreaID field in LineupAreas table (or if the primary key field
is an Autonumber, make the foreign key field a Number, field size Long
Integer).

in your Registration data entry *form*, use a combobox control with
RowSource based on table LineupAreas, to show the list of lineup areas so
you can assign one to each registration record.

hth

 

[mabyn]

Ha ha. Yes, just like in Grade School.
Question : If I don't use Lookup, how/where do I link them? I had both
tables setup as you described with MAJOR exception of knowing how to link
them correctly.

Mabyn

 

[mabyn]

I think I just figured it out. whew...
--
Mabyn

Ha ha. Yes, just like in Grade School.
Question : If I don't use Lookup, how/where do I link them? I had both
tables setup as you described with MAJOR exception of knowing how to link
them correctly.

Mabyn

 

[mabyn]

I spoke too early. I'm working on two separate jobs and am stuck on both.
Tables are linked via the AreaID however when I enter information in the
form it does not go to the Registration table. I can't see that it goes
anywhere. Help me please!
Thanks.

 

[mabyn]

There are many Attendee Names...from past parades we have held. Around 100
have registered this year and have a registration number and Entry Name. The
Entry name is what they call their Krewe or Float. The Attendee Name is the
person or company who we mail information to. There could be duplication of
Registration numbers because if there is a late fee it registers as a new
registration. However, there is only one Entry Name per registration. So, my
list needs to be setup for the parade marshalls like:

Area Name Reg # Entry Name Attendee First & Last Name Company

 

[mabyn]

After searching for this and reading all of the Access links I could find, I
still need to ask someone's assistance. The base of this database if
Microsoft's Event.



The database for this parade is setup as follows now:
1.Attendees
Attendee ID
This is where their name and address is. Not all companies and persons
here will actually register every year.
2. Registration
RegistrationID - Autonumbe used throughout all of their paperwork. Since
the numbers correspond to payments, some of the RegistrationID's are not new
entries but rather other fees (like late fee or special position)
Entry Name - If this record is filled, it indicates that this is actually
a registered float this year.
Float Owner, and several other categories we use to check floats in.
3. Lineup
LineupID autonumber
RegistrationID
Area ID
4.Lineup Area
Area ID autonumber
Areas to lineup in
5. There are several other tables having to do with payment, employees and
type of event.


I created a form from the Lineup Table. I have to arrow to the right for it
to populate the blank however, after saving it...there is nothing in any
form. Obviously there is a control I am skipping. Help?



Registrations

 

[Gina Whipp]

mabyn,

Data will only show in the form if there is data in the table(s). Have you
entered any data?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[mabyn]

Yes. That is what I meant to say. I am entering data in the form and then,
upon checking my tables, there is nothing in them. I am specifically talking
about the Lineup tablesand form (Lineup which is a form off of Attendees) I
created. My form is made up of Lineup Area.
I hope this makes sense.

 

[Gina Whipp]

What is the RecordSource of your form? And when you go into Design mode are
your fields bound or unbound?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[mabyn]

1. Form recordsource is Lineup Areas.
2. I don't know how to find the Bound property. I am in the form (design
view) and have selected Properties and see nothing with any words like that.

 

[Gina Whipp]

Look at the fields... do you see words or the word Unbound? Also, the form
has the same name as the table?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[mabyn]

Yes... the table and the form have the same name. I'll make a new form, place
it on my attendees form and check it quickly.

Bound or unbound. When I look at tables I see nothing in the property that
tells me that in design view. When I look in forms I don't see it either. I
must be looking at the wrong thing?

 

[Gina Whipp]

Okay... let's start from the beginning... Go to Design View of the Form.
Pull up the Properties window and tell me what it says as the RecordSource.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[mabyn]

I am so sorry to be dragging you through this.
Done.
Record Source - Lineup Areas

 

[Gina Whipp]

No problem... taking the night off from work... Okay next step...

Go to the form in design mode and you should have fields on your form. In
each field you should see anmes the names should match the names in your
table. Tell me the names you see in the fields that are on your form and
whther they are text boxes or combo boxes.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[mabyn]

Okay - The form Enter Lineup Areas
In Design View it looks like it will (almost) when it is operable.
(Label) AreaID_Label (Text Box) AreaID
(Label) Areas_Label (Text Box) Areas

serve.

 

[Gina Whipp]

Okay so there are two text boxes... Is AreaID related to any table? if not
type xxx in the field close the form and see if it's in the table.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[mabyn]

Gina - Yes, this is what two of my tables look like and their names:
Lineup Areas Lineup
Registration
AreaID (auto#) AreaID
RegistrationID
Areas LineupID (auto#)
Entry Name
RegistrationID
other Fields...

 

[Gina Whipp]

Mabyn,

I see what you are trying to do but I can also see you have not fully
grasped relational database design.

The database for this parade is setup as follows now:
1. tblAttendees
aAttendeeID (PK, Autonumber)
aFirstName
aLastName
etc...

2. tblRegistration
rRegistrationID (PK, Autonumber)
rEntryName > Is this the Attendee?
rFloatOwner
etc...

3. tblLineUp
luLineUpID (PK, Autonumber)
luRegistrationID (FK)
luLineUpAreaID (FK)

4. tblLineUpArea
luaLineUpAreaID (PK, Autonumber)
luaAreas
etc...

Your form based on this set-up should be based on tblLineUp. Then you will
see who's lined up and in what area. You can use combo boxes for the two
FK's in the table. However you cannot add data to tblLineUp until there is
data to add from in tablLineUpArea and tblRegistration, that is if you are
creating relationships.

You can also review the below for basic database design..

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP): My favorite!
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm