Stuck, Oredering form, Any ideas?

[Dale G]

I’m trying to create a data base for ordering employee uniforms at my place
of employment.
So far I’ve created the following tables, Employee, department, Gender,
Article, supplier.
I’m stuck on how to set up a table for uniform orders, and then combine that
with my Employees table, in a Query. From there I would like to create an
order form using that query. Any ideas?
My employees table has the following fields, ID autonumber, Employee id,
Last name, first name, anniversary date, Job title, home phone, cell phone,
address, city, state, zip code, e-mail.
I also have a query Employee Extended, this is Similar to the example
(Northwind).
I can’t find any examples to follow. If possible I would like part of the
form, the employee part, to Auto fill.
I’m thinking the form should have employee Info, info about the article
ordered, date ordered, date received, duration between ordered & received,
cost, & integrity of received Order.
Any suggestion will be appreciated.

 

[tina]

yes. don't try basing an Orders data entry form on a multi-table query. you
don't need it, and there's a good change it wouldn't be updateable after
you'd written it. having said that much, let's forget about forms for a
minute and concentrate first on building a table to store the orders. i'm
guessing that you may need to order multiple uniforms at one time, but want
to tie each uniform to a specific employee. i'll also assume that each order
goes to a single supplier; in other words, if you're ordering x article from
supplier A and x articles from supplier B, you'll enter a separate order for
each supplier. if that's correct, you need two tables, as

tblOrders
OrderID (primary key)
InvoiceNumber (if you need it. and if it's unique, you may use that as the
primary key instead of having the OrderID field - but, in any case, DON'T
use autonumber to generate an invoice number because there *will* be gaps in
the sequence sooner or later, and probably sooner.)
OrderDate
Supplier (foreign key from tblSuppliers)
<other fields that describe the order *as a whole*, but don't describe order
details, or supplier details - unless that supplier detail is *specific to
the order*, NOT merely specific to the supplier. also, do NOT include a
field for the total cost of the order, that should be a calculated value;
though you may include a field for a discount amount or percent, a tax rate,
a shipping charge - hard values that apply to the specific order *as a
whole*.>

tblOrderDetails
DetailID (primary key)
OrderID (foreign key. if you use an invoice number as primary key for
tblOrders, then use it as the foreign key in this table, instead of
OrderID.)
Article (foreign key from tblArticles)
Size
Color
Price
EmployeeID (foreign key from tblEmployees)

using the above table setup (which is an *example*, remember), your forms
setup would be: a mainform bound to tblOrders. the Supplier field would be
bound to a combobox control in the mainform, with the combobox's RowSource
set to tblSuppliers, so you can choose a supplier for the order. a subform
bound to tblOrderDetails, with the LinkChildFields property set to OrderID
(the foreign key field in tblOrderDetails), and the LinkMasterFields
proeprty set to OrderID (the primary key field in tblOrders). the EmployeeID
field would be bound to a combobox control in the subform, with the
combobox's RowSource set to tblEmployees, so you can choose an employee for
the order.

hth

 

[tina]

i want to expand on my statement posted elsewhere in this thread.

yes. don't try basing an Orders data entry form on a multi-table query. you
don't need it, and there's a good change it wouldn't be updateable after
you'd written it.

a multi-table query is often very useful for reports, and for forms where
data is going to be *reviewed* rather than added/edited. and yes,
*sometimes* in forms where data will be edited, as well. but it's a common
mistake of new users to try to use a single form to add/edit records in
multiple tables, just because the data in those tables are related - and in
most cases, it's just not appropriate, or even the easiest way to do it. i
would be most likely to use a multi-table query as a data entry form's
RecordSource when the tables involved have a one-to-one relationship, but
that's not a common relationship. the majority of table relationships are
one-to-many; and while i don't rule it out entirely, i really can't think of
an example where i'd use a multi-table query for a form to edit the data in
tables with a one-to-many relationship.

hth

 

[Dale G]

Thank you, I’ll give that a try, it may take a while. But I’m sure I’ll have
more questions.

 

[tina]

no problem, i'll be around; i try to check the ngs once an evening, when i'm
involved in a thread.

