Order Entry Form/Database

[Dee S via AccessMonster.com]

I am trying to create an service order entry database and am having
trouble. The template provided by Access is way too complex for what I
need. I'm looking at starting with 2 tables... one for prodcuts and one
for customers. Then a customer form with a subform for products ordered
(similar to what's in the template). This information on these forms needs
to automatically export to table which I can link to a contract and also
export for volume reporting.

Is there anyplace I can get step by step instructions? I try to modify the
template but continue to run into problems. I do not have inventory to
track.

I appreciate any help!!

 

[John Vinson]

I am trying to create an service order entry database and am having
trouble. The template provided by Access is way too complex for what I
need. I'm looking at starting with 2 tables... one for prodcuts and one
for customers.

In that case *you don't understand how relational databases work*.

Then a customer form with a subform for products ordered
(similar to what's in the template). This information on these forms needs
to automatically export to table which I can link to a contract and also
export for volume reporting.

So... you want to do this all in two tables?? You can't!

I see five tables right there: a table of Products; a table of
Customers; a table of Orders (each customer will hopefully have
several orders, not just one); a table of OrderDetails, unless each
order is for one and only one product; and a table of Contracts. I
don't know what the Contracts table would be related to, you'll need
to explain just what a Contract means.

In short, every real-life thing, person, or event of importance to
your application needs its own Table. If you JUST have a table of
Customers, and a table of Products, then there is simply no way to
relate them usefully; the only way would be to allow each customer to
order only one product, one time only by putting the ProductID in the
customer table. And that's no way to run a business!

Is there anyplace I can get step by step instructions? I try to modify the
template but continue to run into problems. I do not have inventory to
track.

I'd suggest you check out the links at http://www.mvps.org/access for
tutorials or books on relational database design. I agree, it's
complicated - but you can't create a useful database unless you have a
decent grounding in relational database design!

John W. Vinson[MVP]

 

[Dee S via AccessMonster.com]

Thank you. I have found a useful template, but am having difficulties with
the total. I'm using a customer form with an orders subform. I'm trying
to have a total on the customer form which adds the totals from the subform
but am getting errors for each way I try. I have tried the simple
=sum( Forms![Workorders by Customer Subform]![Workorder Total] )
I get #Error
I've also tried
=nz( Forms![Workorders by Customer Subform]![Workorder Total] )
I get #Name?
Any suggestions?

 

[John Vinson]

Thank you. I have found a useful template, but am having difficulties with
the total. I'm using a customer form with an orders subform. I'm trying
to have a total on the customer form which adds the totals from the subform
but am getting errors for each way I try. I have tried the simple
=sum( Forms![Workorders by Customer Subform]![Workorder Total] )
I get #Error
I've also tried
=nz( Forms![Workorders by Customer Subform]![Workorder Total] )
I get #Name?
Any suggestions?

Since I have NO idea what template you're using or how your tables are
structured, I'm not certain this is exactly what you want. But I'd
suggest instead using two textboxes. Put one on the Footer of the
subform (you may need to use View in design view to make the form
footer available). Put a textbox txtWorkorderGrandTotal on the footer
with a control source

=Sum([Workorder Total])

assuming that there is a table field named [Workorder Total] in the
form's recordsource.

Then, on the mainform, you need the name of the Subform Control
containing the subform. This might or might not be the same as the
name of the form *within* that control. Set the mainform textbox's
control source to

=subWorkorders.Form!txtWorkorderTotal

where subWorkorders is the Name property of the subform control.

Note that this will not store the total in any table, but that it
would be bad design to store that value in any case; just recalculate
it as needed.

John W. Vinson[MVP]

 

[Dee S via AccessMonster.com]

What if there is not a table field for the total, can I still have a
running total in the footer?
The total I'm working with has other controls within the form that are not
on the table. Any suggestions?

 

[John Vinson]

What if there is not a table field for the total, can I still have a
running total in the footer?

A *running* total to me means that you're putting a total value on
each row, summing the values up to that point. This of course would be
in the body of the form, not the footer - right? Or are you meaning
something else by "running"?

If that is what you want, you can include a calculated field in the
Query upon which the form is based, using DSum() to sum up the values
up to this point.

The total I'm working with has other controls within the form that are not
on the table. Any suggestions?

No, because you don't say what those controls might be, or what it is
that you're trying to sum, or even clearly what it is that you want to
see.

John W. Vinson[MVP]