If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Design questions
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! |
#2
|
|||
|
|||
Design questions
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! |
#3
|
|||
|
|||
Design questions
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! |
#4
|
|||
|
|||
Design questions
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. "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! |
#5
|
|||
|
|||
Design questions
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! |
#6
|
|||
|
|||
Design questions
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! |
#7
|
|||
|
|||
Design questions
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! "KARL DEWEY" wrote: 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! |
Thread Tools | |
Display Modes | |
|
|