Design questions

[Hannah]

I am extremely new (and pretty much entirely self-taught) to Access and think
I should give some background to my questions. I'm currently working on a
database (separate to the one I've posted about elsewhere - but similarly
quite confusing!) that will be used to report a rubbish monitoring scheme.
There will be basic data about each country and harbours involved which will
only be updated as and when these change but there will also be waste
tonnages and monitoring data (detailing the number of bits of each kind of
garbage picked up) that need to be inputed monthly. The tables I have thought
out so far are:

Country - the info and contact details for each country (only one record per
country)
Harbours - harbour name, number of vessels
VesselTypes - choose from a number of different types (only allowed one
choice)
WasteTonnage - total tonnes collected
Monitoring - monitoring sheet number, number of bags
Plastics - number fields for each type of plastic (bottles, nets and so on).
There will also be a separate table for metals, wood, textiles and so on.

My big questions (I'm working on trying to improve my general access
incompetence but these are driving me bonkers!)

1. I think I need a many-to-many relationship between harbours and type of
vessels (one harbour can have many types; each type can belong to more than
one harbour) but I'm not sure how I go about doing this.
2. Again for vessel types, this is a multiple choice question. Someone
advised me to set up yes/no questions for each type of vessel and then the
partcipant could just choose the correct ones but I read elsewhere to avoid
yes/no boxes. Is there a better way to represent this?
3. Both waste tonnage and monitoring data need to be entered monthly. I'm
pretty sure just typing the month into each of these tables is the wrong way
to go about this so I was thinking I need a separate month table but I wasn't
sure how this should be joined?
4. I've given a separate table to each category of waste but should I
actually only have one table for this? My reasoning behind a table for each
one was that it would be easier to query and get the totals for each section
but I'm very open to any advice about this.

Sorry for the bombardment - feel a wee bit like I'm losing my mind! Any help
would be completely and thoroughly appreciated!

 

[KARL DEWEY]

I was thinking I need a separate month table but I wasn't sure how this
should be joined? Just use a DateTime field and extract by month or year or
year/month when needed.It seems that you do like to build tables. You do not need separate tables
for metals, wood, textiles and so on, just have a field that you enter type.
Again -- Plastics - number fields for each type of plastic (bottles, nets
and so on, just field for type.
Why cannot Waste Tonnage (total tonnes collected) and Monitoring (monitoring
sheet number, number of bags) be all in one record?
Why are you having tables for Harbours (harbour name, number of vessels –
always changing) and Vessel Types? You might include totals vessels entering
harbor per the collection period in the same record of the collection.
You never did say what the data is to be used for, what reports are to be
generated.

 

[KARL DEWEY]

I was thinking I need a separate month table but I wasn't sure how this
should be joined? Just use a DateTime field and extract by month or year or
year/month when needed.It seems that you do like to build tables. You do not need separate tables
for metals, wood, textiles and so on, just have a field that you enter type.
Again -- Plastics - number fields for each type of plastic (bottles, nets
and so on, just field for type.
Why cannot Waste Tonnage (total tonnes collected) and Monitoring (monitoring
sheet number, number of bags) be all in one record?
Why are you having tables for Harbours (harbour name, number of vessels –
always changing) and Vessel Types? You might include totals vessels entering
harbor per the collection period in the same record of the collection.
You never did say what the data is to be used for, what reports are to be
generated.

 

[Hannah]

Thank you for your help - that's straightened out quite a few things for me!
I think my obsession with tables, tables, lots of tables comes from some
training I had a few weeks back but I can see now how your suggestions are
better.

In answer to your question, the main purpose is to monitor rubbish being
landed in harbours. We need to see the total tonnage being landed and a small
proportion of that will be monitored and looked at in more detail (e.g. how
much plastic? is it particularly bad for a particular fleet?).

I'd wondered about waste tonnage being part of monitoring but a) I was
worried about confusing the subject and b) not all waste will be monitored so
I wasn't really sure it belonged in that table.

As far as the harbours and vessels go - they won't really be changing. What
we're recording is how many vessels are registered to each harbour (will
probably only be updated once a year) and which types of vessels are
registered to that harbour. I really wasn't sure what way was best to record
type of vessels - if I put it in the same table as harbours, it seemed to
make a lot of empty fields (there are about 15 different vessel types to
choose from) but if you think that's the way to go, I'll happily give it a
bash? Thanks again for all your help, I'm feeling a lot better about this.

 

[Hannah]

Just trying to put your suggestions into practice and have immediately
realised I explained myself very badly about monitoring.

Each monitoring sheet has several different categories (plastics, wood etc)
and in each category there are several different types (e.g. plastics -
bottles, crisp bags, oil drums). If I try to put the entire monitoring sheet
i(all categories) in one table, I can't add a field for type because each
monitoring sheet will involve each type. Does that make any sense? But I'll
think on a way around that. Thanks again and apologies for being lousy at
explanations.

 

[KARL DEWEY]

it seemed to make a lot of empty fields (there are about 15 different
vessel types to choose from)
Do not use a field per type but 2 fields - type & QTY.

i(all categories) in one table, I can't add a field for type because each monitoring sheet will involve each type.

Access tables are not sheets. Excel worksheets are sheets.
Even if you use a 'sheet' to collect the information you must reorganize it
for Access to be properly utilized.
The 'sheet' identity will have no meaning in Access except as a reference
back to your paper copy for authenication. So do not build tables to match
your sheet but to the manner you will use the data for reporting.

Harbour --
HarbourID - Autonumber - primary key field
HarbourName - text
Other information fields like type (deep water port, non-wharf anchorage),
stream fed, breakwater, etc.

Vessel --
VesselID - Autonumber - primary key field
Type - text

HarbourVessels --
HarbourID - number - long integer - foreign key
VesselID - number - long integer - foreign key
QTY - number - long integer

Collection --
CollectID - Autonumber - primary key field
CollectDate - DateTime
HarbourID - number - long integer - foreign key
Comment - memo

Categories --
CategoryID - Autonumber - primary key field
Type - text - plastics, wood, etc
SubType - text - plastics - bottles, crisp bags, drums - wood - lumber,
pallet, crate,

WasteMeasure --
CollectID - number - long integer - foreign key
CategoryID - number - long integer - foreign key
QTY - number - long integer
Measure - text - Ton, CuYd, etc

The table relate in a one-to-many except for predefined list. Use
form/subform with combo to select type, harbour, etc.

 

[Hannah]

Sorry about that Karl - 'sheet' was my bad choice of words (I meant sheet as
in paper sheet!). Thanks for all your help - you've given me heaps to think
about and get working on!