Many to Many problem

[JohnDC]

Hi Mike

If I dispense with the backward part ie attachment to door list, I could simplify it very much.
So now I would have a Door which can have many attachments on it, these attachments would not be grouped. So the table would have the following fields:
Item description
Cost of item
Quantity to attach to door

This would simplfy things massively.
That done how would I display the complete list of attachments on the form to choose with each door, bearing in mind I have to enter the quantity for each attachment.
I have tried a Subform with attachment as the subform source. Do I need to have a query and run the form from the query?
I am trying to keep this very simple but it seems to be very hard.

 

[Rabbie]

Hi Mike

If I dispense with the backward part ie attachment to door list, I could simplify it very much.
So now I would have a Door which can have many attachments on it, these attachments would not be grouped. So the table would have the following fields:
Item description
Cost of item
Quantity to attach to door

This would simplfy things massively.
That done how would I display the complete list of attachments on the form to choose with each door, bearing in mind I have to enter the quantity for each attachment.
I have tried a Subform with attachment as the subform source. Do I need to have a query and run the form from the query?
I am trying to keep this very simple but it seems to be very hard.

However you decide to do this then you are going to need to have a query and run the form from that.

Basically in Access you store data in tables, use queries to extract the relevant data and forms/reports to display the data.

 

[JohnDC]

Ok, I shall give it a go - forgive my ignorance still learning!!

Cheers all
John

 

[Mike375]

If I dispense with the backward part ie attachment to door list, I could simplify it very much.

John,

You don't need to do that. As I said earlier I fequently want to see all the policy holders that have xyz policy. Sometimes for a mail out etc.

IN TERMS OF RECORDS you never have one attachment to many doors. I think this might be the hurdle. What you have is lots of doors that have the same type of attachment.

Let's go back to the data base that stores a person and their kid's details. Lots of parents will have a son called John who is age 10. Thus in a large contact data base in the kid's details there will be lots of 10 year old Johns. However, just like your attachments the children are just a further description of the parent entry.

Look at this way

Door 1 ID 100............attach A ID 100
..............................attach B ID 100
..............................attach C ID 100

Door 1 ID 101............attach A ID 101
..............................attach B ID 101
..............................attach C ID 101

Door 1 ID 102............attach X ID 102
..............................attach Y ID 102
..............................attach Z ID 102
..............................attach B ID 102
..............................attach C ID 102

Note that we have used attachments B and C three times but in each case they have a different ID and that ID is the same as the ID of the door.

That is the basic structure.

As Rabbie just said you then have queries. For example a simple query will change the above to:

Door 1 ID 100............attach A ID 100
Door 1 ID 100............attach B ID 100
Door 1 ID 100............attach C ID 100
Door 1 ID 101............attach A ID 101
Door 1 ID 101............attach B ID 101
Door 1 ID 101............attach C ID 101
Door 1 ID 102............attach X ID 102
Door 1 ID 102............attach Y ID 102
Door 1 ID 102............attach Z ID 102
Door 1 ID 102............attach B ID 102
Door 1 ID 102............attach C ID 102

and it can do that for all doors or some doors. It is now easy to find all the doors that have Attach A or all the doors that have Attach C AND Attach Z. In the latter case a query (or a macro or code) would tell us that only Door ID 102 has both C and Z attachments.

Queries, code or macro then manipulate the data and of course forms display the data. Apart from displaying the data the form also carries the code/macro actions....the buttons or labels or texboxes you click on.

Think of the tables as the foundations of the house. When the house is complete you can't see or feel the foundations. But the foundations determine what sort of house can be built.