Help with SQL code

[rdscott]

All fields not mentioned for simplicity.

Table Invoices – fields: invoiceno (autonumber), college
Table Members – fields: member, college, new-member-flag
Table InvoiceMembers – fields: invoiceno, member

Form creates records for the table invoices after selecting college. Would like to use command button to create records in table InvoiceMembers. Fields to populate:
InvoiceMembers.invoiceno from form.invoiceno
InvoiceMembers.member from members.member


for members.college equal to form.college and member.new-member-flag is true

How can this be done?

Thanks

[June7]

Don't need college in Invoices table.

Create a form/subform arrangement http://office.microsoft.com/en-us/ac...010098674.aspx

Main form bound to Invoices, subform bound to InvoiceMembers with a combobox to select members. If member not in combobox list, use NotInList event http://www.blueclaw-db.com/access_no...ed_example.htm

[rdscott]

college is who the invoice is billed to so college needs to be there. that is collegeid (college for short, to simplified things) that links to a college (customer) file. The reason for the invoicemembers table is show what members were billed on an invoice. The members will stay with that invoice. The invoice table and invoicemembers table will be linked together by invoiceno. i need invoicemembers to be created from members table for new members. i hope this explains a little better. Thanks

[June7]

Okay, only thing I will add is that you probably want to make the combobox to select members dependent on the college selected in college combobox. Review: http://datapigtechnologies.com/flash...combobox2.html

[orange]

What's the purpose of the new_member_flag? How does it get reset? (Access does not like the "-")
Have you considered a BecameMemberDate and do some calculations based on that Date?

[rdscott]

I appreciate all the comments. To clarify: I have two tables. College and CollegeMembers (one to many). College Members are added frequently. When creating an invoice for a college for general inventory, i need to also pull into the invoice the new members that were added since last invoice was created. That is the reason for InvoiceMembers table and NewMemberFlag. Flag set to false when new members are inserted into InvoiceMembers. End result is having an invoice table and invoicemembers (one to many relationship). I need the code that would be in the form to insert the invoice number (from the form) and the new members (from the collegemembers) into invoicemembers table. Thanks again for your input.