Humbled and really stuck ...

[Jamie Risk]

Thanks already to TW, JV and JC who helped me define the following.

I've posted here earlier and received responses that would undoubtedly have
helped competent Access users. What I gleaned from those charitable enough
to respond was that I don't understand how to structure my database. This
is a fundamental problem.

I've got data from two sources, which I can import into two seperate tables.
For the sake of description, I'll call the tables "Professionals" and
"Politicians", each having a similar field describing geographiclal
location. Choosing "Politicians" as one of the tables names is quite
suitable because there can only be one 'politician' per geographical
location (district). For matching 'Districts' between the two tables, I need
to organize a one meeting per 'Politician' where 1 or more 'Professionals'
from their district would be in attendance.

What I'd already tried (and was told was a bad idea - not sure why) was to
have a table of "Districts", a table of "Politicians" and a table of
"Professionals". In each of the "Politicians" and "Professionals" tables, I
(here is the reputed bad idea) created a table lookup to "Districts". Made
sense to me, their was a 'Wizard' to help me do this, and the idea of fixing
the "Districts" field to a static list would make my query more reliable (no
typos etc.).

I was able to get my query to produce matchups between the "Politicians" and
"Professionals" but I couldn't figure out how to reference that query
information to record a meeting time.


Now, it was suggested that I use forms to handle alot of my constraints,
rather than table lookups. Four things I liked about the table lookup
feature was that cryptic ID numbers were hidden from the user, query results
were sure to be exact, duplication of data was avoided and the option to
only allow records from the list could be enforced. I know I can do this
all with forms, but I don't know the best way to organize it.

I know it's asking alot, but:
* How should I structure my tables/forms too
- use pre-existing 'Politician' and 'Professional' tables (these can
be modified if need be)
- producing a query that results in 'Politicians' with 'Professionals'
from the same district
- schedule a time and place for meetings between 'Politicians' and
'Professionals' from the same district
- produce individual and master schedules for all people involved.

 

[Steve Schapel]

Jamie,

I don't know the reasoning behind the advice you have obtained elsewhere
to not use a lookup table. But I would certainly recommend the lookup
table approach, and it is standard procedure in cases such as this.

As for the advice to use forms for data management, you should
absolutely adhere to this. The purpose of tables is data storage, and
there are many reasons why it is inadvisable to access them directly for
data entry/edit/viewing purposes. I would definitely have a Districts
table, and on my Professionals form and my Politicians form, I would
have a combobox bound to the District field, and with its Row Source set
to the Districts table.

It sounds like you want to schedule a meeting in each district. Am I
right? If there will only be one meeting per district, you can simply
add the applicable fields to the Districts table... MeetingDateTime,
Venue, whatever.

Then you can create a query that includes all 3 tables, joined on the
District fields, and this will return the meeting details, with the
politician and professionals for each meeting, and you can then use this
query as the basis of a report to produce your meeting schedule(s).

However, if you can have more than one meeting per district , you would
need another table to record the meeting details, so it would be
slightly more complex but still the same principle.

 

[Tom Wickerath]

Steve,
The advice regarding lookups was given by me in this message:

However, this was specifically stated as lookups defined *at the table level*, and included a
link to the 10 Commandments on the MVPS site, pointing out the 2nd commandment in particular. In
other words, using a lookup at the table level to obscure the actual foreign key value that is
stored in the table. This is in line with your advice to adhere to the use of forms for data
management, since you would not be using table lookups in that case.


Jamie,
Reading through your posts, I wonder if a better design might involve a single table to store
people's names (tblPeople), where their role is included as an attribute (Politician,
Professional (this post), or even Plumber (from a later post:
instead of having separate tables to store
Politicians versus other types. In an earlier post, you stated "The fact is the tables are not
of my creation and come from two separate sources, which happen to have
one field useful field in common." It's okay to import data from separate sources, but that
shouldn't preclude combining this data into a single table if that makes sense. Your source of
data should not be a primary driver of your database design, especially if it is a one-time
import.

Something like this, where pk's are autonumber primary keys and fk's are long integer foreign
keys:

tblDistricts
pkDistrictID
District
+ any other attributes about districts that you wish to store.


tblPeople
pkPeopleID
FirstName
LastName
Role (or fkRoleID that relates to another table: tblRoles)
fkDistrictID
+ any other attributes about people that you wish to store.


tblMeetingAttendance
pkAttendanceID
fkMeetingID
fkPeopleID

Note: Unique index on (fkMeetingID + fkPeopleID)


tblMeetings
pkMeetingID
MeetingTopic
MeetingDate
Location (or fkLocationID that relates to another table: tblLocations)



Tom
_____________________________________


Jamie,

I don't know the reasoning behind the advice you have obtained elsewhere
to not use a lookup table. But I would certainly recommend the lookup
table approach, and it is standard procedure in cases such as this.

As for the advice to use forms for data management, you should
absolutely adhere to this. The purpose of tables is data storage, and
there are many reasons why it is inadvisable to access them directly for
data entry/edit/viewing purposes. I would definitely have a Districts
table, and on my Professionals form and my Politicians form, I would
have a combobox bound to the District field, and with its Row Source set
to the Districts table.

It sounds like you want to schedule a meeting in each district. Am I
right? If there will only be one meeting per district, you can simply
add the applicable fields to the Districts table... MeetingDateTime,
Venue, whatever.

Then you can create a query that includes all 3 tables, joined on the
District fields, and this will return the meeting details, with the
politician and professionals for each meeting, and you can then use this
query as the basis of a report to produce your meeting schedule(s).

However, if you can have more than one meeting per district , you would
need another table to record the meeting details, so it would be
slightly more complex but still the same principle.

 

[Steve Schapel]

Thanks, Tom. Sorry, I didn't consider that Jamie might have been
talking about lookup fields.

Why would you use a DistrictID field? I suppose only if it's possible
for two districts to have the same name?

Assuming the Politicians fields and the Professionals fields is
sufficiently similar, I agree with you idea about combining them into a
single table with a Role field.

 

[Jamie Risk]

Aside from sharing DistrictID and Name fields, the tables are vastly
different.

As far as using DistrictID fields, your right, no reason to do that. I was
doing it every else and I like the idea of 'references' (it's my C
partisan).

 

[Tom Wickerath]

Why would you use a DistrictID field? I suppose only if it's

possible for two districts to have the same name?

1.) Because every table should have a primary key.
2.) I tend to avoid the use of any primary key that has meaning, choosing instead to use a
meaningless surrogate key. I find that an autonumber is ideally suited to this. I refer you to a
past conversation that we've had on this issue:

http://groups-beta.google.com/group..._frm/thread/c58d50d7a3e2b560/86c7e55f4a44e2e6

Cheers,
Tom
_________________________________


Thanks, Tom. Sorry, I didn't consider that Jamie might have been
talking about lookup fields.

Why would you use a DistrictID field? I suppose only if it's possible
for two districts to have the same name?

Assuming the Politicians fields and the Professionals fields is
sufficiently similar, I agree with you idea about combining them into a
single table with a Role field.