Can I do this in access.....

[Mike]

I am very new to Access so please bear with me.

I need to create a db for our company that can do the
following:

We supply building materials for a specific builder. This
builder has 50 different homes 10 diferent elevations and
say 10 options for each model. Now for model "100" I have
a contracted cost for the base house. Then we add
elevation "2" which has a contract cost and lastly we add
a couple of options that each have their own cost.

The base, elevation and option costs ahr further broken
down into into groups; floors, walls,roofs, loose
materials,trim etc. Within these groups, let's use loose
material, would be an itemized list of building supplies
that need to ship to the customer.

How can I bring in multiple line items into an order based
on 3 - 4 variables? Then combine any like items that may
be in the list?

I hope that this is understandable and that someone can
give me some direction.

Thanks,
Mike























..

 

[Pavel Romashkin]

First off, you need to have separate tables for line items, options,
elevations, and homes. You will also need 4 junction tables: line items
joined to each home, option, and to each elevation - this gives you 3
types one-to-many relationships, and one, final, that will join Homes
with Options and with Elevations.
0) home -> list of line items
0.1) option -> list of line items
0.2) elevation -> list of line items
Now, for every job you ship to the builder, you need to join
1) homes to elevations
2) homes to options
Now you can build queries that will list all the line items for the
selected solution.
I didn't give much though to where Groups belong but seems like that
would be a field in the tables which would let you then select line
items used for each group. You would, of course, need a Groups table for
that.

Good luck,
Pavel

 

[kevin watkins]

Mike,

This can be done, and is done in a multiple of ways. The
most important thing to understand in a relationship
database is the relationships. How are the items going to
be related to another item.

I would set it up so that any option that can be selected
is in one table. Then as you build the customer order or
how ever you put the options together, use the primary
key/forign key to attach the individual cost centers to
the order.

These cost centers then have attached to them another
table that lists the quantity of items or material in that
center. The final part is creating the report that puts
this all together on one page. This is done through
queries.

Just remember that it is easiest to know what you want
prior to starting rather than trying to adjust it later.
I would suggest using a dry board and line out all the
features that you want the db to have. Then get a good
grasp of what you want it to do, prior to starting. this
will allow you to design your tables to provide the most
info with the least amount of input.

Good luck,