Help with my tables (1 Viewer)

[Infinite]

In that attached .zip file, I have my data base, and I im needing help with my tables. I am now going to be naming the tables and telling you what there for, and you can tell me what im doing wrong here.
(I will not be including forms or query's, as I dont think those are needed)
Starting from top to bottom (sorted in my custom sorting)

tblAmazonOrders
That table is for all of the orders that our company has from Amazon.com
Layout changeable: No
Reason: The table layout is such that I can just import the sales records
from the site, with no extra hassle.

tblEcwidOrders
That tables is for all of the orders that our company has on our personal site.
Layout changeable: No
Reason: The table layout is such that I can just import the sales records from the site, with no extra hassle.

tblWood
That table is for all of our purchases of wood to make our product.
Layout changeable: Yes

tblCommetns
That table is for comments that we have at shows.
Layout changeable: Yes

tblEvents
That table is for all of your events we have.
Layout changeable: Yes/no
Reason: It kinda has to stay the way it currently is to operate, but, in that sense, it can change, just with some hassle.

tblGasCosts
That table is the gas costs of each year for the IRS Mileage per gallon.
Layout changeable: Yes

tblHomeAway
That table for the fact that we like to keep track if they show was in a building, or outside, like in a park.
Layout changeable: Yes
No longer in the date base, replaced with check boxes.

tblInsideOutside
That table is for the fact that we like to keep track if we had to stay at a hotel, or we could drive to the show, and back without a hotel.
Layout changeable: Yes
No longer in the date base, replaced with check boxes.

tblShowCosts
That table is for the costs of each show. Gas, booth, other fees, etc, etc.
Layout changeable: Yes

tblShowFood
That table is to keep track of the food that is purchases at shows. This is one that I know I need help with. What happens if we have a show that is 12 days long? (and we do) And for each food sale, I have 5 fields. and if its 12 days long, that is 12*5 fields. That is 6 fields. Not gonna happen. And that is only 1 meal a day. Times 65 by 3, and we get 180 fields. That, is laughable. Just not sure how to fix that.
Layout changeable: Yes

tblShowOnline
That table is to keep track of the products, to see if we currently sell them online only, only bring them to shows, do both or neither.
Layout changeable: Yes

tblShows
That table is for the shows. Now, dont confuse this with events, as we have 1 show, but we do it twice, so we have two Events.

tblShowSales
That table is for the sales that we have at each Show. It should be called tblEventSales, but so far ShowSales is how it is. It can (should) change if needed.
Layout changeable: No
Reason: The table layout is how we import our show sales from our site we use to make purchases at Shows (Events).

tblShowTypes
That table is to keep track of each type of show. As you can see, we have fairs, Gun shows, etc, etc.
Layout changeable: Yes

tblStates
That table is to keep track of the state the show was at.
Layout changeable: Yes

tblCategory
That table is to keep track of what type of item it is. Sword, Pistol, etc, etc.
Layout changeable: Yes

tblItemCutOutLength
That table is to keep track of how long it takes to cut out each item.
Layout changeable: Yes

tblItems
That table is for all the items we have.
Layout changeable: Yes

tblItemSizes
That table is for the sizes of each item.
Layout changeable: Yes

tblStatus
That table is to keep track of the items status. Retired, Current, etc, etc.
Layout changeable: Yes

tblItemeOtherNames
That is for the (as the name implies) other names, or aliases of each item.
Layout changeable: Yes


This data base is for everything to do with our business, and it sales.
That is all of the tables that I have in the data base. Any one who is going to help me, thank you much, because it will most likely take some time, as there is a lot of tables. Those of you that dont help, I completely understand.

PS: If I should put this post in a different format (e.g Put quotations around the table name) just say it and ill do that.
Thanks!

 

[plog]

De ja vu:

http://www.access-programmers.co.uk/forums/showthread.php?t=276769

My critiques then, still stand.

 

[jdraw]

Infinite,

Most database development starts with a business issue/opportunity. A description of the business to be supported/automated by a database is created to identify the things involved and how they fit within the business. This becomes a "specification" (blueprint) against which development is done.

Here is a tutorial that starts with a "business description" and it goes through a number of steps to create a data model/entity relationship diagram that reflects the database design to match the specification. This shows the steps and the solution. To get all the benefits from the tutorial, you must work through it.

Good luck. It will pull the pieces together if you work through it.

 

[Infinite]

Re: http://www.access-programmers.co.uk/forums/showpost.php?p=1423548&postcount=2

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:

If I understand you, I should have a field that is (like in the example) costID and in the cost ID I have...lets say, booth costs, and the id for booth costs is 1. so I should have:

CostID---------CostAmount--EventID
BoothCost-----$11.11--------1
Electric--------$11.11--------1

Is that the way to do it?


For those (and me) who dont like long forums, I would rather not, but I want to fix these errors one by one, and make sure that they are the correct way, not just stabbing in the dark, checking, the field is wrong (or correct), repeat.

 

[plog]

I wouldn't call it CostID. 'ID' should be reserved for foreign and primary keys, which should be numeric. I would call it CostType. This is what it would look like:

Costs
Cost_ID, CostType, CostAmount, ID_Event
1, Booth, 11.11, 1
2, Electric, 21.19, 1
3, Booth, 19.13, 2

 

[Infinite]

Ok, so, I wouldn't make a new table, just change my current ones setup.

Just noticed, is Cost_ID a PK auto-number?
If so, that is...better then my current one

Also, were would I get the CostType options? A drop down menu? Most likely not...Or would it just auto choose when some one enters the information need on the form?



One more thing, im now using check boxes for my Home and inside table. 2 down, eleventy-seven more to go.

 

[Infinite]

With the tables tblStatus (Should be tblItemStatus) should I change that and tblCategory to any thing? Or are those fine?

 

[plog]

If the CostType options have more data that relate to them, then you would make a table (and then use a numeric foreign key). If not, then you should review the previous thread where I discussed that.

Again, tables with only one real field shouldn't exist. That means no tblStatus and tblCategory.

It's all there in the previous thread. If you want me to stop giving advice and have others chime in, I will drop out. If not, please review that thread and stop repeating your questions to me.

 

[Infinite]

It's all there in the previous thread. If you want me to stop giving advice and have others chime in, I will drop out. If not, please review that thread and stop repeating your questions to me.

Sorry! Going to go read it, and try to do it all. Sorry again.

 

[MarkK]

please review that thread and stop repeating your questions to me.

plog, you are under no obligation.

 

[Infinite]

Ok...I really, really dont want to have to ask this question, or any for that matter. The question is, how do get access to enter Booth as the CostType after I enter X amount of dollars on my form? The posts on the other thread didn't have this question, nor answer.
Are the pictures how it should be set up? Minus the List box?

 

[plog]

The screenshots look good. To get data in you could either make it an input where people could type what they want the cost to be. Or you could make it a drop down and limit the options to only those you approve. To do that, follow these instructions:

Open table in design view.
Click on the field.
Go the bottom of the screen.
Click on the 'Lookup' tab.
Change the drop down to 'Combo Box'
Change 'Row Source Type' to 'Value List'
Input the values into the 'Row Source', seperating them with semi-colons
e.g. Electric;Booth;Housing;

Changing 'Limit To List' to Yes will disallow anyone from typing in their own categories.

 

[jdraw]

Infinite,

Did you look at the tutorial in post 3?

 

[Infinite]

To get data in you could either make it an input where people could type what they want the cost to be.

Ok, im thinking that I could just set the code on load to load certain labels to a certain type. So, it would look like

Private Sub Form_Load()
Label22 = "Booth"
Label23 = "Electric"
Label24 = "Other Fees"

End Sub

Would that work? It would take a TON of code, and I would have to change it everytime I add a new type of payment, but I could do it, and it would be part of what you said I should do, and part what I would do. Would that idea work?

 

[plog]

There will be plenty of time to decide if you want to wallpaper or paint the upstairs bathroom. Right now, you should focus on laying the foundation properly. You need to get your table structure correct.

Tables, then Reports/Queries then Forms.

 

[jdraw]

@infinite,

Have you worked through the tutorial in post 3?

As readers have been saying -- you need to get your database and table structures right before worrying about forms and events.

 

[vbaInet]

There will be plenty of time to decide if you want to wallpaper or paint the upstairs bathroom. Right now, you should focus on laying the foundation properly. You need to get your table structure correct.

Tables, then Reports/Queries then Forms.

@infinite,

Have you worked through the tutorial in post 3?

As reader have been saying -- you need to get your database and table structures right before worrying about forms and events.

We've been banging on about this issue for quite some time.

This thread is supposed to be about your table structure Infinite, not about forms, queries or reports. Please focus on this as you're getting some good help from some of the best, so don't lose this opportunity.

 

[Infinite]

This thread is supposed to be about your table structure Infinite, not about forms, queries or reports. Please focus on this as you're getting some good help from some of the best, so don't lose this opportunity.

I know, just got a ways ahead of my self sorry about that. Also, meant to respond earlier, but I have been a little busy.


Ok, what I did in the time I had was remove the tables TblStatus, tblCategory. tblOnlineShow and tblShowType.

I am pretty sure that is what you guys wanted done. I also have a new tblShowCosts like you said I should, but (this is next sentence is about forms) I'm not sure how to make it work for my forms. But, that is a question/discussion/answer for another time.
Now that I have finished those tables, im going to work in getting a description for each (most) of my fields. What should I be working on next?

 

[MarkK]

What should I be working on next?

Have you worked through the tutorial in post #3?

 

[JLCantara]

MarkK: did you download his DB? I did and there are so many flaws in there that it should be remade from A to Z!!! Mr Infinite should first work post #3, and then any web tutorial on DB normalization...