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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" <colindodd@xxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:uL$Jk8LjFHA.1428@xxxxxxxxxxxxxxxxxxxxxxx
> 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" <nospam@xxxxxxxxxxx> wrote in message
> news:DrgDe.449646$cg1.46902@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> > 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" <colindodd@xxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > news:%23luLytLjFHA.3608@xxxxxxxxxxxxxxxxxxxxxxx
> >> 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.
> >>
> >> "tina" <nospam@xxxxxxxxxxx> wrote in message
> >> news:vyXCe.10915$5N3.1889@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> >> > 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>
> >>
> >>
> >
> >
>
>


.

Relevant Pages

  • Re: An end to the "poor design" argument
    ... It turns out God is merely a figurehead deity, ... contracted out the job to his old buddies at Halliburton. ... kickback from Halliburton who was already named as prime contractor ... It does not in any way rule out the "intelligent design" ...
    (talk.origins)
  • Re: An end to the "poor design" argument
    ... It turns out God is merely a figurehead deity, ... contracted out the job to his old buddies at Halliburton. ... kickback from Halliburton who was already named as prime contractor ... It does not in any way rule out the "intelligent design" ...
    (talk.origins)
  • Re: Looking for a design for an order type database...
    ... > each Supervisor is in charge of more than one Area. ... may be met by more than one Contractor (example: ... > fkSupervisorID (fk from tblSupervisors) ... > fkOrderID (foreign key from tblOrders) ...
    (microsoft.public.access.gettingstarted)
  • Re: Looking for a design for an order type database...
    ... Again, thanks a million, Colin D. ... >>> fkOrderID (foreign key from tblOrders) ... >>> all you have to do is group the records by ContractorID, ...
    (microsoft.public.access.gettingstarted)
  • Re: What Exactly IS a "Hybrid" table saw?
    ... >>>just a contractor saw with a closed base? ... >>>trunnions or by adjusting the table (like a ... >>the first hybrid, the DeWalt, still offers only a half enclosed base, as ... > Probably an Orion design. ...
    (rec.woodworking)