btw, i notice i forgot to include a quantity field in tblOrderDetails -
oops! you'll want to include whatever fields in tblOrderDetails that you
need to describe a specific order detail. and if you find order details
confusing, it may help to think of them as line items on invoices (or sales
order) - one record equals one line item on one invoice/sales order.

hth

 

[Dale G]

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], Employee.[Anniversary Date],
Employee.[Job Title] FROM Employee ORDER BY IIf(IsNull([Last
Name]),IIf(IsNull([First Name]),[Job Title],[First Name]),IIf(IsNull([First
Name]),[Last Name],[First Name] & " " & [Last Name]));
And the column count at 5, the widths 0.5";1.6";0.8";1";1". What this shows
is the Employee ID, Employee Name, Anniversary Date, and Job Title. When I
use the combo box it populates only the Employee ID. Is there a way to make
this combo box auto fill others?

In the sub form I have order ID, employee name, Article, Size, quantity,
Gender, department, supplier.
I should change some of that. Any way that’s where i'm at.

 

[Dale G]

I changed job title to department on all.

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], Employee.[Anniversary Date],
Employee.[Job Title] FROM Employee ORDER BY IIf(IsNull([Last
Name]),IIf(IsNull([First Name]),[Job Title],[First Name]),IIf(IsNull([First
Name]),[Last Name],[First Name] & " " & [Last Name]));
And the column count at 5, the widths 0.5";1.6";0.8";1";1". What this shows
is the Employee ID, Employee Name, Anniversary Date, and Job Title. When I
use the combo box it populates only the Employee ID. Is there a way to make
this combo box auto fill others?

In the sub form I have order ID, employee name, Article, Size, quantity,
Gender, department, supplier.
I should change some of that. Any way that’s where i'm at.

no problem, i'll be around; i try to check the ngs once an evening, when i'm
involved in a thread.

btw, i notice i forgot to include a quantity field in tblOrderDetails -
oops! you'll want to include whatever fields in tblOrderDetails that you
need to describe a specific order detail. and if you find order details
confusing, it may help to think of them as line items on invoices (or sales
order) - one record equals one line item on one invoice/sales order.

hth

 

[tina]

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], Employee.[Anniversary Date],
Employee.[Job Title] FROM Employee ORDER BY IIf(IsNull([Last
Name]),IIf(IsNull([First Name]),[Job Title],[First Name]),IIf(IsNull([First
Name]),[Last Name],[First Name] & " " & [Last Name]));
And the column count at 5, the widths 0.5";1.6";0.8";1";1". What this shows
is the Employee ID, Employee Name, Anniversary Date, and Job Title. When I
use the combo box it populates only the Employee ID. Is there a way to make
this combo box auto fill others?

In the sub form I have order ID, employee name, Article, Size, quantity,
Gender, department, supplier.
I should change some of that. Any way that's where i'm at.

no problem, i'll be around; i try to check the ngs once an evening, when i'm
involved in a thread.

btw, i notice i forgot to include a quantity field in tblOrderDetails -
oops! you'll want to include whatever fields in tblOrderDetails that you
need to describe a specific order detail. and if you find order details
confusing, it may help to think of them as line items on invoices (or sales
order) - one record equals one line item on one invoice/sales order.

hth


I'll
have and
in it.
i relationship,
but relationships
are data
in create
an Employee
id, of
the

 

[Dale G]

tbluniformOrders; Order ID, Employee ID, Employee Name, Anniversary Date,
Department, Date Ordered, Date Received, Duration, Integrity, 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.

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], Employee.[Anniversary Date],
Employee.[Job Title] FROM Employee ORDER BY IIf(IsNull([Last
Name]),IIf(IsNull([First Name]),[Job Title],[First Name]),IIf(IsNull([First
Name]),[Last Name],[First Name] & " " & [Last Name]));
And the column count at 5, the widths 0.5";1.6";0.8";1";1". What this shows
is the Employee ID, Employee Name, Anniversary Date, and Job Title. When I
use the combo box it populates only the Employee ID. Is there a way to make
this combo box auto fill others?

