database structure question - easy

[Daniel M]

I am creating a new database and wanted to get some feedback on how i should
structure it before i get too far.

I will have a form that has 10 drop down boxes on it. each one is cmbo1-10
with an associated qty text box, txtQty1-10. All the combo boxes pull from
the same table. The question is how do i save the data to the table? Should i
have 1 entry per combo box or should i have a single line with column for
each combo box?

Each entry in the table will have a date stamp, a combobox entry (or 10) and
a qty entry (or 10). And an entryID number.

I will then run reports weekly that will tally up all of a particular
combobox entry and add all the qtys together. the combobox entry will be
stored as a number corresponding to a productID in the product table.

Basically i will be selecting which product from the dropdown and a qty of
what has been shipped that day. then add them all up at the end of the week.
I want dropdowns so i can add products easily. If i save 1 set of entrys in
one row i will have to search for say product 1 in each of the combobox
entries 1-10. This doesnt sound like the best approach, but i'm not sure
about adding entries for each product either. any guidance on the issue would
be appreciated.

 

[Arvin Meyer [MVP]]

Hi Daniel,

I'm not really clear on what you are trying you accomplish in your data
collection. Can you supply more information?

 

[John W. Vinson]

I am creating a new database and wanted to get some feedback on how i should
structure it before i get too far.

Sounds like you need to backtrack a bit.

I will have a form that has 10 drop down boxes on it. each one is cmbo1-10
with an associated qty text box, txtQty1-10. All the combo boxes pull from
the same table. The question is how do i save the data to the table? Should i
have 1 entry per combo box or should i have a single line with column for
each combo box?

If you're designing your table to fit your form... you are on the wrong track.
Forms *are just windows*, tools to manage data in a normalized table. Design
the tables and relationships *first*, and then design the form to fit them!

Each entry in the table will have a date stamp, a combobox entry (or 10) and
a qty entry (or 10). And an entryID number.

If you have ten fields (for what? what are these combo boxes?) someday you'll
need eleven. It appears that you're making the very common mistake of
"committing spreadsheet" - e.g. one <something> to many <quantities> by adding
ten quantity fields, rather than *a second table* with ten *RECORDS*, each
with a quantity.

I will then run reports weekly that will tally up all of a particular
combobox entry and add all the qtys together. the combobox entry will be
stored as a number corresponding to a productID in the product table.

Easy with a properly normalized table; much harder with one field per
quantity.

Basically i will be selecting which product from the dropdown and a qty of
what has been shipped that day. then add them all up at the end of the week.
I want dropdowns so i can add products easily. If i save 1 set of entrys in
one row i will have to search for say product 1 in each of the combobox
entries 1-10. This doesnt sound like the best approach, but i'm not sure
about adding entries for each product either. any guidance on the issue would
be appreciated.

It isn't a good approach at all. Have you looked at the Northwind sample
database?? It correctly allows you to enter one, or two, or ten, or 866
quantities per order, by adding *new records* in the OrderDetails table.

 

[Daniel M]

Ok, lets start over and get to the end result...I need to tally up how many
of product x, y and z ship in a week. I created a product table and added x,
y and z to the table. when i get more products i add them to the table.

I want to be able to enter shipped products once or mulitple times a day. I
selected 10 drop downs to allow me to add 10 different products and entry in
one shot with a submit button. Right now i could have just used 3 one for
each product. If i ever get above 10 products i just fill out the form
however many times i need. 10 would give me an average of one submit /day.

So at the end of the week i need to know how many of product x is shipped.
so i will look for product x using a certain date range and add the qtys.

That is all this db has to do. So the question is how to structure the
database to handle this. I hope this helps explain things a littlebit
clearer. I know working from the form backwards may not be the best way to do
it but i'm not a db guy so i have to figure out what i want out of it and
workout how to get there.

 

[Daniel M]

See my other update.

Hi Daniel,

I'm not really clear on what you are trying you accomplish in your data
collection. Can you supply more information?
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[John W. Vinson]

Ok, lets start over and get to the end result...I need to tally up how many
of product x, y and z ship in a week. I created a product table and added x,
y and z to the table. when i get more products i add them to the table.

