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.

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.
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.


--
Build a little, test a little.


"Hannah" wrote:

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" wrote:

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.
My reasoning behind a table for each one was that it would be easier to query and get the totals for each section. No! A field for type will do it.
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.

--
Build a little, test a little.


"Hannah" wrote:

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 a

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!