Create Multiple records at one time from button on a form

[yezleb]

I am trying to create multiple one to many records from a button on a form. The form displays data from a "bowlers" table. I need to take he PK from the bowler and create records in another table to represent fees owed by week. I have a table with the dates in it that I need added to the form. What I am looking to create is records in my table like this:

bowlerid, weekno, teamno, house, bowlertype, owedamt.

I have two types of bowlers, regular and sub. The code needs to look at the table first and determine if the bowlerid is already in the table..if not in table then look at bowler type and determine regular or sub. If regular need to create records for weekno 1-17 and add in the rest of the infor from the bowlers table along with a specific "owedamt".

I can not figure out how to add the multiple values while doing the loop for the 17 weeks. I have over 200 bowers and need to create the charges in the table upfront at one time for each bowler.

Any help is greatly appreciated.

 

[dhookom]

Please provide some actual table and field names. What do you want to do if the the bowler is a sub? What field determines if the bowler is a sub?

I expect you could use a query something like:

INSERT INTO AnotherTable (fielda, fieldb fieldc,...)
SELECT FieldA, Fieldb, fieldc,...
FROM bowlers, [table with the dates]
WHERE ....

Duane
Hook'D on Access
MS Access MVP

 

[yezleb]

Bowlers table:
bowlerid
bowlertype
team

Dates table:
weekno
date
house

FeesAndPayments table: to append records to
bowlerid (from bowlers table)
weekno (need an entry for each week 1-17
team (from bowlers table if regular, else blank)
house (from dates table...corresponding week)
bowlertype (from bowlers table)
owedamount (to be entered as 12.00 for regular bowler, 0.00 for sub)
paidamount (to be manually entered weekly)
paidtype (to be manually entered weekly)

I need to have a button on my form that would check to see if the current bowler id has had records created in the FeesAndPayments table. If not, need to add each record by week for that bowler. If they are a regular bowler then need to enter weekno, team, house, bowlertype, owedamount (12.00). If they are a sub need to enter weekno, house, bowlertype. Need one recored for each of the 17 weeks.

Is this the info you are looking for?

 

[dhookom]

CAUTION: untested SQL
I would first create a cartesian query [qcarBowlersWeeks] to get all bowlers and weeks

SELECT BowlerID, BowlerType, Team, WeekNo, Date, House
FROM Bowlers, Dates
WHERE WeekNo Between 1 and 17;

Then create your INSERT/APPEND query with SQL like

INSERT INTO FeesAndPayments (BowlerID, WeekNo, Team, House, BowlerType, OwedAmount)
SELECT BowlerID, WeekNo, Team, House, BowlerType, IIF(BowlerType = "sub", 0,12)
FROM qcarBowlersWeeks Q LEFT JOIN FeesAndPayments F ON 
Q.BowlerID = F.BowlerID AND Q.WeekNo = F.WeekNo
WHERE F.BowlerID Is Null

Duane
Hook'D on Access
MS Access MVP