Adding multiple records

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Group I need ideas!

I'm currently working on a Purchase Order database. Here is the workflow
that I would like to use in approaching this project. This is in a very
simple form (not access form) now and once I get the parts of it to work
then I will add all of the bells and whistles.

1.) I have one table that holds all of the - normalized - data for purchase
orders.

2.) I have a second table that will keep just a list of the last 50 numbers.
Once the numbers are used I don't need them again.

table2 layout (very simple)
UsedPONumbers |
.....
5120 |
5121 |
5122 |
.....
5128 |

2.) A person will open up a form and as soon as the form opens it will run a
function - using the DMax function - to grab the next PO number i.e.
5129.From the second table then enter it back to increment the count by one.
This is done to keep people from taking a number that isn't available.

3.) The person will then enter the data in the form. I want this form to be
totally unbound from any table. This will eliminate having to deal with
slowly responding forms.

form layout (very simple)

Purchase Order Number: 5129 User Id: 099
____________________________________
Detail | Description | Material | Quantity | etc...
01 | Mounting Plate | CRS | 1
02 | Cutter | A-2 | 2
03 | Socket Head Cap Screw | Pur | 10

4.) After clicking a command button. The data will then be entered into
table number 1.

table1 layout (very simple)
UserId | PO Num | Detail | Description | Material | Quantity | etc...
099 | 5129 |01 | Mounting Plate | CRS | 1
099 | 5129 |02 | Cutter | A-2 | 2
099 | 5129 |03 | Socket Head Cap Screw | Pur | 10

5.) does the data flow this way?
a. form which is bound to a temp table created with SQL - setting
the Default value of the PurchaseOrder to the active number i.e. 5129
i. table is created and populated via user activities i.e.
copy paste, directly entering data, etc....
ii. An event then appends data to the first table.
iii. second table (temp) is deleted.
b. using some sort of an Array function.
i. How?
ii. What would the syntax be?
iii. Would it be cleaner?

Regards
(e-mail address removed)
 
Hi Mike

First, I can't see the point of the UsedPONumbers table. The next PONumber
can easily be found using this expression:
NextPO = Nz(DMax("PONumber", "PurchaseOrders"),0)+1

Second, why use a temporary table? Then you will have to copy all the new
records to the real table, and you say speed is important!

Third, Access has a very powerful way to manipulate data in one-to-many
related tables, and that is linked subforms. All the hassles of setting up
temporary default values and ensuring integrity of related records are taken
care of for you.

I suggest you create two forms - a single view form bound to your
PurchaseOrders table and a continiuous view form bound to your POItems
table. Place the second one in a subform control on the first, linked by
PONumber, and the job will be done.
 
Graham

I know this will save time in the layout of stuff right now. But just
how much data can it handle with up to10 users connected to it at once. I
looked into our current PO - excel - book and for 2004 we have generated
5000 entries. Each entry contains 20 pieces of - normalized - data.Which
adds up to 100,000 pieces of data so far this year.

Mike Sundman
mike.sundman.

Graham Mandeno said:
Hi Mike

First, I can't see the point of the UsedPONumbers table.
Click to expand...

!!!! If i choose to use a temp table the second table will make the unique
record so no one else grabs the number before you can place your po.

The next PONumber
can easily be found using this expression:
NextPO = Nz(DMax("PONumber", "PurchaseOrders"),0)+1
Click to expand...

!!!! Cool formula.
Second, why use a temporary table? Then you will have to copy all the new
records to the real table, and you say speed is important!
Click to expand...

!!!!! Is it quicker to open 5000 records to add 20 or is it quicker to just
add the 20 without the overhead of opening up 5000.
Third, Access has a very powerful way to manipulate data in one-to-many
related tables, and that is linked subforms. All the hassles of setting up
temporary default values and ensuring integrity of related records are taken
care of for you.

I suggest you create two forms - a single view form bound to your
PurchaseOrders table and a continiuous view form bound to your POItems
table. Place the second one in a subform control on the first, linked by
PONumber, and the job will be done.
Click to expand...

!!!!! Some of my forms may need to have up to 4 linked and fully
syncronized subforms.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Mike said:
Group I need ideas!

I'm currently working on a Purchase Order database. Here is the workflow
that I would like to use in approaching this project. This is in a very
simple form (not access form) now and once I get the parts of it to work
then I will add all of the bells and whistles.

1.) I have one table that holds all of the - normalized - data for purchase
orders.

2.) I have a second table that will keep just a list of the last 50 numbers.
Once the numbers are used I don't need them again.

table2 layout (very simple)
UsedPONumbers |
....
5120 |
5121 |
5122 |
....
5128 |

2.) A person will open up a form and as soon as the form opens it will
Click to expand...
run
a
function - using the DMax function - to grab the next PO number i.e.
5129.From the second table then enter it back to increment the count by one.
This is done to keep people from taking a number that isn't available.

3.) The person will then enter the data in the form. I want this form to be
totally unbound from any table. This will eliminate having to deal with
slowly responding forms.

form layout (very simple)

Purchase Order Number: 5129 User Id: 099
____________________________________
Detail | Description | Material | Quantity | etc...
01 | Mounting Plate | CRS | 1
02 | Cutter | A-2 | 2
03 | Socket Head Cap Screw | Pur | 10

4.) After clicking a command button. The data will then be entered into
table number 1.

table1 layout (very simple)
UserId | PO Num | Detail | Description | Material | Quantity | etc...
099 | 5129 |01 | Mounting Plate | CRS | 1
099 | 5129 |02 | Cutter | A-2 | 2
099 | 5129 |03 | Socket Head Cap Screw | Pur | 10

5.) does the data flow this way?
a. form which is bound to a temp table created with SQL - setting
the Default value of the PurchaseOrder to the active number i.e. 5129
i. table is created and populated via user activities i.e.
copy paste, directly entering data, etc....
ii. An event then appends data to the first table.
iii. second table (temp) is deleted.
b. using some sort of an Array function.
i. How?
ii. What would the syntax be?
iii. Would it be cleaner?

Regards
(e-mail address removed)
Click to expand...
Click to expand...
 
I know this will save time in the layout of stuff right now. But just
how much data can it handle with up to10 users connected to it at once. I
looked into our current PO - excel - book and for 2004 we have generated
5000 entries. Each entry contains 20 pieces of - normalized - data.Which
adds up to 100,000 pieces of data so far this year.
Click to expand...

Access (using properly indexed tables and well-designed queries) can
handle million-row tables with aplomb. You're WASTING time by creating
the temp tables, not saving it!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Group

Ok I don't want to WASTE time. Is there a place were I could find out how to
speed things up in future databases that I create. I have a time card
database right now that is really SLOW and it doesn't have anywhere near
"million-row" tables - it must just be my way of doing things.

Mike S.
 
Back
Top