form and query problem. please help.

[Mitchell_Collen via AccessMonster.com]

All tables are linked with weak entities. However, when i enter data on the
form I can't get it to let me enter more than one partipicant without access
generating a new invoice id. however i need one invoice to many participants.
It wont work and i have no idea what to do at this point. in addition the
workshop will not let me add workshop to invoice. this is a small mdb and i'd
like to email it to anyone who can assist me with the relationships as I
think this is the problem but I don't know what to do. please help me.


INVOICE
invoiceNO - autonumber
invoice prices

WORKSHOP
workshopNO - autonumber
workshopName

PARTICIPANT
participantNo
par_Name

user process access process
enter invoice price autogenerate invoice no.
enter desired workshop select list of particpants add
workshop id to invoice
enter participants store first paticipant id to
invoice, store second part. id...

-MC

 

[Guest]

First off, if your invoice number is an autonumber then of course it will
create a new ID for each participant. That is what autonumber is for!

Generally I think you need to clarify your design and table relationships
(and you should have put this in the database design area rather than queries
which deal specifically with Access query design and SQL). You don't specify
what your primary keys are. If you don't know what a primary key is then you
need to do some learning (and I mean that in a nice way!)

I think I can establish that each workshop has a single invoice and each
invoice (or workshop) has many participants?

 

[WarriorMCB]

If you need to have the same invoice Number then take it off AUTONUMBER and generate the number yourself. The table can be based on a different ID name thats not used in the table. You will have to delete the existing relationships in order to make the change, once you have done this establish the relationships again without linking it to autonumber ID.

Hope this helps
MCB

 

[Mitchell_Collen via AccessMonster.com]

ok, thanks. yea, i had never thought about the invoice primary key being the
problem. it is autonumber. i think maybe that's all i will need to change to
link them correctly. i agree with you, this is a design issue. i will use
that link instead. thanks. MC

First off, if your invoice number is an autonumber then of course it will
create a new ID for each participant. That is what autonumber is for!

Generally I think you need to clarify your design and table relationships
(and you should have put this in the database design area rather than queries
which deal specifically with Access query design and SQL). You don't specify
what your primary keys are. If you don't know what a primary key is then you
need to do some learning (and I mean that in a nice way!)

I think I can establish that each workshop has a single invoice and each
invoice (or workshop) has many participants?

All tables are linked with weak entities. However, when i enter data on the
form I can't get it to let me enter more than one partipicant without access

[quoted text clipped - 24 lines]

-MC