Need Help designing a table structure

[Kevin Rosenthal]

Hi all,

I am a newbie to creating access tables. I have a project that I am getting
confused on how to design the table structure. Here is my problem.

I am writing a clothing inventory database and I need to track the following
information.

Style Number
Color
Size

Now, any given style can have any number of colors or sizes. What would be
the best way to setup my tables to reduce the amount of duplicated data?
Also, would it be best to keep the current amount in the table or would it be
better to figure the total using a query of all inbound transactions and all
outbound transactions?

Any help would be greatly appreciated!!!

Thanks!

 

[tw]

To completely normalize this table you could have a colors table which lists
all the possible colors of any given item, a size table with all the sizes
that you carry, and a style table with all the styles available. then you
would have an inventory table that would have style, color, size, and
quantity fields

your reports could show how many of a specific style you have by grouping on
style number
your reports could show how many of a specific style/size you have be
grouping by style and size
or you can group by style and color or by style, size and color.

You would want to store the quantity of a specific style-size-color and you
would sum the quantity when grouping all of a specific style regardless of
color or size to get how many of a particular style item you have.

Does this answer your questions?

Your color table could have fields
Code - Color
1 - Red
2 - Blue
3 - Green

in your inventory table you would store the values 1, 2 or 3 based on the
color of the item

A size table isn't really necessary you could just store the sizes as 12,
12.5 etc but if the sizes are more complicated than you can use a size table
similar to this
Code - Size
1 - 12T
2 - 12.5T
3 - 12W
4 - 12.5W
5 - 12P
6 - 12.5P
etc

in your inventory table you would store style 1234 Red Dress in size 12T it
would be like this
StyleCode = 1234
ColorCode = 1
SizeCode=1 or ActualSize 12T (depending on how you choose to do this)

you could put combo boxes on forms so the users don't need to see the codes
just the styles, colors, and sizes

Does this help?

 

[Kevin Rosenthal]

Hi,

Yes, this helps a lot. If I might, I am having another Access issue. I
have written code in ColdFusion to access a Access Database and do all I
liked, but now I am in a situation where the person does not have access to a
server running coldfusion. SO, how do I get the forms to work like they do in
ColdFusion? What I mean is in CF I can run a query up front to create the
selection options and also to verify that a record with specific information
does not already exist. For example,

In my inventory table I need to see if this already exists and if so, update
it and if not either give an error go to a inventory addition screen. Also,
in some cases I would need to do just the oppisit. Such as removing from
Inventory.

Style - 1455
Color - 4 (red in the colors table)
size - 1 (Small in the size table)

In cold fusion I would get the data from the user in a form and they do
whatever query or pre-prosessing before I wrote to the database.

I just can not get the hang of how access will do this.

HELP!

Thanks!!!!
Kevin

 

[tw]

Well, I don't really know anything about ColdFusion, but you could do what
you are asking in a variety of ways. One is you could create an unbound
form with fields that represent the fields from the inventory table, but are
not bound to those fields, then you can run a query based on the data the
user enters into the form. The form which is unbound can call another form
which is bound using that query as the record source.

In this option, the user enters data onto a form, then clicks on a "find
data" button. (or whatever). What your find data button does is call
another form which has a query as the recordsource. The query can be one
that you save in the queries window or one that is saved in the form's
record source by clicking on ... so the record source is either going to be
the name of the query or the sql statement.

In the query you are going to use data from the first form as the criteria
for the data set. To do that you will refer to the form's controls... I.E
forms!frmName.txtStyle, where forms! identifies the type of object. frmName
is the name of your unbound form, txtStyle is the name of the control on
your form. In your sql statement you might have something like this...

select * from inventory where [Inventory]![Style] = forms!frmName.txtStyle
and _
[Inventory]![Color] = forms!frmName.txtColor and _
[Inventory]![size] = forms!frmName.txtSize


You could also create a bound form with a "find" tool as an option box so
that the user can find the inventory record to modify by typing in the
option box, this one is a little more complicated and might require a little
coding, but not much. Basically you have an unbound option box on the form
with the other fields bound to the inventory table or a query based on the
inventory table. The unbound option box will use a recordset clone of the
other data bound to the form as the source for the drop down list. It gets
more complicated when matching to more than one field, but it is possible.
The user selects from the list or types in the box to locate the record in
the list and selects it, then you have code in the update event to move the
record in the bound form to the record selected in the option box.