In the sub form I have order ID, employee name, Article, Size, quantity,
Gender, department, supplier.
I should change some of that. Any way that's where i'm at.

no problem, i'll be around; i try to check the ngs once an evening, when i'm
involved in a thread.

btw, i notice i forgot to include a quantity field in tblOrderDetails -
oops! you'll want to include whatever fields in tblOrderDetails that you
need to describe a specific order detail. and if you find order details
confusing, it may help to think of them as line items on invoices (or sales
order) - one record equals one line item on one invoice/sales order.

hth


Thank you, I'll give that a try, it may take a while. But I'm sure I'll
have
more questions.

:

i want to expand on my statement posted elsewhere in this thread.

yes. don't try basing an Orders data entry form on a multi-table
query.
you
don't need it, and there's a good change it wouldn't be updateable
after
you'd written it.

a multi-table query is often very useful for reports, and for forms
where
data is going to be *reviewed* rather than added/edited. and yes,
*sometimes* in forms where data will be edited, as well. but it's a
common
mistake of new users to try to use a single form to add/edit records in
multiple tables, just because the data in those tables are related - and
in
most cases, it's just not appropriate, or even the easiest way to do it.
i
would be most likely to use a multi-table query as a data entry form's
RecordSource when the tables involved have a one-to-one relationship,
but
that's not a common relationship. the majority of table relationships
are
one-to-many; and while i don't rule it out entirely, i really can't
think of
an example where i'd use a multi-table query for a form to edit the data
in
tables with a one-to-many relationship.

hth


I'm trying to create a data base for ordering employee uniforms at my
place
of employment.
So far I've created the following tables, Employee, department,
Gender,
Article, supplier.
I'm stuck on how to set up a table for uniform orders, and then
combine
that
with my Employees table, in a Query. From there I would like to create
an
order form using that query. Any ideas?
My employees table has the following fields, ID autonumber, Employee
id,
Last name, first name, anniversary date, Job title, home phone, cell
phone,
address, city, state, zip code, e-mail.
I also have a query Employee Extended, this is Similar to the example
(Northwind).
I can't find any examples to follow. If possible I would like part of
the
form, the employee part, to Auto fill.
I'm thinking the form should have employee Info, info about the
article
ordered, date ordered, date received, duration between ordered &
received,
cost, & integrity of received Order.
Any suggestion will be appreciated.

 

[John W. Vinson]

tbluniformOrders; Order ID, Employee ID, Employee Name, Anniversary Date,
Department, Date Ordered, Date Received, Duration, Integrity, 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.

YOu should cretainly also remove Employee Name from both tables. That name
should exist in the Employee table, only; all you need in any of the orders
tables is the employee ID.

 

[tina]

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], Employee.[Anniversary Date],
Employee.[Job Title] FROM Employee ORDER BY IIf(IsNull([Last
Name]),IIf(IsNull([First Name]),[Job Title],[First Name]),IIf(IsNull([First
Name]),[Last Name],[First Name] & " " & [Last Name]));
And the column count at 5, the widths 0.5";1.6";0.8";1";1". What this shows
is the Employee ID, Employee Name, Anniversary Date, and Job Title.

When
I

use the combo box it populates only the Employee ID. Is there a way

to
make

this combo box auto fill others?

In the sub form I have order ID, employee name, Article, Size, quantity,
Gender, department, supplier.
I should change some of that. Any way that's where i'm at.


:

no problem, i'll be around; i try to check the ngs once an evening,

when
i'm

involved in a thread.

btw, i notice i forgot to include a quantity field in tblOrderDetails -
oops! you'll want to include whatever fields in tblOrderDetails that you
need to describe a specific order detail. and if you find order details
confusing, it may help to think of them as line items on invoices

(or
sales

order) - one record equals one line item on one invoice/sales order.

hth


Thank you, I'll give that a try, it may take a while. But I'm sure I'll
have
more questions.

:

i want to expand on my statement posted elsewhere in this thread.

yes. don't try basing an Orders data entry form on a multi-table
query.
you
don't need it, and there's a good change it wouldn't be updateable
after
you'd written it.

