How is this? (1 Viewer)
- Thread starter Infinite
- Start date
My comments all relate to your tables. If you don't set those up properly, and I don't think you have, everything else is pointless in reviewing. Here's the issues I see:
Tables with only 1 real field: autonumber primary keys don't count as a real field. So, tblCategory, tblHomeAway, tblInsideOutside, tblShowTypes don't need to exist. You're really not gaining anything by linking to a table with just one field.
tblEvents.StateID is text: You have a states table, with an autonumber primary key in it, but you use text for this field. Since you have an ID field in tblStates, any field you want to link to it should be done using that field.
tblShowCosts: This is improperly structured. You should aim to put data in a table and not in the names of the table nor fields. What I mean is, "Electric" shouldn't be a field name, but a value in a field called "CostType". This table should probably have this structure:
tblShowCosts
Cost_ID, EventID, CostType, CostAmount
And that's it. Then for every cost (e.g. TaxPaid, Trailer, etc.) you would add a record and put the type it was (e.g TaxPaid, Trailer, etc.) into the CostType field. Tables should add data vertically (with rows) and not horizontally (with columns). Suppose you were to add a new CostType. With your structure you rework your table and any queries, forms and reports based on it. With the proper structure, you simply just add a new record and put that type in the CostType field.
No Notation: This is the biggest and most egregious error. In Desin view of the table, you can add notes to every field. You should do this. Just what the field is for and its intended use. It will help you when you have to work on it in the future and will help anyone who comes after you and has to work on it.
Tables with only 1 real field: autonumber primary keys don't count as a real field. So, tblCategory, tblHomeAway, tblInsideOutside, tblShowTypes don't need to exist. You're really not gaining anything by linking to a table with just one field.
tblEvents.StateID is text: You have a states table, with an autonumber primary key in it, but you use text for this field. Since you have an ID field in tblStates, any field you want to link to it should be done using that field.
tblShowCosts: This is improperly structured. You should aim to put data in a table and not in the names of the table nor fields. What I mean is, "Electric" shouldn't be a field name, but a value in a field called "CostType". This table should probably have this structure:
tblShowCosts
Cost_ID, EventID, CostType, CostAmount
And that's it. Then for every cost (e.g. TaxPaid, Trailer, etc.) you would add a record and put the type it was (e.g TaxPaid, Trailer, etc.) into the CostType field. Tables should add data vertically (with rows) and not horizontally (with columns). Suppose you were to add a new CostType. With your structure you rework your table and any queries, forms and reports based on it. With the proper structure, you simply just add a new record and put that type in the CostType field.
No Notation: This is the biggest and most egregious error. In Desin view of the table, you can add notes to every field. You should do this. Just what the field is for and its intended use. It will help you when you have to work on it in the future and will help anyone who comes after you and has to work on it.
Last edited:
Infinite
More left to learn.
- Local time
- Today, 03:49
- Joined
- Mar 16, 2015
- Messages
- 402
Ok, so what should I do with those fields then? I need them for the drop down list for the editing of shows.
Not quite sure what you mean, could you elaborate more?
So with this I should have like...Drop down list for what it is? Not (again) quite sure.
If a field only has 2 options (e.g. Home/Away) then it should be a Yes/No field type and designate one value as the default when naming it. For example name it [LocationHome] and if it is set to Yes it means it is Home, if false it means Away.
For multiple values, in design view you click on the Lookup tab of the field and insert valid values there.
--
tblStates has these fields:
StateID, State, [State Name]
The purpose of StateID is to be a foriegn key (http://www.w3schools.com/sql/sql_foreignkey.asp) in any other table that needs to link to it. You used the State field, which is incorrect since you have a StateID. Actually, if you look at your tblEvents you will see one with "IL: Illinois" in there, which will prevent it from linking back to tblStates.
--
Yes you should have a drop down list for CostTypes, but not lookup tables as I explained initially. You would go to the Lookup tab and assign the valid values there.
For multiple values, in design view you click on the Lookup tab of the field and insert valid values there.
--
tblStates has these fields:
StateID, State, [State Name]
The purpose of StateID is to be a foriegn key (http://www.w3schools.com/sql/sql_foreignkey.asp) in any other table that needs to link to it. You used the State field, which is incorrect since you have a StateID. Actually, if you look at your tblEvents you will see one with "IL: Illinois" in there, which will prevent it from linking back to tblStates.
--
Yes you should have a drop down list for CostTypes, but not lookup tables as I explained initially. You would go to the Lookup tab and assign the valid values there.
Infinite
More left to learn.
- Local time
- Today, 03:49
- Joined
- Mar 16, 2015
- Messages
- 402
How would I tell access that?
Ok, I agree with you there, how would I fix it?
Ok, so lets say I create a combo box using the Wizard, would I just use the "I will type in the values I want" option? And type in the show types? That would make sense seeing as there is only so many show types. If that is not the way to go, then using the type in values doesnt make sense
If you wanted to convert it back to text, you could do so in a query--If true print "Home", else print "Away".
I think that postal code is fine to use as a foreign key, so my advice is to drop the ID from tblStates. Then you change the StateID in tlbEvents field to only hold 2 characters.
Infinite
More left to learn.
- Local time
- Today, 03:49
- Joined
- Mar 16, 2015
- Messages
- 402
Were would I input that in a query?
And, the things that you have mentioned I have wrong, are they things that I should never do, or things that are just like...basic stuff im doing wrong? Will it every harm my database how im doing it? I plan on fixing the things wrong, but im just trying to see how good it is so far.
You would make a calculated field. It would be in a new space in design view.
Yes the things I mentioned (storing calculated values, redundant data, using correct foreign key) are all things you shouldn't do. It can harm performance (but honestly, your database isn't big enough to actually suffer anything from it) and make you lose data (like that IL-Illionois issue).
Read that link I posted about normalization. That's what you need to learn about.
Yes the things I mentioned (storing calculated values, redundant data, using correct foreign key) are all things you shouldn't do. It can harm performance (but honestly, your database isn't big enough to actually suffer anything from it) and make you lose data (like that IL-Illionois issue).
Read that link I posted about normalization. That's what you need to learn about.
