Right beginning

[Jose Lopes]

I’m dealing with this doubt for quite sometime. It all begins with table
design. I’m putting this here to see if someone can just point me out in the
right direction because I already build my DB from scratch a zillion times
now.

I work in a factory where we build three different types of products: A, B
and C. Workers are used to distinguish them apart by this letter type. They
are different just in this type (field) but all other properties (height,
length, weight, etc) are present in the three. When a costumer makes an order
(6 char string), it can be made of one A product and/or by B and /or C.
Example: in order 005675 was requested one A and one C; order 005676 was just
one A. But the workers are used to distinguish the products by an incremental
numeration of the three products (…A0025, A0026, A0027 and so on). This means
that the late order 005675 had an A0026 and C0015, order 005676 had just
A0027, and the next order 005677 will have an A0028, B0125 and C0016. I hope
I got it clear to understand.

I’m trying to achieve an auto numbering function for these increments, by
using a ProductType field (string type size 1) for the first letter and the
ProductReference (A####) would be a calculated field. So I thought of
creating a ORDERS table where I input the order number and client data, and
three different tables to PRODUCT_A, PRODUCT_B and PRODUCT_C, all related by
the ProductReference field. This way I will also be able to easily count and
report several things about the production although this will just end up
repeating the height, length, weight, dates, due dates, production dates on
each table… a complete waste of time. And this... doesn't smells right to me


My request: can someone point me out a correct way to structure this?

Many thanks for the help.

 

[Jeff Boyce]

The number and type of items making up your Order Details sound very much
like what you'd see in the Northwind (sample) database that comes with
Access. Have you looked at that sample's Order-OrderDetail table structure?

I believe you can find code at mvps.org/access to help incrementally count
(sequence) numbers in a table.

If I understand, one of your Orders can consist of any number of ProductA,
any number of ProductB, and any number of ProductC.

I don't understand why you/your factory/the workers need to know "C0015" and
"C0016". Are the actual items labeled this way, so that you are tracking
physical inventory? Or is this a convenient way to count how many "C"s have
been ordered? (If the latter, there's a lot simpler way!).

Regards

Jeff Boyce
<Office/Access MVP>

 

[Jose Lopes]

Well Jeff

That's correct my Orders can consist of any number of ProductA, any number
of ProductB, and any number of ProductC. The products are labelled this way
so they are easily found in the factory and everybody knows what product it
is. If I could I would have chosen to number them automatically. So I really
need these labels because they are used to it.

Regards

 

[Jeff Boyce]

Jose

I wasn't suggesting changing the way the products are labeled.

I was asking why the products are numbered.

Jeff Boyce
<Office/Access MVP>

 

[Jose Lopes]

I did not get that last question... They are numbered because they are
different in shape (although they have almost every other properties in
common) and must be traceable across the factory...

 

[Jeff Boyce]

Jose

Are you saying that all the "A"s are "different in shape" (from one A to
another)? Or that "A"s are not shaped the same as "B"s, ...?

I thought your earlier post used a sequential numbering scheme for "A"s (and
for "B"s, ...). How does that help you keep track ('traceable across the
factory')?

Regards

Jeff Boyce
<Office/Access MVP>

 

[Jose Lopes]

Hello again Jeff.

A products are all alike in shape (actually they really have an A shape, and
that is why by the A0000 numbering system they are more easily recognised by
all workers that have an operation to do on it).

B products have a B shape and are all similar in other properties. The same
for C.

Now, back to my original post… can you help me with this type of auto
numbering? If I click a dropdown box listing “A,B,C†and after I chose one of
them I need the field “Reference†to become the maximum value of that type
found in the table. At this pointI have an indexed field “Reference†as the
primary key for the Products table. This reference field is being written by
hand in the form and contains several references mixed: …A0125, B0023, C0089,
and so on.

Thanks for your patience.

 

[Jeff Boyce]

Jose

I'm afraid I still have to ask "why?"

If all "A"s have the same shape (an "A" shape), and they are not uniquely
identified (say, with a serial number or some other mark), then why do you
need to use a sequential numbering system on "A"s?

Would it be enough to know how many "A"s were ordered?

Regards

Jeff Boyce
<Office/Access MVP>

 

[Jose Lopes]

Hi Jeff.

So, are you suggesting I should make an automunber field and define another
field to simply select A, B or C types? That would create sequential numbers
but the A B and C products would came up without sequence. That has crossed
my mind already. Like you said the sequential numbering allow us to easily
count the number of products produced but, on the other hand, maybe the
effort to achieve it is huge.

What's your suggestion at this point? Thx.

 

[John Vinson]

Like you said the sequential numbering allow us to easily
count the number of products produced but, on the other hand, maybe the
effort to achieve it is huge.

PMFJI but...

Counting records should have NOTHING WHATSOEVER to do with assigning
ID values. They are different tasks.

A Count can (and should!) always be done using a Totals Query to
select and count records which meet some criterion.

John W. Vinson[MVP]

 

[Jeff Boyce]

John V. provided the answer I was heading toward ... don't use sequence
numbering to "count" your entries (that's what queries are for).

If you don't need to uniquely identify a specific "A" item, don't bother
using sequence numbers.

Regards

Jeff Boyce
<Office/Access MVP>