Restructured Table Design but....

  • Thread starter Thread starter Tom \T\
  • Start date Start date
T

Tom \T\

Jeff,

I have restructured the table design so that there is tblVisitedStates with
PersonID, StateID, and an Autonumbered Primary Key. I had to add a Primary
Key to tblVisitedStates instead of using the combined PK of tblPerson and
tblStates because of business rules which would allow a Person to visit the
same states on different dates.

Now here is my problem and I just cannot see past it. I have a Main Form
with a subform which is in Datasheet View. Across the top of the Subform's
Datasheet view you can see State#1, State#2, and State#3 which used to be
linked to tblPerson.State1, tblPerson.State2, and tblPerson.State3 and all 3
fields are ComboBoxes to facilitate correct data.

Originally each Field(State#1, State#2, and State#3):

ControlSource = State1
RowSourceType = "Table/Query"
RowSource = "SELECT tblState.StateID, tblState.Description FROM tblState;"
ColumnWidth = 0";1"
BoundColumn = 1

ControlSource = State2
RowSourceType = "Table/Query"
RowSource = "SELECT tblState.StateID, tblState.Description FROM tblState;"
ColumnWidth = 0";1"
BoundColumn = 1

ControlSource = State3
RowSourceType = "Table/Query"
RowSource = "SELECT tblState.StateID, tblState.Description FROM tblState;"
ColumnWidth = 0";1"
BoundColumn = 1

Now there may be 1 State, 2 States, or 3 States entered for that Time Frame.
How do I enumerate those fields with the current Data in the DataSheet View
in the NEW Configuration so that as I read John Smith's Record I see State#1
= Arizona, State#2 = Washington, State#3 = Delaware or Amber Smith's Record
State#1 = New Hampshire.

Smith, John's PersonID = 5
Wells, Amber's PersonID = 6

tblVisitedStates.PK, tblVisitedStates.PersonID, tblVisitedStates.StateID
1,5,AZ
2,5,WA
3,5,DE
4,6,NH

I have used the BAD table Design for so long I'm suffering a severe mental
block on how to get my desired result with the NEW table Design. Any
suggestions would be greatly appreciated.

- Tom
 
Your table design is OK. However, the form design follows
the old, non normalized structure.
Try to reconsider the subform layout. Do you really need
a cross tab layout? Is 3 columns enough to cover all
situations? What happens if somebody visits more than 3
states? Instead of a subform, try to create a cross tab
query and open it when needed. Add a button on your form,
which will execute "DoCmd.OpenQuery YourCrossTabQuery".
In such case, you are not limited to 3 columns in the
header. Of course, you can apply some criteria when
openning query like "PersonID=XXX".

:-)

-----Original Message-----
Jeff,

I have restructured the table design so that there is tblVisitedStates with
PersonID, StateID, and an Autonumbered Primary Key. I had to add a Primary
Key to tblVisitedStates instead of using the combined PK of tblPerson and
tblStates because of business rules which would allow a Person to visit the
same states on different dates.

Now here is my problem and I just cannot see past it. I have a Main Form
with a subform which is in Datasheet View. Across the top of the Subform's
Datasheet view you can see State#1, State#2, and State#3 which used to be
linked to tblPerson.State1, tblPerson.State2, and tblPerson.State3 and all 3
fields are ComboBoxes to facilitate correct data.

Originally each Field(State#1, State#2, and State#3):

ControlSource = State1
RowSourceType = "Table/Query"
RowSource = "SELECT tblState.StateID,
Click to expand...
tblState.Description FROM tblState;"
ColumnWidth = 0";1"
BoundColumn = 1

ControlSource = State2
RowSourceType = "Table/Query"
RowSource = "SELECT tblState.StateID,
Click to expand...
tblState.Description FROM tblState;"
ColumnWidth = 0";1"
BoundColumn = 1

ControlSource = State3
RowSourceType = "Table/Query"
RowSource = "SELECT tblState.StateID,
Click to expand...
tblState.Description FROM tblState;"
 
Dejan,

Firstly, I want to thank you for replying.

Secondly, I removed the primary key from tblVisitedStates because both FKs
can and will create a Composite Primary Key.

Thirdly, for each record there can be no more than 3 visited States. The
range is between 1 and 3, no more no less. The subform has already been
designed and the users like the layout. In addition, there is more
information other than State#1, State#2, or State#3 on the subform. The
subform is used as a reference as well as a Data Entry. The columns in the
subform DataSheet View look like this:

SubForm1:
Date Time Authorization State#1 State#2 State#3
Classification
Rec1
Rec2
Rec3
Rec...
AddNewRec

I need the DataSheet View to display State#1-3, update State#1-3, and add a
new record with State#1-3 just as before but with the corrected/normalized
table design. Any ideas?

- Tom
 
Hi Tom!

It is pleasure to help people :-)

It is going to be hard to acoplish your three goals
(display/update/insert) from a non normalized form into a
normalized table. (By the way, tou did well to remove the
PK from tblVisitedScores).

To display the data, it is fairly easy. Since it is
allways 3 or less states, simple cross tab query will do.
Probbaly that is the part your users like the best -
spredsheet is vary convinient layout for them. So leave
it tat way. Create a crosstab query, with all required
fields and use it as record source for the form.

However, cross tab is non updateable query, so no updates
or adding new records is possible. If your users do more
searches than updates or inserts, can you consider a pop-
up form for inserts/updates. Many years ago, in
spreadsheet programs like Excel and Lotus, there was no
in-cell editing. If you wanted to update a cell, you had
to do it in separate location, top left corner of the
screen (you can do it this way in Excell even nowdays).
You can do the same thing - offer them a pop-up form
where they can add new records to your normalized table
or edit existing records. For invoking "New Record" pop-
up you may use a button, and for editing you may use
double-click event of the respective control. Pop-up
forms should be unbound. On the pop-up, you perform all
possible validation and then use recordsets to insert or
edit records. Once the work in the pop-up form is
finished, close it and refresh the record source for the
main form, so users can see the changes. I know it sounds
complicated, but can not think of anything better. I
have been in similar situation many times - the user
always have thair way :-)

You can use my e-mail dejan*mladenovic@eqao*com (replace
* with .) if you have any further questions. I am having
troubles navigating through this forum.

Have a Happy New Year

:-)
-----Original Message-----
Dejan,

Firstly, I want to thank you for replying.

Secondly, I removed the primary key from
Click to expand...
tblVisitedStates because both FKs
 
Back
Top