I want to be able to enter shipped products once or mulitple times a day. I
selected 10 drop downs to allow me to add 10 different products and entry in
one shot with a submit button. Right now i could have just used 3 one for
each product. If i ever get above 10 products i just fill out the form
however many times i need. 10 would give me an average of one submit /day.

So at the end of the week i need to know how many of product x is shipped.
so i will look for product x using a certain date range and add the qtys.

That is all this db has to do. So the question is how to structure the
database to handle this. I hope this helps explain things a littlebit
clearer. I know working from the form backwards may not be the best way to do
it but i'm not a db guy so i have to figure out what i want out of it and
workout how to get there.

Very reasonable!

What you need to do is identify the Entities - real-life persons, things, or
events - of importance; each kind of Entity gets a table. Then you determine
the Relationships between the entities. *Then* you create queries to relate
the tables, Forms to edit them, and - finally - Reports to print them out.

In this case you have a table of Products, with fields such as ProductNo
(primary key, manually assigned I'd say since Autonumbers aren't controllable
and will have gaps), ProductName, and any other information that you need to
know about the product - shipping weight, cost, whatever.

Next you'll have a table of Shipments with a (probably autonumber) ShipmentID,
Shipmentdate, and other fields about the shipment - destination, shipper,
whatever you need (you know your business, I don't!!!)

Since each Shipment may involve many Products, and each Product may be shipped
in many different Shipments, you need *a third table* to model the
relationship - ShippedProducts say. This would have fields for ShipmentID
(link to Shipments), ProductNo (link to which product is shipped), and
Quantity - and possibly other fields if you need them.

You could use a Form based on Shipments with a Subform based on
ShippedProducts. With a continuous Subform you could have a combo box to
select which product to include and a textbox to enter the quantity.

At the end of the month you can then run a Totals query to calculate the total
number of each product shipped in a date range, the average number of products
shipped per day or per shipment, the total number of shipments, the total
weight... whatever you need, you'll have all the data in an adaptable form!

 

[Daniel M]

Ok, so taking a step back, i understand what you are saying but i dont think
i need the third table. When i said this is all the db has to do i was
serious. Currently i have an excel spread sheet that lists products
shipped/week. I add up all the shipments i have of a specific product for one
day and add it up for the week. This is a little too manual for me so i
wanted to automate it a bit. One day i may ship 100pc of product x and
nothing else, other days i may ship prduct x, y and z. So i have to go search
each day for X add it up, each day for Y add it up, etc. *Also keep in mind
that shipping is not truely shipping in terms of packaging up and sending to
someone. I am using shipping as moving from one location in mfg to another.*
So in this case i may ship product x in the morning and product x and y in
the afternoon. Time is irrelevant only date.

That being said, i figured i could place a product, date, and qty line in
the table everytime i ship something. This would mean if i shipped more than
1 product at a time, ie: 100 product x and 40 product y, i would have 2
entries in the table. one for x qty 100 and one for y qty 40. I can then find
like dates and like products, add them up and report them.

Does this make sense? Hopefully i am not just saying the same thing over and
over again; and i truely understand the feedback i'm getting. I understand
the complexity of needing to add additional data later but i really dont see
it being needed for this project.

 

[John W. Vinson]

That being said, i figured i could place a product, date, and qty line in
the table everytime i ship something. This would mean if i shipped more than
1 product at a time, ie: 100 product x and 40 product y, i would have 2
entries in the table. one for x qty 100 and one for y qty 40. I can then find
like dates and like products, add them up and report them.

Does this make sense? Hopefully i am not just saying the same thing over and
over again; and i truely understand the feedback i'm getting. I understand
the complexity of needing to add additional data later but i really dont see
it being needed for this project.

By all means, add only the fields that you need: you know them far better than
I.

You may then be able to get by with two tables: Products and Shipments. The
Shipments table, though, would indeed have one *ROW* per product per shipment;
it might have fields like

Shipments
ShipmentID <Autonumber, Primary Key>
ProductID <Number or Text, link to primary key of Products>
ShipmentDate <Date/Time, default value =Date() >
Quantity <Number>

Your analysis of the reporting process is right on the mark.