a multi-table query is often very useful for reports, and for forms
where
data is going to be *reviewed* rather than added/edited. and yes,
*sometimes* in forms where data will be edited, as well. but it's a
common
mistake of new users to try to use a single form to add/edit

records
in

multiple tables, just because the data in those tables are

related -
and

in
most cases, it's just not appropriate, or even the easiest way

to do
it.

i
would be most likely to use a multi-table query as a data entry form's
RecordSource when the tables involved have a one-to-one relationship,
but
that's not a common relationship. the majority of table relationships
are
one-to-many; and while i don't rule it out entirely, i really can't
think of
an example where i'd use a multi-table query for a form to edit

the
data

in
tables with a one-to-many relationship.

hth


I'm trying to create a data base for ordering employee

uniforms at
my

place
of employment.
So far I've created the following tables, Employee, department,
Gender,
Article, supplier.
I'm stuck on how to set up a table for uniform orders, and then
combine
that
with my Employees table, in a Query. From there I would like

to
create

an
order form using that query. Any ideas?
My employees table has the following fields, ID autonumber, Employee
id,
Last name, first name, anniversary date, Job title, home

phone,
cell

phone,
address, city, state, zip code, e-mail.
I also have a query Employee Extended, this is Similar to the example
(Northwind).
I can't find any examples to follow. If possible I would like

part
of

the
form, the employee part, to Auto fill.
I'm thinking the form should have employee Info, info about the
article
ordered, date ordered, date received, duration between ordered &
received,
cost, & integrity of received Order.
Any suggestion will be appreciated.

 

[Dale G]

I took out the Auto ID that access applies, is that ok?

 

[Dale G]

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], Employee.[Anniversary
Date],
Employee.[Job Title] FROM Employee ORDER BY IIf(IsNull([Last
Name]),IIf(IsNull([First Name]),[Job Title],[First
Name]),IIf(IsNull([First
Name]),[Last Name],[First Name] & " " & [Last Name]));
And the column count at 5, the widths 0.5";1.6";0.8";1";1". What this
shows
is the Employee ID, Employee Name, Anniversary Date, and Job Title. When
I
use the combo box it populates only the Employee ID. Is there a way to
make
this combo box auto fill others?

In the sub form I have order ID, employee name, Article, Size, quantity,
Gender, department, supplier.
I should change some of that. Any way that's where i'm at.


:

no problem, i'll be around; i try to check the ngs once an evening, when
i'm
involved in a thread.

btw, i notice i forgot to include a quantity field in tblOrderDetails -
oops! you'll want to include whatever fields in tblOrderDetails that you
need to describe a specific order detail. and if you find order details
confusing, it may help to think of them as line items on invoices (or
sales
order) - one record equals one line item on one invoice/sales order.

hth


Thank you, I'll give that a try, it may take a while. But I'm sure
I'll
have
more questions.

:

i want to expand on my statement posted elsewhere in this thread.

yes. don't try basing an Orders data entry form on a multi-table
query.
you
don't need it, and there's a good change it wouldn't be updateable
after
you'd written it.

a multi-table query is often very useful for reports, and for forms
where
data is going to be *reviewed* rather than added/edited. and yes,
*sometimes* in forms where data will be edited, as well. but it's a
common
mistake of new users to try to use a single form to add/edit records
in
multiple tables, just because the data in those tables are related -
and
in
most cases, it's just not appropriate, or even the easiest way to do
it.
i
would be most likely to use a multi-table query as a data entry
form's
RecordSource when the tables involved have a one-to-one
relationship,
but
that's not a common relationship. the majority of table
relationships
are
one-to-many; and while i don't rule it out entirely, i really can't
think of
an example where i'd use a multi-table query for a form to edit the
data
in
tables with a one-to-many relationship.

hth


