How to use a many-to-many table to retrieve (query) data for a for

[Michael_Randall]

Hello, I saw an earlier posting that related to this question but the
response didn't quite answer my question. I created the following Access
tables and fields.

tblWriter
WriterID - Autonumber
WriterName - text

tblProject
ProjectID - Autonumber
ProjectName - text

tblWriter_Projects - (many-to-many joining table)
WriterID - number
ProjectID - number

If I want to display, in a form, all the projects that a certain writer
worked on, I believe I would need to create a query.

I have two forms, one that creates a new writer and one that creates a new
project. The two tables are linked through the tblWriter_Projects table, but
since I haven't entered any data into that table, how can I use (query) it to
retrieve information?

I think that once I create a query, I would then have to base the forms
record source on that query. But I just can't get the concept of using the
tblWriter_Projects table when I haven't entered any data into it.

Any suggestions or examples would be greatly appreciated.

Thanks

 

[Jeff Boyce]

Michael

If you are trying to see all the Projects for a Writer, one way to do this
would be to:

* create a form based on tblWriter (actually, on a query of it)
* create a form based on a query joining tblWriter_Project and tblProject
* use the second form as a subform, inserted into frmWriter -- use the
WriterID as the Parent/Child fields in the subform control.

Use the frmWriter to "look up" a writer -- all the Projects s/he is
connected to would show in the subform. To add a new project, add a new
project in the subform.

To add a new Writer, add a new writer in the main form.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/