Looking for a design for an order type database...

[Sapper]

Hi all,
Here's my thing(?) I am the middle man in an order and supply chain.
Every two weeks I order from contractors, people to perform work for my
customers. Every Contractor can supply all of the same type of people
(craftsmen) to all of my customers, who use all of the types of people in
various areas.
Now I know that the tables I need are:
Contractors (all the usual "Supplier" fields - 30 Contractors);
Craftsmen(only one field > Product - 10 Crafts);
Customers (only two fields > FirstName / LastName - 10 Customers);
Areas (one field > ReportingPoint - 10 Areas);
SD Date (one field > SD_Date, these are dates that are set at the begining
of the year - 26 Dates);
and of course Orders (fields from all the above).
I know the basic layout, but am having a lot of problems in how there are
linked for Relationships, they are all related through the S/D Date, as each
S/D date is one order for all of the Contractors and Customers alike. I
thought I had it all set out, but after using it for a short period the
integrity has faulted and the reports show wrong data, some of the
requirements (Crafts) are shown on the report for the wrong dates, the
report is from a S/D Date Query. I think my relationships are messed up,
Help? TIA, Colin D.

 

[tina]

your table design is not entirely clear to me. you listed a table called
"Craftsmen", but you don't list people in it - you list products. so i'm
guessing that the table is really a list of "Crafts" or "CraftCategories"
(carpentry, electrical, etc - i'm thinking construction, though i realize
your business may deal with a different field entirely), not specific people
as identifiable individuals. example: electricians - not Joe Smith,
electrician.

going with my assumptions about table Craftsmen above, i'd say your main
"entity" relationship is a one-to-many relationship between Customers and
Orders: one customer may have many orders, but each order belongs to a
specific customer. if a single Order may include more than one Craftsman
category, then you need an OrderDetails table which has a many-to-one
relationship with the Orders table. the rest of your tables appear to be
"supporting" tables that list appropriate values that may be used in the
data fields in the Orders and OrderDetails tables.

if you're following me so far, and assuming that an OrderDetails table is
appropriate, the relationships would be:

Customers 1:n Orders
Orders 1:n OrderDetails
Craftsmen 1:n OrderDetails
SD_Date 1:n Orders
(recommend you don't allow spaces in your table names, and don't give a
field exactly the same name as a table.)
Areas 1:n Orders
(this assumes that all the craftsmen used in one order report to the same
area. if, instead, different craftsmen in the same order may report to
different areas, then the relationship would be
Areas 1:n OrderDetails)
Contractors 1:n Orders
(again, this assumes that all the craftsmen used in one order are supplied
by the same contractor. if, instead, different craftsmen in the same order
may come from different contractors, then the relationship would be
Contractors 1:n OrderDetails)

hth

 

[Sapper]

Sorry, it is a little fuzzy to me sometimes, but this may help clarify it
for you at each section below:

your table design is not entirely clear to me. you listed a table called
"Craftsmen", but you don't list people in it - you list products. so i'm
guessing that the table is really a list of "Crafts" or "CraftCategories"
(carpentry, electrical, etc - i'm thinking construction, though i realize
your business may deal with a different field entirely), not specific
people
as identifiable individuals. example: electricians - not Joe Smith,
electrician.

True my "Products" are Crafts (or Tradesmen) not names.

going with my assumptions about table Craftsmen above, i'd say your main
"entity" relationship is a one-to-many relationship between Customers and
Orders: one customer may have many orders, but each order belongs to a
specific customer. if a single Order may include more than one Craftsman
category, then you need an OrderDetails table which has a many-to-one
relationship with the Orders table. the rest of your tables appear to be
"supporting" tables that list appropriate values that may be used in the
data fields in the Orders and OrderDetails tables.

This is were it gets fuzzy, each Order has many Customers and Contractors
(Suppliers).
For instance:
Contractors may supply:
to Customers
Smith Inc; 1 x Millwright; 1 Welder; 4 Electricians; 2
Labourers - Woody; Blender and Finishing
Helpers Co; 2 x Millwrights; 2 Welders; 2 Electricians; 3
bourers - Blender; Finishing and Warehouse
Fabricators Ltd, 2 Millwrights; 3 Welders, 10 Electricians 25
bourers - Woody; Forming and Finishing

Each Order may include several different Crafts from several different
Contractors for several different Customers. So no doubt I do need an Orders
Detail table, I'm not sure how it would fit into the overall scheme?

if you're following me so far, and assuming that an OrderDetails table is
appropriate, the relationships would be:

Customers 1:n Orders
Orders 1:n OrderDetails
Craftsmen 1:n OrderDetails
SD_Date 1:n Orders
(recommend you don't allow spaces in your table names, and don't give a
field exactly the same name as a table.) Agreed.

Areas 1:n Orders
(this assumes that all the craftsmen used in one order report to the same
area. if, instead, different craftsmen in the same order may report to
different areas, then the relationship would be
Areas 1:n OrderDetails)
Contractors 1:n Orders
(again, this assumes that all the craftsmen used in one order are supplied
by the same contractor. if, instead, different craftsmen in the same order
may come from different contractors, then the relationship would be
Contractors 1:n OrderDetails)