I'm trying to create a data base for ordering employee uniforms at
my
place
of employment.
So far I've created the following tables, Employee, department,
Gender,
Article, supplier.
I'm stuck on how to set up a table for uniform orders, and then
combine
that
with my Employees table, in a Query. From there I would like to
create
an
order form using that query. Any ideas?
My employees table has the following fields, ID autonumber,
Employee
id,
Last name, first name, anniversary date, Job title, home phone,
cell
phone,
address, city, state, zip code, e-mail.
I also have a query Employee Extended, this is Similar to the
example
(Northwind).
I can't find any examples to follow. If possible I would like part
of
the
form, the employee part, to Auto fill.
I'm thinking the form should have employee Info, info about the
article
ordered, date ordered, date received, duration between ordered &
received,
cost, & integrity of received Order.
Any suggestion will be appreciated.

 

[John W. Vinson]

I took out the Auto ID that access applies, is that ok?

I'm sorry, Dale, I don't understand. If the employee name is a lookup field,
listen to Tina... Lookup Fields are EEEVil.

 

[tina]

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], Employee.[Anniversary
Date],
Employee.[Job Title] FROM Employee ORDER BY IIf(IsNull([Last
Name]),IIf(IsNull([First Name]),[Job Title],[First
Name]),IIf(IsNull([First
Name]),[Last Name],[First Name] & " " & [Last Name]));
And the column count at 5, the widths 0.5";1.6";0.8";1";1". What this
shows
is the Employee ID, Employee Name, Anniversary Date, and Job

Title.
When

I
use the combo box it populates only the Employee ID. Is there a

way
to

make
this combo box auto fill others?

In the sub form I have order ID, employee name, Article, Size, quantity,
Gender, department, supplier.
I should change some of that. Any way that's where i'm at.


:

no problem, i'll be around; i try to check the ngs once an

evening,
when

i'm
involved in a thread.

btw, i notice i forgot to include a quantity field in tblOrderDetails -
oops! you'll want to include whatever fields in tblOrderDetails

that
you

need to describe a specific order detail. and if you find order details
confusing, it may help to think of them as line items on

invoices
(or

sales
order) - one record equals one line item on one invoice/sales

order.

hth


Thank you, I'll give that a try, it may take a while. But I'm sure
I'll
have
more questions.

:

i want to expand on my statement posted elsewhere in this thread.

yes. don't try basing an Orders data entry form on a multi-table
query.
you
don't need it, and there's a good change it wouldn't be updateable
after
you'd written it.

a multi-table query is often very useful for reports, and

for
forms

where
data is going to be *reviewed* rather than added/edited. and yes,
*sometimes* in forms where data will be edited, as well. but it's a
common
mistake of new users to try to use a single form to add/edit records
in
multiple tables, just because the data in those tables are related -
and
in
most cases, it's just not appropriate, or even the easiest

way
to do

it.
i
would be most likely to use a multi-table query as a data entry
form's
RecordSource when the tables involved have a one-to-one
relationship,
but
that's not a common relationship. the majority of table
relationships
are
one-to-many; and while i don't rule it out entirely, i

really
can't

think of
an example where i'd use a multi-table query for a form to

edit
the

data
in
tables with a one-to-many relationship.

hth


I'm trying to create a data base for ordering employee uniforms at
my
place
of employment.
So far I've created the following tables, Employee, department,
Gender,
Article, supplier.
I'm stuck on how to set up a table for uniform orders, and then
combine
that
with my Employees table, in a Query. From there I would

like
to

create
an
order form using that query. Any ideas?
My employees table has the following fields, ID autonumber,
Employee
id,
Last name, first name, anniversary date, Job title, home phone,
cell
phone,
address, city, state, zip code, e-mail.
I also have a query Employee Extended, this is Similar to the
example
(Northwind).
I can't find any examples to follow. If possible I would

like
part

of
the
form, the employee part, to Auto fill.
I'm thinking the form should have employee Info, info

about
the

article
ordered, date ordered, date received, duration between

ordered
&

received,
cost, & integrity of received Order.
Any suggestion will be appreciated.

 

[Dale G]

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], Employee.[Anniversary
Date],
Employee.[Job Title] FROM Employee ORDER BY IIf(IsNull([Last
Name]),IIf(IsNull([First Name]),[Job Title],[First
Name]),IIf(IsNull([First
Name]),[Last Name],[First Name] & " " & [Last Name]));
And the column count at 5, the widths 0.5";1.6";0.8";1";1". What this
shows
is the Employee ID, Employee Name, Anniversary Date, and Job Title.
When
I
use the combo box it populates only the Employee ID. Is there a way
to
make
this combo box auto fill others?

