Create records based on ordered number of items

[Memento]

Hi all!

Alright, maybe this is easier than it seems to me... Scenario:

I have a orders table, and a bound products table, created onto a
form/subform. In an order I create, i can for example type in:

Amount Description Unitprice
Total
3 Hewlett Packard nx7200 laptops 100
300
15 Logitech Mouse USB ... 10
150

Pretty straightforward, right?

Normally one day these items should get delivered. Once delivered, I want to
be able to set the state of the order to "delivered".

Once an order has its state set to "delivered", I want to be able to create
from a single record above (3 laptops), 3 separate records in a
hardware-table (that contains more details about each laptop which I will
add). The same goes for the Mouses, which then would create 15 separate
records in the related hardware-table (i'll put in serial numbers afterwards).

Preferably this would go automatically... (I can put in a confirmation
dialog in between).

Any suggestions how to approach this?

 

[CraigH]

Hi all!

Alright, maybe this is easier than it seems to me... Scenario:

I have a orders table, and a bound products table, created onto a
form/subform. In an order I create, i can for example type in:

Amount Description Unitprice
Total
3 Hewlett Packard nx7200 laptops 100
300
15 Logitech Mouse USB ... 10
150

Pretty straightforward, right?

Normally one day these items should get delivered. Once delivered, I want to
be able to set the state of the order to "delivered".

Once an order has its state set to "delivered", I want to be able to create
from a single record above (3 laptops), 3 separate records in a
hardware-table (that contains more details about each laptop which I will
add). The same goes for the Mouses, which then would create 15 separate
records in the related hardware-table (i'll put in serial numbers afterwards).

Preferably this would go automatically... (I can put in a confirmation
dialog in between).

Any suggestions how to approach this?

Just had to respond, the other responses ... well 1 I think was for another
question.

Many ways to handle changing the state to delivered and yes another field in
the order table could be used for that. Also in what I hope you have is a
OrderDetail table or another table (but that is a little more involved) you
have the deliveries of the line items.

However you decide to do it some manual input of deliveries is entered and
then you can allow access to the individual line items to put in furthur
information. This is not contrary to normalization because I see the table
you are creating as details to the OrderDetails Table.

Now the ways this should be done may be debatable as to the optimal
normalization:

You either have another table (I am going to it LineItemDetail instead of
OrderDetailDetail)

1. It looks like this:
LineItemDetailID PK
OrderDetailID FK
SerialNumber
and other info - this would be simple information

2. Or it could look like this

LineItemDetailID PK
OrderDetailID FK
HardwareID
- But you also need a Hardware Table

HardwareID
PartNumberID - to the PartsTable
SerialNumber
and other Info - even links to other tables showing ie software instaled on
this item.

Some comments:
The First scenario - is really for only simple stuff that you would like to
record, the second really is almost like building a system but still could be
used for only the small stuff (serial numbers, color etc).

On the second one you could have just used the Hardware ID on the order
details and this might be the confusion about normatization but really the
Order Detail is line is just giving you the "generic" hardware.

To impliment either the LineItemDetail you will just have a subform to the
OrderDetails - I would just put this as a subform on the Orders form
below/besisde the order details - you have a text box that get the
orderdetailId - and that is the Parent link to the Child Link for that
subform.

There of course more involved in setting up the 2nd one with the Hardware
table.

Hope this will help - let me know

 

[vanderghast]

That was assuming your wish was to generate "amount" new records. Change the
SELECT to an INSERT to append those records into a table which will already
have the extra field (nullable) ready to get the eventual annotation, per
item.


Vanderghast, Access MVP

 

[Memento]

Hi all,

I can see this is ending up as a "normalization" issue... it's actually
rather simple. I'll explain again:

You have a orders table, and (indeed CraigH) an OrderDetails table, related
tables offcourse.

Now suppose you order 15 laptops from Hewlett Packard (HP Compaq nx 6710 for
example). Considering normalization this is not contrary to normalization
rules as vanderghast and MVP state: if i get these delivered, these are 15
individual laptops, each with their own set of hardware and software, and
specifics, as serial numbers, os installation, etc... so basically i do not
agree that 'normalization' should go as far as that (there's also something
as 'over-normalization', and 'denormalization'! And using a 'counter' is not
the same as using real records with individual information in it (again: even
when manufacturer, type, model, and any other information is the same, other
fields won't be).

In my order I can easily type these as:

AMOUNT DESCR PRICE
15 HP laptop 100

So one record orders 15 HP Laptops here. As they get delivered, i'm getting
15 unique laptops. How do you get them into 15 unique records...
(normalization and other discussions put aside). I've tried add-queries
without much success, so this will end up in code i guess...

Tx for the information so far guys.

With regards,

 

[Paolo]

Hi Memento,
let's say the table where you have the orders is called orders (and you have
a flag that identify the delivered records called d_flag and another flag
that identify records not yet inserted in the second table called i_flag) and
the table where you wanna create the records (one per each piece delivered)
extra_infos you can do that. Add a button to your form and insert this code
in the on click event

dim rec_del as DAO.recordset
dim rec_ins as DAO.recordset

set rec_del=currentdb.openrecordset("select * from orders where
d_flag=-1 and i_flag=0",dbopendynaset) '' all this on one line
set rec_ins=currentdb.openrecordset("select * from
extra_infos",dbopendynaset) '' all this on one line
do while not rec_del.eof
for i=1 to rec_del!amount
rec_ins.addnew
rec_ins!descr=rec_del!descr
rec_ins.update
next i
rec_del.edit
rec_del!i_flag=-1
rec_del.update
rec_del.movenext
loop
msgbox "Done"

This is air code so you have to test it.
HTH Paolo