many to many relationship with dates

[tonyrulesyall]

I am working on a Microsoft XP Access database.

There is a many to many relationship that I would like to show in
the queries and then report.

Each new file has a "date created" field. They also have multiple
"dates paid".

I would like to show a report that focuses on each "date created" and
shows all the "dates paid" just for that specific "date created".

 

[louisjohnphillips]

I am working on a Microsoft XP Access database.

There is a many to many relationship that I would like to show in
the queries and then report.

Each new file has a "date created" field.  They also have multiple
"dates paid".

I would like to show a report that focuses on each "date created" and
shows all the "dates paid" just for that specific "date created".

This sounds like the table has the following structure:

Create table1
(
DateCreated date/time,
DatePaid1 date/time,
DatePaid2 date/time,
DatePaid3 date/time,
DatePaidN date/time
)


Would it be possible to split this table into a Master/Detail
relationship?

Create table1
(
FileID long autonumber,
DateCreated date/time
)

Create table2
(
FileID long autonumber,
DatePaid date/time
)

Then the query would be:

SELECT A.FileID, B.DatePaid
from table1 inner join table2 on table1.FileID = table2.FileID;

To use this in a report, employ a report and sub report. In the main
report,
the rowsource might be
SELECT * from table1;
In the sub report the rowsource might be
SELECT * from table2;

Then link the Parent ( main report ) to the Child ( sub report ) with
FileID.