Top Issues Database

[Brad]

I'm pretty new to Access and database design. I'm setting up a database to
enter and track the Top 8 Weekly Issues. I've got 1 table set up like:
tblTop8
ID
Issue_Num - (1 - 8)
Week_Of -
Model - Model affected by the issue
Issue - the Issue

I want to set up a form where the user can enter all 8 issues for the week.
So for instance, there will be 8 combo boxes on the form for Model, 8 Text
boxes for Issue, which I don't think is possible. Any advice on where / what
I need to do?

 

[Keith Wilby]

I'm pretty new to Access and database design. I'm setting up a database to
enter and track the Top 8 Weekly Issues. I've got 1 table set up like:
tblTop8
ID
Issue_Num - (1 - 8)
Week_Of -
Model - Model affected by the issue
Issue - the Issue

I want to set up a form where the user can enter all 8 issues for the
week.
So for instance, there will be 8 combo boxes on the form for Model, 8 Text
boxes for Issue, which I don't think is possible. Any advice on where /
what
I need to do?

Set your form to continuous or datasheet view, it will then look similar to
an Excel spreadsheet.

Keith.
www.keithwilby.co.uk

 

[Philip Herlihy]

I'm pretty new to Access and database design. I'm setting up a database to
enter and track the Top 8 Weekly Issues. I've got 1 table set up like:
tblTop8
ID
Issue_Num - (1 - 8)
Week_Of -
Model - Model affected by the issue
Issue - the Issue

I want to set up a form where the user can enter all 8 issues for the week.
So for instance, there will be 8 combo boxes on the form for Model, 8 Text
boxes for Issue, which I don't think is possible. Any advice on where / what
I need to do?

Sounds to me that the natural layout for this would be a form displaying
a week at a time, with a subform displaying Issues.

You can create this by the simple expedient of separating your data into
separate tables for "Week" and "Issue" with a one-to-many relationship
implemented by including the week-ID as a foreign key in the Issues table.

There may be no other motivation to do this, but if you do, and "tell"
Access about the relationship via the Relationships window, when you use
the Form Builder to create a form based on the query you will need to
"join" the tables, Access will offer you a Form/Subform option. Tidy up
the layout of the various boxes on the form(s) and you'll have a working
solution very quickly.

Phil, London

 

[Ken Sheridan]

Firstly create another table tblWeek_Of with one column (field), Week_Of,
which will of course be its primary key. Then create a form in single form
view based on a query on this table which sorts the rows by Week_Of value.
The form will have just the one bound control, a text box bound to the
Week_Of column.

Create a relationship between tblWeek_Of and tblTop8 on the Week_Of columns,
enforcing referential integrity

Create a form in continuous form or datasheet view based on a query on
tblTop8 sorted first by Week_Of, then by Issue_Num. Give Issue_Num a
validation rule to prevent values less than 1 or greater than 8 being entered.

Embed the form based on tblTop8 in the form based on tblWeek_Of as a
subform, linking them on the Week_Of columns. You can then either navigate
to an existing Week_Of record in the main form or go to a new record and
enter a new Week_Of value in it. In either case the subform will show the
related rows from tblTop8 for the Week_Of in question, and you can enter new
rows in the subform for each issue, selecting a model and issue for each.

One important thing you should do is make the Week_Of and Issue_Num columns
in tblTop8 its composite primary key. Do this in table design by
Ctrl_clicking on each to select then both and then right-click and select
Primary Key from the shortcut menu. This will prevent more than one issue
per week being given the same issue number.

Ken Sheridan
Stafford, England