By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,967 Members | 1,241 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,967 IT Pros & Developers. It's quick & easy.

Table normalizing help/suggestion

P: n/a
Hi!

Our database, an inventory/order system in ms access 2000, is working
just fine.

However, the other day, one of our employees suggested that it should
be possible to do some advanced work on the tblCustomer.

What he suggested was: Could there be possible to choose a customer, on
creating a new order from frmOrder, and automatically have an orderline
be filled out with a preset item, from tblItems, in frmOrderdetails?

As for now, we use the traditional ordersystem like:

tblCustomers -> tblOrders -> tblOrderdetails <- tblItems

and when choosing a customer, we have to fill in each orderline the
customer order.

We have been looking at having a field in tblCustomers that gets its
data from tblItems, or a subtable to tblCustomer that register preset
data from tblItems, but will not this create redundancies or other
problems?

So, if anybody have a clever idea how this is to be achieved, if
possible at all, please suggest.

Me.Name

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
The answer would depend on your business systems.

If there are current items that tend to be on most orders at the moment, and
you want to default all new orders to include that item you could add the
items to a OrderDetailDefaultItem table. Any items in that table would then
be added to the order by executing an INSERT query statement in the
AfterInsert event of the Order form.

Alternatively, if each customer has particular items they usually order
(specific to them), you could create a CustomerDefaultItem table. Using the
same event, you append any records for the customer.

There is no redundancy in setting up default items. But if you wanted to get
really smart, you could possibly automate this instead of setting up the
extra tables. This would involve some fuzzy logic that looks back over
recent orders to see what everyone is ordering (or back at recent orders for
this customer to see what she is always ordering), and if items meet a
threshold (e.g. 75% of orders in the period have the item), then append it
to the order as a default item.

However, before you program anything where the software is trying to be too
smart, consider the possible side effects. Is it likely that some data entry
person will not notice the item(s) the computer put into the order
automatically, and you will have customers complaining that they were sent
and charged for things they did not order?

Ultimately, it is your business logic that will dictate whether this is a
good idea or a bad idea.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<gs***@hotmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

Our database, an inventory/order system in ms access 2000, is working
just fine.

However, the other day, one of our employees suggested that it should
be possible to do some advanced work on the tblCustomer.

What he suggested was: Could there be possible to choose a customer, on
creating a new order from frmOrder, and automatically have an orderline
be filled out with a preset item, from tblItems, in frmOrderdetails?

As for now, we use the traditional ordersystem like:

tblCustomers -> tblOrders -> tblOrderdetails <- tblItems

and when choosing a customer, we have to fill in each orderline the
customer order.

We have been looking at having a field in tblCustomers that gets its
data from tblItems, or a subtable to tblCustomer that register preset
data from tblItems, but will not this create redundancies or other
problems?

So, if anybody have a clever idea how this is to be achieved, if
possible at all, please suggest.

Me.Name

Nov 13 '05 #2

P: n/a
Hi!

Thanks Allen.

As always you point me in the right direction.

I think the first part of your answer is what really meet our business
logic. I'll have a go and see where I'll land.

Me.Name

Nov 13 '05 #3

P: n/a
Great.

(BTW, you will probably have to Requery the subform after executing the
Append query before the items show in the subform.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<gs***@hotmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

Thanks Allen.

As always you point me in the right direction.

I think the first part of your answer is what really meet our business
logic. I'll have a go and see where I'll land.

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.