Access Database

[Guest]

When setting up a database for products that has many different categories. I
have 7 tables that list that these categories. When there is a new product I
want it to list it only one time in he main table and it automatically would
be listed in these 7 or more tables. How would a person do this?

 

[Dirk Goldgar]

When setting up a database for products that has many different
categories. I have 7 tables that list that these categories. When
there is a new product I want it to list it only one time in he main
table and it automatically would be listed in these 7 or more tables.
How would a person do this?

It sounds as though you may be going about this the wrong way, but I'm
not sure. I would think that you'd have one table of Products and one
table of Categories (listing the 7 categories). Then, if a product can
only be in one category, there would be a CategoryID field in the
Products table, indicating which category that product is in. On the
other hand, if a product may be in more than one category, then you'd
need another table of "ProductsCategories", wherein each record includes
a ProductID (to link it to the specific product in the Products table)
and a CategoryID (to link it to the specific category in the Categories
table). It's really the ProductsCategories table, in this setup that
stored the information about which products are in which categories.

 

[Joseph Meehan]

When setting up a database for products that has many different
categories. I have 7 tables that list that these categories. When
there is a new product I want it to list it only one time in he main
table and it automatically would be listed in these 7 or more tables.
How would a person do this?

It sounds like you table design is wrong. Databases like Access work
with relationships. That is the products would be in one table the
categories in another where one or more category might be related to a
product. In addition you might have a customer table which would be related
to an Purchase table the purchase table would be related to the product
table etc.

If all this sounds really screwy, take a good look at the Northwind
example that comes with Access.

 

[Guest]

Dick
There are many products in this case (boats) I am looking at the cost
charged, for each boat while charter fishing. I could list all the things
that I want to know in one table but it would be five miles long. So I have
set up smaller tables for each subject that I need to know. As boats name &
size abut 7 columns in this table. Then the fishing equipment that that boat
used in a separate table. Then a table for there rates charged. I thought
there may be 15 or more of these tables.

Now when I bring in a new boat to the boat table I have to list it 15 times
to get it all the tables, seems like a lot of work.

What I want the program to do is when I enter the boat in the master table
it enters them in all the tables. When I start a new table about something
that I forgot or the captains are just starting to advertise so I can compare
it or when the rates change for 2007 I don’t have to go back and write in all
the boats in the new table. Now this may be screwy as all get out but what do
you expect from a fisherman.

 

[Stephen Russell]

Dick
There are many products in this case (boats) I am looking at the cost
charged, for each boat while charter fishing. I could list all the things
that I want to know in one table but it would be five miles long. So I
have
set up smaller tables for each subject that I need to know. As boats name
&
size abut 7 columns in this table. Then the fishing equipment that that
boat
used in a separate table. Then a table for there rates charged. I thought
there may be 15 or more of these tables.

Now when I bring in a new boat to the boat table I have to list it 15
times
to get it all the tables, seems like a lot of work.

What I want the program to do is when I enter the boat in the master table
it enters them in all the tables. When I start a new table about something
that I forgot or the captains are just starting to advertise so I can
compare
it or when the rates change for 2007 I don't have to go back and write in
all
the boats in the new table. Now this may be screwy as all get out but what
do
you expect from a fisherman.

I think that you need to create a Kit or a Bill of Materials for each
ship/boat that you have. Fuel consumption per hr should change per vessel
if they hold 5 people vs 40 people.

Same with insurance costs per vessel and staff required by ship. Is food
offered as well as bar?

Tables I would consider:
Inventory of Items - fuel, man hours, food, beer, supplies for sale, etc.
BOM - assembly of items by ship that can be structured per hour or per
tour/trip.
Ussage - Consumption of BOM line items with extended VALUES recorded here.
Ships - Vessels used

 

[David Cox]

What I want the program to do is when I enter the boat in the master table

it enters them in all the tables

This does not sound like good design. You should be entering boat name once.
Perhaps you would be better off using your expertise running your business
and calling in an expert to set up the database design with you? It will
save you time, money and grief in the long run.

 

[Guest]

Dick
I am going about it the wrong way; your reply helped me see that. I will try
to do a better job of explaining what I am trying to do with the data base.
I run a charter boat, there are many other charter boats in the port I fish
out of, there are also ports to the north and south of our port that have
charter fishing.
What I first wanted to do was compare there rates to see what would be the
going rate, for 5 hours of fishing, looking at that I found that the boat &
fishing equipment they have makes for more or less of a charge.
So the tables are for all the different things about captains and there
boats who I compete against.
I keep all the cost for our boat in the company books so do not need to
include a table for boat and trip cost. I know how much I have to charge but
want to find out how much I can charge and still get customers.
Are there sample tables of a problem like this, there must be some way to
enter this data with out having to look up ID number and boat name? If I
understand what you wrote and put it into my needs. I need a (product) boat
table that would list all the boats and many other tables that would list all
the different equipment that each boat has. But I can’t understand how to
enter the data without having the boat name in the first record.
Anyhow thanks for the help for all, the project has slowed way down but will
be much better when done.
We do not fish in the winter so if I can’t comprehend this problem by spring
I will look for a expert data baser.