Help with database design?

[Randy]

I am trying to convert an Approach database to Microsoft Access. This
is called a Front Desk Program for clerical staff. They record what
clients leaves at the front desk for caseworkers.

The previous designer of the program had the clients basic information
flat db. Since the client could leave up to 35 pieces of information
the designer put 35 fields that were yes/no check boxes. Staff could
then click on multiple fields that applied to what they left at the
front desk. Setting up a drop down list will not work due to the fact
on a certain date they could leave multiple items.

Is there another way this information could be recorded and still get
reports by individual items as to what is left at the front desk?

 

[PC Datasheet]

Look at a multiselect listbox.

 

[Randy]

What is a multiselect listbox. Where can I find more information?

 

[Ed Warren]

"Clients can leave things at the front desk"
"on any given day a client may leave multiple things at the front desk"
"There are only 35 things a client can leave but that number may change in
the future)
"Clients may leave several of a specific type at the front desk on any given
day"

You will need the following tables:

1. Clients
ClientID with stuff about the client (clientID, name, address,
..... etc)
2. StuffToLeave
StuffID, StuffDescription
3. ClientDate (some clients will leave stuff on a date others will not)
ClientDateID ClientID DateLeft
3. ItemLeft
ItemLeftID, Date, ClientDateID, StuffID, numberleft

Clients 1 -->M ClientDate 1-->M ItemLeft M <--1 StuffToLeave


Forms:
1. Clients
(all the clients fields, used to manage the client list)
2.StuffToLeave
(all the StuffToLeave fields, used to manage the stuff list)
3. ClientDate
ClientDateID (autonumber, hidden) DropDownList for Clients, DateLeft
(default to today's date or now())

4. ItemLeft (used as a subform in the ClientDate form above)
ClientDateID(hidden textbox), ItemLeft (comboBox, data source
StuffToLeave), number left (default 1)


Reports (as required)

using queries you can get who left what when and group them by client, date,
item in any order then set the sort order to have them sorted by client,
date, item.
So you can produce reports like
DATE
Items Left
Clients
or
Clients
Date
Items Left , number


Ed Warren

 

[PC Datasheet]

Look up the multiselect property in the Help file to see how it works.
Create a listbox then open properties to the Other tab. The multiselect
property is the third one down.