B
Beetle
Not quite. First, if TransID 1 and TransID 2 are part of the same Order, then
it would look like;
TransID, 1. OrderID, 1.
TransID, 2. OrderID, 1.
In addition to that, you would not store the Article description in the
Transactions table. In one of your earlier posts you stated that you had
an Articles table. That table should have fields like;
ArticleID (Primary Key - probably AutoNumber)
SupplierID (Foreign Key to Suppliers table)
ArticleDescription (values like xxxPants, qqttLongSleeveShirt, etc.)
In your Transactions table you would only store the ArticleID, so the data
should look like;
TransID, 1. OrderID, 1. ArticleID, 14. Size, 38/36. Quantity, 5.
TransID, 2. OrderID, 1. ArticleID, 26. Size, XL. Quantity, 5.
And, you could normalize this even further by having a table for sizes and
you would only store the SizeID in the Transactions table
--
_________
Sean Bailey
it would look like;
TransID, 1. OrderID, 1.
TransID, 2. OrderID, 1.
In addition to that, you would not store the Article description in the
Transactions table. In one of your earlier posts you stated that you had
an Articles table. That table should have fields like;
ArticleID (Primary Key - probably AutoNumber)
SupplierID (Foreign Key to Suppliers table)
ArticleDescription (values like xxxPants, qqttLongSleeveShirt, etc.)
In your Transactions table you would only store the ArticleID, so the data
should look like;
TransID, 1. OrderID, 1. ArticleID, 14. Size, 38/36. Quantity, 5.
TransID, 2. OrderID, 1. ArticleID, 26. Size, XL. Quantity, 5.
And, you could normalize this even further by having a table for sizes and
you would only store the SizeID in the Transactions table
--
_________
Sean Bailey
Dale G said:The last part didn't come out right.
TransID, 1. OrderID, 1. ArticleID, xxxPants. Size, 38/36. Quantity, 5.
TransID, 2. OrderID, 2. ArticleID, qqttLongSleeveshirt. Size, XL. Quantity 5.
Does that look right?
Dale G said:Ok I changed a few things. Table names and fields.
tblEmployees, EmpNo, LastName, FirstName, AnnversaryDate.
tblUniformOrders, OrderID, EmpNo, DateReceived, DateOrdered.
tblUniformTrans, TransID, OrderID, ArticleID, Size, Quantity.
Thinking ahead a little, when I begin to record orders I believe each
Article along with the quantity will be 1 record.
Example,
TransID OrderID ArticleID Size
Quantity
1 1 xxtPants
38/34 5
2 1 qxqShortsleeve Shirt L
5
Does that seem right to you, or am missing something?
Beetle said:You're getting closer, but you don't need EmployeeID in tblUniformTransactions.
Each "transaction" is related to the main Order via OrderID. Since the main
Order record has the EmployeeID, that's all you need.
I feel I understand the basics about Tables
It's getting the info from the table that seems to be the hard part.
It will seeem much easier once you begin to understand the
multiple options you have for displaying information. However, as Tina and
John have already pointed out, if you don't get the tables right, it will be
difficult to get useful information from your application.
It's important to be aware of the difference between *storing* information
and *displaying* information. Any given attribute/field/piece of information
should only be *stored* one time, in one table (not including key values).
I'm talking about fields like Employee First and Last Name, Supplier Name,
Address, Phone Number, things like that. Key values are stored in more than
one table in order to create relationships.
On the other hand, you can *display* information as many times, in as
many places (forms, reports, etc.) as is necessary for your application.
Let's take a simplified example based on your application. You might set
up a main form/sub form where the main form is based on tblUniformOrders
and the sub form is based on tblUniformTransactions. Obviously, when you
create a new Order record in the main form, you need to be able to see the
Employee names in order to make an appropriate selection. So, you put
a combo box control on the form that is bound to the EmployeeID field
from tblUniformOrders. The combo box would have the following properties;
Control Source = EmployeeID (determines which field the cb is bound to)
Row Source = Select EmployeeID, [FirstName] & " " & [LastName] As FullName
From tblEmployees Order By tblEmployees.LastName
(the Row Source determines what is *displayed* in the cb)
Bound Column = 1 (determines which field from the *Row Source* is stored
in the *Control Source*)
Column Count = 2 (you have two columns in your *Row Source* - not
including the Order By column - so you need two
columns
in your cb)
Column Widths = 0", 2" (this would hide the EmployeeID and show the FullName)
The above combo box would *display* a concantenated First & Last Name
from tblEmployees, but would only *store* the EmployeeID in tblUniformOrders.
I think you should take Tina's advice, and do a little more research on proper
normalization before you proceed much further.
--
_________
Sean Bailey
:
Ok now here is what I have on
tblUniformOrders
OrderID, EmployeeID, DateOrdered, DateReceived, Supplier.
tblUniformTransactions
TransID, OrderID, EmployeeID, ArticleID, Size, Quantity.
What do you think?
I have no problem removing one of the EmployeeIDs as long as I can track
exactly what article, and how many, each employee ordered.
I feel I understand the basics about Tables
It's getting the info from the table that seems to be the hard part.
your help is appreciated, thank you.
:
okay, we're kind of stuck in the mud here, hon. you need a better grasp of
relational design principles so you'll understand what i'm telling you about
analyzing your process to best decide what fields belong in what tables. i
imagine you're chomping at the bit to get past tables and move on to
queries, forms, and reports; everybody feels that way at first, believe me,
because it seems like the tables/relationships just can't be so all-fired
important. but i really, really urge you to stop, take a step back, and
invest some quality time in learning the basics of relational design. if you
don't do it now, you'll go back and do it after a few enormously frustrating
weeks or months of trying to build a solid house on top of an unstable
foundation - and it'll be a hundred times harder then. to get started, go to
http://home.att.net/~california.db/tips.html#aTip1.
hth
Ok Now I have,
tblUniformTransactions
TransactionsID, OrderID, EmployeeID, ArticleID, Size, Quantity, Integrity,
Integrity is for the condition the order is received.
For example sometimes the order is incorrect by size, color, or style.
Other
times the order is short, or missing items. Sometimes both.
The uniforms are ordered on a individual bases, an employee is allotted a
annual dollar amount, based on their hire date, that's why I had
anniversary
date in the table, but maybe it should be on a different table, like the
employee table.
The orders are wrote out by hand & faxed.
The faxed form has an ID for the article and description as well.
tblUniformOrders
OrderID, EmployeeID, AnniversaryDate, Department, DateOrdered,
DateReceived,
Duration, Integrity, Supplier.
I'm not sure how to assign the key. When I try I receive this message,
Primary Key cannot contain a null field.
:
comments inline.
tbluniformOrders; Order ID, Employee ID, Employee Name, Anniversary
Date,
Department, Date Ordered, Date Received, Duration, Integrity,
Supplier.
tbluniformOrders
OrderID (primary key, i assume)
EmployeeID (does not belong in this table, unless each entire order will
*always* be for one or more articles for a single employee only)
EmployeeName (does not belong in this table, period. the employee's name
should only be defined in the employee table.)
DateOrdered (okay)
DateReceived (okay)
Duration (i'm not sure - what is this?)
Integrity (ditto above)
Supplier (okay, again assuming that each entire order will *always* go
to a
single specific supplier)
tblUniformtransactions; Order ID, Employee Name, Article, Size,
Gender,
Quantity, Department, Integrity, Supplier.
I think your right. Looks like I should remove some from
tbluniformOrders.
tblUniformtransactions
OrderID (if this is the primary key, recommend you name it something
else,
like TransID, assuming that the primary key of tbluniformOrders is named
OrderID. if this is the foreign key from tbluniformOrders, that's fine,
but
then where is the primary key field for this table?)
EmployeeName (this is iffy. are you providing the name in the order
because
it will be put on the uniform? AND might the employee's name change
between
one uniform order and the next? if both are true, then yes it makes
sense.
but if you're just doing it to feel better because you're using to
seeing
employee information in a transaction record - Excel-style - then you
need
to get past that psychological block and embrace relational design
principles.)
Article (okay)
Size (okay)
Gender (do you sometimes order a male uniform for a female employee or
vice
versa? or does an employee's gender sometimes change between one order
and
the next? if no to both questions, this field doesn't belong in this
table;
it belongs in the employees table.)
Quantity (okay)
Dept (somewhat iffy. i'd imagine that employees might move from one dept
to
another, so i can see a legitimate need for tracking point-in-time dept
there. and if you might order uniforms for more than one dept in the
same
order, that's also a legitimate need for dept in this table. if you're
not
sure, re-analyze your process.)
Integrity (?? you have an Integrity field in tbluniformOrders. is there
a
need to track at both the order level AND at the
transactions-within-an-order level? if the two Integrity fields describe
the
same thing, my guess is that it's inappropriate to have the field in
both
tables; you have to ask yourself: does the Integrity value describe an
order as a whole? or does the value only apply separately to each
transaction within an order?)
Supplier (does not belong in this table OR does not belong in
tbluniformOrders. you have to analyze your process. will each order go
to a
single supplier? if so, then the Supplier field belongs in
tbluniformOrders
*only*. or may a single order go to many suppliers, transaction by
transaction? if so, then the Supplier field belongs in
tblUniformtransactions only. but having the field in both tables makes
no
sense.)
just as a side note, suggest you 1) be consistent in how you name your
tables (and all other objects you create in Access); it makes it easier
for
anyone who looks at the database, and especially SQL statements and VBA
code, to read and understand the names - my personal naming convention
is
lowercase prefixes with multiword names having first-letter
capitalization,
such as tblUniformOrders, tblUniformTransactions - and, 2) don't put
spaces
in the names of anything you name in Access; for more information, see
http://home.att.net/~california.db/tips.html#aTip5.
hth
:
okay, but's let's back up a minute. before i address your form
question,
tell me what the table(s)/fields names for your orders tables,
because
already i believe you're breaking normalization rules - so we want
to
fix
that before you work on forms.
hth
Ok I think I have that set up. I have on my Uniform orders form,
Employee
ID(combo box), Employee Name, anniversary date, Job title, and
Date
Ordered
Date Received, Supplier (combo box).
I have the employee id combo box Row Source,
SELECT Employee.[Employee ID], IIf(IsNull([Last
Name]),IIf(IsNull([First
Name]),[Job Title],[First Name]),IIf(IsNull([First Name]),[Last
Name],[First
Name] & " " & [Last Name])) AS [Employee Name],