In the sub form I have order ID, employee name, Article, Size,
quantity,
Gender, department, supplier.
I should change some of that. Any way that's where i'm at.


:

no problem, i'll be around; i try to check the ngs once an evening,
when
i'm
involved in a thread.

btw, i notice i forgot to include a quantity field in
tblOrderDetails -
oops! you'll want to include whatever fields in tblOrderDetails that
you
need to describe a specific order detail. and if you find order
details
confusing, it may help to think of them as line items on invoices
(or
sales
order) - one record equals one line item on one invoice/sales order.


hth


Thank you, I'll give that a try, it may take a while. But I'm sure
I'll
have
more questions.

:

i want to expand on my statement posted elsewhere in this
thread.

yes. don't try basing an Orders data entry form on a
multi-table
query.
you
don't need it, and there's a good change it wouldn't be
updateable
after
you'd written it.

a multi-table query is often very useful for reports, and for
forms
where
data is going to be *reviewed* rather than added/edited. and
yes,
*sometimes* in forms where data will be edited, as well. but
it's a
common
mistake of new users to try to use a single form to add/edit
records
in
multiple tables, just because the data in those tables are
related -
and
in
most cases, it's just not appropriate, or even the easiest way
to do
it.
i
would be most likely to use a multi-table query as a data entry
form's
RecordSource when the tables involved have a one-to-one
relationship,
but
that's not a common relationship. the majority of table
relationships
are
one-to-many; and while i don't rule it out entirely, i really
can't
think of
an example where i'd use a multi-table query for a form to edit
the
data
in
tables with a one-to-many relationship.

hth


"Dale G" <[email protected]> wrote in message

 

[tina]

comments inline.

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

not as much as you think you do, hon, really.

It's getting the info from the table that seems to be the hard part.

no, it's really not. the hardest part is designing the tables/relationships
correctly - and when you do, it's much easier to use the Access tool to get
the data out of the tables, because Access is geared to work within the
principles of relational design.

your help is appreciated, thank you.

i know you mean that, and you're welcome. at this point the best help i have
to offer you is to reiterate my advice for you to read up/more on relational
design principles; when you understand them better, you'll be able to
analyze your process and see where the EmployeeID foreign key field belongs.

hth

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 frustra ting
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], Employee.[Anniversary
Date],
Employee.[Job Title] FROM Employee ORDER BY IIf(IsNull([Last
Name]),IIf(IsNull([First Name]),[Job Title],[First
Name]),IIf(IsNull([First
Name]),[Last Name],[First Name] & " " & [Last Name]));
And the column count at 5, the widths 0.5";1.6";0.8";1";1".

What
this

shows
is the Employee ID, Employee Name, Anniversary Date, and Job Title.
When
I
use the combo box it populates only the Employee ID. Is there

a
way

to
make
this combo box auto fill others?

In the sub form I have order ID, employee name, Article, Size,
quantity,
Gender, department, supplier.
I should change some of that. Any way that's where i'm at.


:

no problem, i'll be around; i try to check the ngs once an evening,
when
i'm
involved in a thread.

btw, i notice i forgot to include a quantity field in
tblOrderDetails -
oops! you'll want to include whatever fields in

tblOrderDetails
that

you
need to describe a specific order detail. and if you find order
details
confusing, it may help to think of them as line items on invoices
(or
sales
order) - one record equals one line item on one

invoice/sales
order.

hth


Thank you, I'll give that a try, it may take a while. But

I'm
sure

I'll
have
more questions.

:

i want to expand on my statement posted elsewhere in this
thread.

yes. don't try basing an Orders data entry form on a
multi-table
query.
you
don't need it, and there's a good change it wouldn't be
updateable
after
you'd written it.

a multi-table query is often very useful for reports,

and
for

