Create a split

[tcheck]

I have a quick and I think easy question that I am just not getting.

I want a user to select a item in a form from a pull down(field1), enter an amount of money in another field (field2) and then enter a split amount in a third field (field3).

Once all 3 are entered, they would click a button that would activate an append query that would take the first field enter it into a table and then take the field2/field3 put that into the table as well. So far easy.

But now I want as many rows created as was input in field3. So if they put in 1500 in field2
3 in field3


I would get 500 three times in my input table.

Should I run this query 3 times in my code?

[TheShabz]

Why would you want to hold data in a table in triplicate? Perhaps if you give us some context we can help better?

[tcheck]

Thanks for the reply;
It's to split the amount of money into an allocation table.

[TheShabz]

Instead of holding A record with 500 3 times, try storing the original 1500 and then the 3 as another field. Then for display or reporting purposes, simply divide it on the fly do display. I think you would be best served doing that, unless I'm still not grasping your intent.

[tcheck]

Lets say that we get a payment in for $1500 an I need to split it between 3 different people and I need to know where that payment came from (filed1) what was the total payment (field2) and how many people I had to pay that check to (field3). So I want to add that information to a table (called allocation) with 3 different rows for the 3 people that will get paid their $500. In the allocation table i need;
where the check came from
how much did this person get paid 1500/3= 500
who is getting paid.

After these 3 rows are input into the allocation table the user would enter who the 3 people are that are getting paid.

So I need to figure out a way to add 3 new rows (or next time it may be $10,000 divided by 5 people.)

Sorry, it's making sense to me but I am not sure if the above makes it any clearer?

[TheShabz]

Ahh, so you're not holding the data in triplicate but have a record for each person the split is going to. The "proper" way to do it would be to have one table that records the total payment amount, one table that records all of the people, and a third, that acts as a junction table to facilitate a many to many relationship between the previous 2 tables that holds the actual splits and the amount. Here's what it would look like:
tblPayment
paymentID (autonumber PK)
paymentFrom
paymentDate
etc
totalAmount
numberOfSplits

tblPersons
personID (Autonumber PK)
firstName
lastName
etc

tblSplit
splitID (autonumber PK)
paymentID (foreign key)
personID (foreign key)
split value

That being said, your idea of running the query 3 times, once with each person being paid, will work but not follow normalization standards. You would have a For Loop in your VBA with (i=1, i <= Me.SplitNumber, i++) and have it iterate through each name, running an append query each time. If you have the time/patience/permission, I'd recommend going with the properly normalized method as it will make querying and reporting much easier. If not, your original idea will work, just takes some coding. I'm not all that good with VBA outside of creating workflows but I'm sure you can find what you need in Google or asking around here.

[tcheck]

There is a table for all of the above, this is just an addition where as it is added to the total payment but the money is divided by multiple producers and I would like to add this allocation to a seperate table.

Any VB coders out there?