As I said above: Each Order may include several different Crafts from
several different Contractors for several different Customers. I'm the
middleman between Supplier and Customer, I need to know what number of
Craftsmen the Customer requires, so I can create and Order for my
Contractors (Suppliers), all for each specific date.
So each row of the "Input" table would require: a Supplier, a Craft; # of
Crafts required, and the Customers name (to report to), and each Order would
consist of up to 25 rows and all belong to one Date.
So what you've replied with makes more sense now.

 

[tina]

well, maybe i'm on the wrong wavelength, but it seems to me there should be
some data element that defines a single Order - besides the date. (after
all, you built your database based on the tables/relationships you posted,
and have found that it's not working for you.) i could be all wet, but
without a better understanding of the flow of your business process, i don't
know.

perhaps the bottom line is "who's paying for, or making the request for,
your services as middleman?" if it's the Customers, then i think the table
relationships i posted are probably appropriate. if it's the Contractors,
then you can pretty much just switch out Customers/Contractors tables in the
relationships i posted.

don't get hung up on the idea that everything done on one date has to be
considered a single Order. as long as each Order record contains a date from
your SD_Date table, you can pull together all orders on a given date
whenever you need to, by using a query.

hth

 

[Sapper]

Maybe I'm trying to do a complex thing, when all I need to do is create the
various tables:
Contractors; Crafts; Customers; etc., then create an input form using the
various tables as combo boxes to create an Orders table, this table would be
related to the SD_Date table only. Each row of the Orders table would be
part of an order. i.e. SD_DateID > OrdersID (one to many).
Orders table would have fields:
1-SD_DateID
2-OrdersID
3-Contractors > ContractorsID
4-Customers > CustomersID
5-Crafts > CraftsID
6-NumberRequired

Does this sound too simple? Or is it not a workable thing?

Business flow? Well, I'm the Maintenance Planner and each 14 days period I
collect and compile a listing of Carftsmen required by four Supervisors to
perform work on various machinery throughout the plant during a
"Turn-around". These Craftsmen are rewquested from up to 25 different
Contractors, each contractor is able to supply all the craftsmen types
required, but no one Contractor can supply all of our requirements (140 to
175 Craftsmen). Prior to each Turn-around I send (fax) a listing of what
Craftsmen I require (a specific number of each) to different Contractors, at
the same time the fax lets them know who the Craftsmen must report to (my
Customers). I am employed by the Customers company.

I hope this hasn't confused the issue.

Colin D.

well, maybe i'm on the wrong wavelength, but it seems to me there should
be
some data element that defines a single Order - besides the date. (after
all, you built your database based on the tables/relationships you posted,
and have found that it's not working for you.) i could be all wet, but
without a better understanding of the flow of your business process, i
don't
know.

perhaps the bottom line is "who's paying for, or making the request for,
your services as middleman?" if it's the Customers, then i think the table
relationships i posted are probably appropriate. if it's the Contractors,
then you can pretty much just switch out Customers/Contractors tables in
the
relationships i posted.

don't get hung up on the idea that everything done on one date has to be
considered a single Order. as long as each Order record contains a date
from
your SD_Date table, you can pull together all orders on a given date
whenever you need to, by using a query.

hth

<SNIP>

 

[tina]

Colin, your explanation of your business process goes a long way in helping
me to understand what your data tracking needs are. if you'll hang with this
thread another day or so, i'll see if i can come up with a
tables/relationships design that may work for you.

two questions: do you want to produce each Contractor's "list to be faxed"
in the database? and do you provide data to someone in your company so that
the contractors will get paid?

 

[Sapper]

Yes, I do require a one page per Contractor report that gets faxed to each
Contractor.
And no each Contractor invoices through me for payment by our accounts dept.
I'm glad you can see more of what I mean, anything you come up with must be
better than what I've been toying with.
Colin D.

 

[tina]

Colin, please bear with me. i haven't abandoned you or this thread - had a
minor emergency to handle today, which blew my schedule out of the water. i
promise i'll post tomorrow Thurs 7/21 for sure. either a suggested solution,
or a white flag - but i won't leave you hanging.

tina

 

[tina]

ok Colin, here's what i came up with. if i were designing this db for you,
in a professional relationship, i would have asked numerous additional
questions, to ensure that the design fully supports your process. since
that's not appropriate in this forum, i made some assumptions and built from
there:

each Area of the plant is "supervised" by only one Supervisor (your
Customer).
each Supervisor is in charge of more than one Area.
(this is not really vital to the tables design as a whole; it simply allows
more specific support of certain aspects of data entry, for the user.)
each Area may require maintenance using more than one Craft, in any single
Order.
the need for "x" number of Craftsment in a specific Area in a specific
Order, may be met by more than one Contractor (example: need 20
electricians in area B for order 7/29/05. contractor Charlie Co provide 15
of them, and contractor Tango Co provides the other 5.)
each Supervisor contacts you (Colin) with a request for x number of x
craftsmen for x areas on x SD_Date. the supervisor may make all requests for
an SD_Date at once, or "dribble" them in over a period of time.
you (Colin) decide how many craftsmen to request from each contractor, to
fill each supervisor's order for a particular SD_Date.

note: i used the term Supervisors in my model, in place of Customers.

based on the above, here's my suggested table/relationship design, as

tblSupervisors
SupervisorID (pk)
FirstName
LastName

tblAreas
AreaID (pk)
AreaName
fkSupervisorID (fk from tblSupervisors)
(linking each area to a specific supervisor, allows the Areas droplist in a
data entry form to be filtered by the supervisor associated with the current
record. if it's not appropriate, just delete the fk field.)

tblContractors
ContractorID (pk)
CompanyName

tblCraftsmen
CraftID (pk)
CraftName

tblSD_Dates
SDDate (pk)

tblOrders
OrderID (pk)
fkSupervisorID (fk from tblSupervisors)
fkSDDate (fk from tblSD_Dates)

tblOrderDetails
DetailID (pk)
fkOrderID (foreign key from tblOrders)
fkAreaID (foreign key from tblAreas)
fkCraftID (foreign key from tblCraftsmen)
NumberRequired

tblOrderDetailContractors
DetConID (pk)
fkDetailID (foreign key from tblOrderDetails
fkContractorID (foreign key from tblContractors)
NumberSupplied (or NumberRequested)

tblSupervisors 1:n tblAreas
tblSupervisors 1:n tblOrders
tblSD_Dates 1:n tblOrders

tblOrders 1:n tblOrderDetails
tblAreas 1:n tblOrderDetails
tblCraftsment 1:n tblOrderDetails

tblOrderDetails 1:n tblOrderDetailContractors
tblContractors 1:n tblOrderDetailContractors

to generat a list of requested craftsmen to fax to each of your contractors,
the following query works, as

SELECT tblOrders.fkSDDate, tblContractors.CompanyName,
tblOrderDetailContractors.NumberSupplied, tblCraftsmen.CraftName,
tblAreas.AreaName, [FirstName] & " " & [LastName] AS SupvrName
FROM ((((tblOrderDetails LEFT JOIN tblOrders ON tblOrderDetails.fkOrderID =
tblOrders.OrderID) LEFT JOIN tblAreas ON tblOrderDetails.fkAreaID =
tblAreas.AreaID) LEFT JOIN tblCraftsmen ON tblOrderDetails.fkCraftID =
tblCraftsmen.CraftID) LEFT JOIN tblSupervisors ON tblOrders.fkSupervisorID =
tblSupervisors.SupervisorID) LEFT JOIN (tblOrderDetailContractors LEFT JOIN
tblContractors ON tblOrderDetailContractors.fkContractorID =
tblContractors.ContractorID) ON tblOrderDetails.DetailID =
tblOrderDetailContractors.fkDetailID
WHERE (((tblOrders.fkSDDate)=#7/29/2005#));

all you have to do is group the records by ContractorID, in the report
design, and set a page break in the ContractorID Footer section.

hopefully the above will give you a least a starting point to make heads or
tails of your process. sorry it took me so long to work something up!

hth

 

[Sapper]

In a nutshell, WOW! I have still to really digest it all, but WOW, thanks
from what I've looked at, it is all and more than I expected.
It's true what some of my fellow learners have said about this ng, the help
to us novices is unbelievable and absolutely exceptional.
I shall leave this thread for now and take some time (probably several
weeks) to get to "know" this layout you've provided.
Tina - Thx, Colin D.

ok Colin, here's what i came up with. if i were designing this db for you,
in a professional relationship, i would have asked numerous additional
questions, to ensure that the design fully supports your process. since
that's not appropriate in this forum, i made some assumptions and built
from
there:

each Area of the plant is "supervised" by only one Supervisor (your
Customer).
each Supervisor is in charge of more than one Area.
(this is not really vital to the tables design as a whole; it simply
allows
more specific support of certain aspects of data entry, for the user.)
each Area may require maintenance using more than one Craft, in any single
Order.
the need for "x" number of Craftsment in a specific Area in a specific
Order, may be met by more than one Contractor (example: need 20
electricians in area B for order 7/29/05. contractor Charlie Co provide 15
of them, and contractor Tango Co provides the other 5.)
each Supervisor contacts you (Colin) with a request for x number of x
craftsmen for x areas on x SD_Date. the supervisor may make all requests
for
an SD_Date at once, or "dribble" them in over a period of time.
you (Colin) decide how many craftsmen to request from each contractor, to
fill each supervisor's order for a particular SD_Date.

note: i used the term Supervisors in my model, in place of Customers.

based on the above, here's my suggested table/relationship design, as

tblSupervisors
SupervisorID (pk)
FirstName
LastName

tblAreas
AreaID (pk)
AreaName
fkSupervisorID (fk from tblSupervisors)
(linking each area to a specific supervisor, allows the Areas droplist in
a
data entry form to be filtered by the supervisor associated with the
current
record. if it's not appropriate, just delete the fk field.)

tblContractors
ContractorID (pk)
CompanyName

tblCraftsmen
CraftID (pk)
CraftName

tblSD_Dates
SDDate (pk)

tblOrders
OrderID (pk)
fkSupervisorID (fk from tblSupervisors)
fkSDDate (fk from tblSD_Dates)

tblOrderDetails
DetailID (pk)
fkOrderID (foreign key from tblOrders)
fkAreaID (foreign key from tblAreas)
fkCraftID (foreign key from tblCraftsmen)
NumberRequired

tblOrderDetailContractors
DetConID (pk)
fkDetailID (foreign key from tblOrderDetails
fkContractorID (foreign key from tblContractors)
NumberSupplied (or NumberRequested)

tblSupervisors 1:n tblAreas
tblSupervisors 1:n tblOrders
tblSD_Dates 1:n tblOrders

tblOrders 1:n tblOrderDetails
tblAreas 1:n tblOrderDetails
tblCraftsment 1:n tblOrderDetails

tblOrderDetails 1:n tblOrderDetailContractors
tblContractors 1:n tblOrderDetailContractors

to generat a list of requested craftsmen to fax to each of your
contractors,
the following query works, as

SELECT tblOrders.fkSDDate, tblContractors.CompanyName,
tblOrderDetailContractors.NumberSupplied, tblCraftsmen.CraftName,
tblAreas.AreaName, [FirstName] & " " & [LastName] AS SupvrName
FROM ((((tblOrderDetails LEFT JOIN tblOrders ON tblOrderDetails.fkOrderID
=
tblOrders.OrderID) LEFT JOIN tblAreas ON tblOrderDetails.fkAreaID =
tblAreas.AreaID) LEFT JOIN tblCraftsmen ON tblOrderDetails.fkCraftID =
tblCraftsmen.CraftID) LEFT JOIN tblSupervisors ON tblOrders.fkSupervisorID
=
tblSupervisors.SupervisorID) LEFT JOIN (tblOrderDetailContractors LEFT
JOIN
tblContractors ON tblOrderDetailContractors.fkContractorID =
tblContractors.ContractorID) ON tblOrderDetails.DetailID =
tblOrderDetailContractors.fkDetailID
WHERE (((tblOrders.fkSDDate)=#7/29/2005#));

all you have to do is group the records by ContractorID, in the report
design, and set a page break in the ContractorID Footer section.

hopefully the above will give you a least a starting point to make heads
or
tails of your process. sorry it took me so long to work something up!

hth

Yes, I do require a one page per Contractor report that gets faxed to
each
Contractor.
And no each Contractor invoices through me for payment by our accounts dept.
I'm glad you can see more of what I mean, anything you come up with must be
better than what I've been toying with.
Colin D.

 

[tina]

well, you're very welcome Colin; hope it turns out to be a workable solution
for you, or at least gets you on the road to one.
btw, i created the posted design in a db, with a quick-and-dirty data entry
form/subform setup, so i could test the relationships, data entry, and
query. you're welcome to see the db, just post back to this thread if you
want it, and i'll email it to you (it's an A2000 db, can be converted to
A97, or A2002-03). i'll be watching the thread for several days at least,
but probably not several weeks.

In a nutshell, WOW! I have still to really digest it all, but WOW, thanks
from what I've looked at, it is all and more than I expected.
It's true what some of my fellow learners have said about this ng, the help
to us novices is unbelievable and absolutely exceptional.
I shall leave this thread for now and take some time (probably several
weeks) to get to "know" this layout you've provided.
Tina - Thx, Colin D.

ok Colin, here's what i came up with. if i were designing this db for you,
in a professional relationship, i would have asked numerous additional
questions, to ensure that the design fully supports your process. since
that's not appropriate in this forum, i made some assumptions and built
from
there:

each Area of the plant is "supervised" by only one Supervisor (your
Customer).
each Supervisor is in charge of more than one Area.
(this is not really vital to the tables design as a whole; it simply
allows
more specific support of certain aspects of data entry, for the user.)
each Area may require maintenance using more than one Craft, in any single
Order.
the need for "x" number of Craftsment in a specific Area in a specific
Order, may be met by more than one Contractor (example: need 20
electricians in area B for order 7/29/05. contractor Charlie Co provide 15
of them, and contractor Tango Co provides the other 5.)
each Supervisor contacts you (Colin) with a request for x number of x
craftsmen for x areas on x SD_Date. the supervisor may make all requests
for
an SD_Date at once, or "dribble" them in over a period of time.
you (Colin) decide how many craftsmen to request from each contractor, to
fill each supervisor's order for a particular SD_Date.

note: i used the term Supervisors in my model, in place of Customers.

based on the above, here's my suggested table/relationship design, as

tblSupervisors
SupervisorID (pk)
FirstName
LastName

tblAreas
AreaID (pk)
AreaName
fkSupervisorID (fk from tblSupervisors)
(linking each area to a specific supervisor, allows the Areas droplist in
a
data entry form to be filtered by the supervisor associated with the
current
record. if it's not appropriate, just delete the fk field.)

tblContractors
ContractorID (pk)
CompanyName

tblCraftsmen
CraftID (pk)
CraftName

tblSD_Dates
SDDate (pk)

tblOrders
OrderID (pk)
fkSupervisorID (fk from tblSupervisors)
fkSDDate (fk from tblSD_Dates)

tblOrderDetails
DetailID (pk)
fkOrderID (foreign key from tblOrders)
fkAreaID (foreign key from tblAreas)
fkCraftID (foreign key from tblCraftsmen)
NumberRequired

tblOrderDetailContractors
DetConID (pk)
fkDetailID (foreign key from tblOrderDetails
fkContractorID (foreign key from tblContractors)
NumberSupplied (or NumberRequested)

tblSupervisors 1:n tblAreas
tblSupervisors 1:n tblOrders
tblSD_Dates 1:n tblOrders

tblOrders 1:n tblOrderDetails
tblAreas 1:n tblOrderDetails
tblCraftsment 1:n tblOrderDetails

tblOrderDetails 1:n tblOrderDetailContractors
tblContractors 1:n tblOrderDetailContractors

to generat a list of requested craftsmen to fax to each of your
contractors,
the following query works, as

SELECT tblOrders.fkSDDate, tblContractors.CompanyName,
tblOrderDetailContractors.NumberSupplied, tblCraftsmen.CraftName,
tblAreas.AreaName, [FirstName] & " " & [LastName] AS SupvrName
FROM ((((tblOrderDetails LEFT JOIN tblOrders ON tblOrderDetails.fkOrderID
=
tblOrders.OrderID) LEFT JOIN tblAreas ON tblOrderDetails.fkAreaID =
tblAreas.AreaID) LEFT JOIN tblCraftsmen ON tblOrderDetails.fkCraftID =
tblCraftsmen.CraftID) LEFT JOIN tblSupervisors ON tblOrders.fkSupervisorID
=
tblSupervisors.SupervisorID) LEFT JOIN (tblOrderDetailContractors LEFT
JOIN
tblContractors ON tblOrderDetailContractors.fkContractorID =
tblContractors.ContractorID) ON tblOrderDetails.DetailID =
tblOrderDetailContractors.fkDetailID
WHERE (((tblOrders.fkSDDate)=#7/29/2005#));

all you have to do is group the records by ContractorID, in the report
design, and set a page break in the ContractorID Footer section.

hopefully the above will give you a least a starting point to make heads
or
tails of your process. sorry it took me so long to work something up!

hth

Yes, I do require a one page per Contractor report that gets faxed to
each
Contractor.
And no each Contractor invoices through me for payment by our accounts dept.
I'm glad you can see more of what I mean, anything you come up with

must
be

better than what I've been toying with.
Colin D.

Colin, your explanation of your business process goes a long way in
helping
me to understand what your data tracking needs are. if you'll hang with
this
thread another day or so, i'll see if i can come up with a
tables/relationships design that may work for you.

two questions: do you want to produce each Contractor's "list to be
faxed"
in the database? and do you provide data to someone in your company so
that
the contractors will get paid?


Maybe I'm trying to do a complex thing, when all I need to do is
create
the
various tables:
Contractors; Crafts; Customers; etc., then create an input form

using
the

various tables as combo boxes to create an Orders table, this table would
be
related to the SD_Date table only. Each row of the Orders table

would
be

part of an order. i.e. SD_DateID > OrdersID (one to many).
Orders table would have fields:
1-SD_DateID
2-OrdersID
3-Contractors > ContractorsID
4-Customers > CustomersID
5-Crafts > CraftsID
6-NumberRequired

Does this sound too simple? Or is it not a workable thing?

Business flow? Well, I'm the Maintenance Planner and each 14 days period
I
collect and compile a listing of Carftsmen required by four
Supervisors
to
perform work on various machinery throughout the plant during a
"Turn-around". These Craftsmen are rewquested from up to 25 different
Contractors, each contractor is able to supply all the craftsmen types
required, but no one Contractor can supply all of our requirements
(140
to
175 Craftsmen). Prior to each Turn-around I send (fax) a listing of what
Craftsmen I require (a specific number of each) to different Contractors,
at
the same time the fax lets them know who the Craftsmen must report

to
(my

Customers). I am employed by the Customers company.

I hope this hasn't confused the issue.

Colin D.

well, maybe i'm on the wrong wavelength, but it seems to me there
should
be
some data element that defines a single Order - besides the date.
(after
all, you built your database based on the tables/relationships you
posted,
and have found that it's not working for you.) i could be all wet, but
without a better understanding of the flow of your business

process,
i

don't
know.

perhaps the bottom line is "who's paying for, or making the request
for,
your services as middleman?" if it's the Customers, then i think the
table
relationships i posted are probably appropriate. if it's the
Contractors,
then you can pretty much just switch out Customers/Contractors
tables
in
the
relationships i posted.

don't get hung up on the idea that everything done on one date has
to
be
considered a single Order. as long as each Order record contains a date
from
your SD_Date table, you can pull together all orders on a given date
whenever you need to, by using a query.

hth

<SNIP>

 

[Sapper]

Yes, Please, it will help me better understand the whole relationship thing,
I use A2000.
Just take out the NOSPAM from the address. I can hardly wait to see it.
Again, thanks a million, Colin D.

well, you're very welcome Colin; hope it turns out to be a workable
solution
for you, or at least gets you on the road to one.
btw, i created the posted design in a db, with a quick-and-dirty data
entry
form/subform setup, so i could test the relationships, data entry, and
query. you're welcome to see the db, just post back to this thread if you
want it, and i'll email it to you (it's an A2000 db, can be converted to
A97, or A2002-03). i'll be watching the thread for several days at least,
but probably not several weeks.

In a nutshell, WOW! I have still to really digest it all, but WOW, thanks
from what I've looked at, it is all and more than I expected.
It's true what some of my fellow learners have said about this ng, the help
to us novices is unbelievable and absolutely exceptional.
I shall leave this thread for now and take some time (probably several
weeks) to get to "know" this layout you've provided.
Tina - Thx, Colin D.

ok Colin, here's what i came up with. if i were designing this db for you,
in a professional relationship, i would have asked numerous additional
questions, to ensure that the design fully supports your process. since
that's not appropriate in this forum, i made some assumptions and built
from
there:

each Area of the plant is "supervised" by only one Supervisor (your
Customer).
each Supervisor is in charge of more than one Area.
(this is not really vital to the tables design as a whole; it simply
allows
more specific support of certain aspects of data entry, for the user.)
each Area may require maintenance using more than one Craft, in any single
Order.
the need for "x" number of Craftsment in a specific Area in a specific
Order, may be met by more than one Contractor (example: need 20
electricians in area B for order 7/29/05. contractor Charlie Co provide 15
of them, and contractor Tango Co provides the other 5.)
each Supervisor contacts you (Colin) with a request for x number of x
craftsmen for x areas on x SD_Date. the supervisor may make all
requests
for
an SD_Date at once, or "dribble" them in over a period of time.
you (Colin) decide how many craftsmen to request from each contractor, to
fill each supervisor's order for a particular SD_Date.

note: i used the term Supervisors in my model, in place of Customers.

based on the above, here's my suggested table/relationship design, as

tblSupervisors
SupervisorID (pk)
FirstName
LastName

tblAreas
AreaID (pk)
AreaName
fkSupervisorID (fk from tblSupervisors)
(linking each area to a specific supervisor, allows the Areas droplist in
a
data entry form to be filtered by the supervisor associated with the
current
record. if it's not appropriate, just delete the fk field.)

tblContractors
ContractorID (pk)
CompanyName

tblCraftsmen
CraftID (pk)
CraftName

tblSD_Dates
SDDate (pk)

tblOrders
OrderID (pk)
fkSupervisorID (fk from tblSupervisors)
fkSDDate (fk from tblSD_Dates)

tblOrderDetails
DetailID (pk)
fkOrderID (foreign key from tblOrders)
fkAreaID (foreign key from tblAreas)
fkCraftID (foreign key from tblCraftsmen)
NumberRequired

tblOrderDetailContractors
DetConID (pk)
fkDetailID (foreign key from tblOrderDetails
fkContractorID (foreign key from tblContractors)
NumberSupplied (or NumberRequested)

tblSupervisors 1:n tblAreas
tblSupervisors 1:n tblOrders
tblSD_Dates 1:n tblOrders

tblOrders 1:n tblOrderDetails
tblAreas 1:n tblOrderDetails
tblCraftsment 1:n tblOrderDetails

tblOrderDetails 1:n tblOrderDetailContractors
tblContractors 1:n tblOrderDetailContractors

to generat a list of requested craftsmen to fax to each of your
contractors,
the following query works, as

SELECT tblOrders.fkSDDate, tblContractors.CompanyName,
tblOrderDetailContractors.NumberSupplied, tblCraftsmen.CraftName,
tblAreas.AreaName, [FirstName] & " " & [LastName] AS SupvrName
FROM ((((tblOrderDetails LEFT JOIN tblOrders ON tblOrderDetails.fkOrderID
=
tblOrders.OrderID) LEFT JOIN tblAreas ON tblOrderDetails.fkAreaID =
tblAreas.AreaID) LEFT JOIN tblCraftsmen ON tblOrderDetails.fkCraftID =
tblCraftsmen.CraftID) LEFT JOIN tblSupervisors ON tblOrders.fkSupervisorID
=
tblSupervisors.SupervisorID) LEFT JOIN (tblOrderDetailContractors LEFT
JOIN
tblContractors ON tblOrderDetailContractors.fkContractorID =
tblContractors.ContractorID) ON tblOrderDetails.DetailID =
tblOrderDetailContractors.fkDetailID
WHERE (((tblOrders.fkSDDate)=#7/29/2005#));

all you have to do is group the records by ContractorID, in the report
design, and set a page break in the ContractorID Footer section.

hopefully the above will give you a least a starting point to make
heads
or
tails of your process. sorry it took me so long to work something up!

hth


Yes, I do require a one page per Contractor report that gets faxed to
each
Contractor.
And no each Contractor invoices through me for payment by our accounts
dept.
I'm glad you can see more of what I mean, anything you come up with must
be
better than what I've been toying with.
Colin D.

Colin, your explanation of your business process goes a long way in
helping
me to understand what your data tracking needs are. if you'll hang with
this
thread another day or so, i'll see if i can come up with a
tables/relationships design that may work for you.

two questions: do you want to produce each Contractor's "list to be
faxed"
in the database? and do you provide data to someone in your company so
that
the contractors will get paid?


Maybe I'm trying to do a complex thing, when all I need to do is
create
the
various tables:
Contractors; Crafts; Customers; etc., then create an input form using
the
various tables as combo boxes to create an Orders table, this table
would
be
related to the SD_Date table only. Each row of the Orders table would
be
part of an order. i.e. SD_DateID > OrdersID (one to many).
Orders table would have fields:
1-SD_DateID
2-OrdersID
3-Contractors > ContractorsID
4-Customers > CustomersID
5-Crafts > CraftsID
6-NumberRequired

Does this sound too simple? Or is it not a workable thing?

Business flow? Well, I'm the Maintenance Planner and each 14 days
period
I
collect and compile a listing of Carftsmen required by four
Supervisors
to
perform work on various machinery throughout the plant during a
"Turn-around". These Craftsmen are rewquested from up to 25 different
Contractors, each contractor is able to supply all the craftsmen types
required, but no one Contractor can supply all of our requirements
(140
to
175 Craftsmen). Prior to each Turn-around I send (fax) a listing of
what
Craftsmen I require (a specific number of each) to different
Contractors,
at
the same time the fax lets them know who the Craftsmen must report to
(my
Customers). I am employed by the Customers company.

I hope this hasn't confused the issue.

Colin D.

well, maybe i'm on the wrong wavelength, but it seems to me there
should
be
some data element that defines a single Order - besides the date.
(after
all, you built your database based on the tables/relationships
you
posted,
and have found that it's not working for you.) i could be all
wet,
but
without a better understanding of the flow of your business process,
i
don't
know.

perhaps the bottom line is "who's paying for, or making the request
for,
your services as middleman?" if it's the Customers, then i think the
table
relationships i posted are probably appropriate. if it's the
Contractors,
then you can pretty much just switch out Customers/Contractors
tables
in
the
relationships i posted.

don't get hung up on the idea that everything done on one date
has
to
be
considered a single Order. as long as each Order record contains
a
date
from
your SD_Date table, you can pull together all orders on a given date
whenever you need to, by using a query.

hth

<SNIP>

 

[tina]

email sent.

Yes, Please, it will help me better understand the whole relationship thing,
I use A2000.
Just take out the NOSPAM from the address. I can hardly wait to see it.
Again, thanks a million, Colin D.

well, you're very welcome Colin; hope it turns out to be a workable
solution
for you, or at least gets you on the road to one.
btw, i created the posted design in a db, with a quick-and-dirty data
entry
form/subform setup, so i could test the relationships, data entry, and
query. you're welcome to see the db, just post back to this thread if you
want it, and i'll email it to you (it's an A2000 db, can be converted to
A97, or A2002-03). i'll be watching the thread for several days at least,
but probably not several weeks.

In a nutshell, WOW! I have still to really digest it all, but WOW, thanks
from what I've looked at, it is all and more than I expected.
It's true what some of my fellow learners have said about this ng, the help
to us novices is unbelievable and absolutely exceptional.
I shall leave this thread for now and take some time (probably several
weeks) to get to "know" this layout you've provided.
Tina - Thx, Colin D.


ok Colin, here's what i came up with. if i were designing this db for you,
in a professional relationship, i would have asked numerous additional
questions, to ensure that the design fully supports your process. since
that's not appropriate in this forum, i made some assumptions and built
from
there:

each Area of the plant is "supervised" by only one Supervisor (your
Customer).
each Supervisor is in charge of more than one Area.
(this is not really vital to the tables design as a whole; it simply
allows
more specific support of certain aspects of data entry, for the user.)
each Area may require maintenance using more than one Craft, in any single
Order.
the need for "x" number of Craftsment in a specific Area in a specific
Order, may be met by more than one Contractor (example: need 20
electricians in area B for order 7/29/05. contractor Charlie Co

provide
15

of them, and contractor Tango Co provides the other 5.)
each Supervisor contacts you (Colin) with a request for x number of x
craftsmen for x areas on x SD_Date. the supervisor may make all
requests
for
an SD_Date at once, or "dribble" them in over a period of time.
you (Colin) decide how many craftsmen to request from each

contractor,
to

fill each supervisor's order for a particular SD_Date.

note: i used the term Supervisors in my model, in place of Customers.

based on the above, here's my suggested table/relationship design, as

tblSupervisors
SupervisorID (pk)
FirstName
LastName

tblAreas
AreaID (pk)
AreaName
fkSupervisorID (fk from tblSupervisors)
(linking each area to a specific supervisor, allows the Areas

droplist
in

a
data entry form to be filtered by the supervisor associated with the
current
record. if it's not appropriate, just delete the fk field.)

tblContractors
ContractorID (pk)
CompanyName

tblCraftsmen
CraftID (pk)
CraftName

tblSD_Dates
SDDate (pk)

tblOrders
OrderID (pk)
fkSupervisorID (fk from tblSupervisors)
fkSDDate (fk from tblSD_Dates)

tblOrderDetails
DetailID (pk)
fkOrderID (foreign key from tblOrders)
fkAreaID (foreign key from tblAreas)
fkCraftID (foreign key from tblCraftsmen)
NumberRequired

tblOrderDetailContractors
DetConID (pk)
fkDetailID (foreign key from tblOrderDetails
fkContractorID (foreign key from tblContractors)
NumberSupplied (or NumberRequested)

tblSupervisors 1:n tblAreas
tblSupervisors 1:n tblOrders
tblSD_Dates 1:n tblOrders

tblOrders 1:n tblOrderDetails
tblAreas 1:n tblOrderDetails
tblCraftsment 1:n tblOrderDetails

tblOrderDetails 1:n tblOrderDetailContractors
tblContractors 1:n tblOrderDetailContractors

to generat a list of requested craftsmen to fax to each of your
contractors,
the following query works, as

SELECT tblOrders.fkSDDate, tblContractors.CompanyName,
tblOrderDetailContractors.NumberSupplied, tblCraftsmen.CraftName,
tblAreas.AreaName, [FirstName] & " " & [LastName] AS SupvrName
FROM ((((tblOrderDetails LEFT JOIN tblOrders ON tblOrderDetails.fkOrderID
=
tblOrders.OrderID) LEFT JOIN tblAreas ON tblOrderDetails.fkAreaID =
tblAreas.AreaID) LEFT JOIN tblCraftsmen ON tblOrderDetails.fkCraftID =
tblCraftsmen.CraftID) LEFT JOIN tblSupervisors ON tblOrders.fkSupervisorID
=
tblSupervisors.SupervisorID) LEFT JOIN (tblOrderDetailContractors LEFT
JOIN
tblContractors ON tblOrderDetailContractors.fkContractorID =
tblContractors.ContractorID) ON tblOrderDetails.DetailID =
tblOrderDetailContractors.fkDetailID
WHERE (((tblOrders.fkSDDate)=#7/29/2005#));

all you have to do is group the records by ContractorID, in the report
design, and set a page break in the ContractorID Footer section.

hopefully the above will give you a least a starting point to make
heads
or
tails of your process. sorry it took me so long to work something up!

hth


Yes, I do require a one page per Contractor report that gets faxed to
each
Contractor.
And no each Contractor invoices through me for payment by our accounts
dept.
I'm glad you can see more of what I mean, anything you come up with must
be
better than what I've been toying with.
Colin D.

Colin, your explanation of your business process goes a long way in
helping
me to understand what your data tracking needs are. if you'll hang with
this
thread another day or so, i'll see if i can come up with a
tables/relationships design that may work for you.

two questions: do you want to produce each Contractor's "list to be
faxed"
in the database? and do you provide data to someone in your

company
so

that
the contractors will get paid?


Maybe I'm trying to do a complex thing, when all I need to do is
create
the
various tables:
Contractors; Crafts; Customers; etc., then create an input form using
the
various tables as combo boxes to create an Orders table, this table
would
be
related to the SD_Date table only. Each row of the Orders table would
be
part of an order. i.e. SD_DateID > OrdersID (one to many).
Orders table would have fields:
1-SD_DateID
2-OrdersID
3-Contractors > ContractorsID
4-Customers > CustomersID
5-Crafts > CraftsID
6-NumberRequired

Does this sound too simple? Or is it not a workable thing?

Business flow? Well, I'm the Maintenance Planner and each 14 days
period
I
collect and compile a listing of Carftsmen required by four
Supervisors
to
perform work on various machinery throughout the plant during a
"Turn-around". These Craftsmen are rewquested from up to 25 different
Contractors, each contractor is able to supply all the craftsmen types
required, but no one Contractor can supply all of our requirements
(140
to
175 Craftsmen). Prior to each Turn-around I send (fax) a listing of
what
Craftsmen I require (a specific number of each) to different
Contractors,
at
the same time the fax lets them know who the Craftsmen must

report
to

(my
Customers). I am employed by the Customers company.

I hope this hasn't confused the issue.

Colin D.

well, maybe i'm on the wrong wavelength, but it seems to me there
should
be
some data element that defines a single Order - besides the date.
(after
all, you built your database based on the tables/relationships
you
posted,
and have found that it's not working for you.) i could be all
wet,
but
without a better understanding of the flow of your business process,
i
don't
know.

perhaps the bottom line is "who's paying for, or making the request
for,
your services as middleman?" if it's the Customers, then i

think
the

table
relationships i posted are probably appropriate. if it's the
Contractors,
then you can pretty much just switch out Customers/Contractors
tables
in
the
relationships i posted.

don't get hung up on the idea that everything done on one date
has
to
be
considered a single Order. as long as each Order record contains
a
date
from
your SD_Date table, you can pull together all orders on a given date
whenever you need to, by using a query.

hth

<SNIP>