forms
where
data is going to be *reviewed* rather than added/edited. and
yes,
*sometimes* in forms where data will be edited, as well. but
it's a
common
mistake of new users to try to use a single form to add/edit
records
in
multiple tables, just because the data in those tables are
related -
and
in
most cases, it's just not appropriate, or even the

easiest
way

to do
it.
i
would be most likely to use a multi-table query as a

data
entry

form's
RecordSource when the tables involved have a one-to-one
relationship,
but
that's not a common relationship. the majority of table
relationships
are
one-to-many; and while i don't rule it out entirely, i really
can't
think of
an example where i'd use a multi-table query for a form

to
edit

the
data
in
tables with a one-to-many relationship.

hth


"Dale G" <[email protected]> wrote in

message

 

[Beetle]

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], Employee.[Anniversary
Date],
Employee.[Job Title] FROM Employee ORDER BY IIf(IsNull([Last
Name]),IIf(IsNull([First Name]),[Job Title],[First
Name]),IIf(IsNull([First
Name]),[Last Name],[First Name] & " " & [Last Name]));
And the column count at 5, the widths 0.5";1.6";0.8";1";1". What this
shows
is the Employee ID, Employee Name, Anniversary Date, and Job Title.
When
I
use the combo box it populates only the Employee ID. Is there a way
to
make
this combo box auto fill others?

In the sub form I have order ID, employee name, Article, Size,
quantity,
Gender, department, supplier.
I should change some of that. Any way that's where i'm at.


:

no problem, i'll be around; i try to check the ngs once an evening,
when
i'm
involved in a thread.

btw, i notice i forgot to include a quantity field in
tblOrderDetails -
oops! you'll want to include whatever fields in tblOrderDetails that
you
need to describe a specific order detail. and if you find order
details
confusing, it may help to think of them as line items on invoices
(or
sales
order) - one record equals one line item on one invoice/sales order.


hth


Thank you, I'll give that a try, it may take a while. But I'm sure
I'll
have
more questions.

:

i want to expand on my statement posted elsewhere in this
thread.

yes. don't try basing an Orders data entry form on a
multi-table
query.
you
don't need it, and there's a good change it wouldn't be
updateable
after
you'd written it.

a multi-table query is often very useful for reports, and for
forms
where
data is going to be *reviewed* rather than added/edited. and
yes,
*sometimes* in forms where data will be edited, as well. but
it's a
common
mistake of new users to try to use a single form to add/edit
records
in
multiple tables, just because the data in those tables are
related -
and
in
most cases, it's just not appropriate, or even the easiest way
to do
it.
i
would be most likely to use a multi-table query as a data entry
form's
RecordSource when the tables involved have a one-to-one
relationship,

 

[Dale G]

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?

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],
Employee.[Anniversary
Date],
Employee.[Job Title] FROM Employee ORDER BY IIf(IsNull([Last
Name]),IIf(IsNull([First Name]),[Job Title],[First
Name]),IIf(IsNull([First
Name]),[Last Name],[First Name] & " " & [Last Name]));
And the column count at 5, the widths 0.5";1.6";0.8";1";1". What
this
shows
is the Employee ID, Employee Name, Anniversary Date, and Job
Title.
When
I
use the combo box it populates only the Employee ID. Is there a
way
to
make
this combo box auto fill others?

In the sub form I have order ID, employee name, Article, Size,
quantity,
Gender, department, supplier.
I should change some of that. Any way that's where i'm at.


:

no problem, i'll be around; i try to check the ngs once an
evening,
when
i'm
involved in a thread.

btw, i notice i forgot to include a quantity field in
tblOrderDetails -
oops! you'll want to include whatever fields in tblOrderDetails
that
you
need to describe a specific order detail. and if you find order
details

 

[Dale G]

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?

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?

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],
Employee.[Anniversary
Date],
Employee.[Job Title] FROM Employee ORDER BY IIf(IsNull([Last
Name]),IIf(IsNull([First Name]),[Job Title],[First
Name]),IIf(IsNull([First
Name]),[Last Name],[First Name] & " " & [Last Name]));
And the column count at 5, the widths 0.5";1.6";0.8";1";1". What
this
shows
is the Employee ID, Employee Name, Anniversary Date, and Job