Form creation all records not shown

[Jen0dorf]

Hi

My Database consists of three tables and a linking table, tbl graves, tbldeceased,tblmemorail and tbl memorialtablejoin.

having completed my tables and checked that the relationship data gives the correct data I decided to embark on a simple form to display all the data on a single form.

So I used the create Form option, selected add existing fields. All of my tables are shown in the fields list.

From tblgraves I selected plot and grave Id, from tbl deceased forename and surname and from tbl memorial the inscription field.

The form was created but on examination I realised that my total records was shown as 12000 records whereas tble graves and tbl deceased has 39000 records and tblememorial 17000 records


So I created the form using just tblgraves and tbldeceased and that shows my 39201 records.

If I leave form view and go back to design view and add the inscription record from tblMemorials I see the message box as per the attachment.

Not every grave or deceased has a memorial but I would expect to see my 39000 records

I've also attached a screen shot of my relationships

I cant see what direction to go in so any pointer would be helpfull

thanks

Ian

[NTC]

You are displaying the fundamental table relationships.

When making a query, in query design mode - one can modify these relationships in order to achieve the query results being sought; make a query and put your cursor right on the line itself and right click...then you want to choose the option for all graves records....it will result in an arrowed line pointing... repeat that for join lines to other tables and be sure the arrows all point the same way. A little experimentation and you will get the hang of it.

Once your query results in the correct records - then make a form based on the query.

[JamesDeckert]

The reason it's asking for the relationship is because you're leaving out the join table. You need to include tables that link other tables together along with any fields which the link is based on (fk_GraveID, fk_MemorialID, & memorialID from tblMemorial). If this doesn't fix the problem, then you'll need to check the join lines like NTC suggested. You'll want all records from tblGraves and matching records for the child tables.

It seems to me that your primary key for the join table should be the combination of the two foreign keys, not a new number (probably won't matter, but redundant).

Why are you splitting the dates into 3 fields in tblDeceased?
Why do you have age in tblDeceased instead of DOB?

[Jen0dorf]

Thanks for all the advice which I shall work on. THis is a retirement project to help my wife and her Family History group so it's a total learning curve for me!.

As to the dates being two separate fields I though the same and asked the question in the forum as was advised that Access does not like non US date formats and it could lead to confusion so I left it as it was . Also if someone searched for a year of Death or burial; it would make it simpler.

As to using a primary key combination again elsewhere I was told not a good idea.

I suppose it's similar to when I design a web site many ways to achieve the same end!

thanks

Ian
QUOTE=JamesDeckert;294924]The reason it's asking for the relationship is because you're leaving out the join table. You need to include tables that link other tables together along with any fields which the link is based on (fk_GraveID, fk_MemorialID, & memorialID from tblMemorial). If this doesn't fix the problem, then you'll need to check the join lines like NTC suggested. You'll want all records from tblGraves and matching records for the child tables.

It seems to me that your primary key for the join table should be the combination of the two foreign keys, not a new number (probably won't matter, but redundant).

Why are you splitting the dates into 3 fields in tblDeceased?
Why do you have age in tblDeceased instead of DOB?[/QUOTE]

[Jen0dorf]

Hi

Just a quick thanks - played with the three options as per above and now have my 39000 records.

SO much to learn so little time;-)

cheers

Ian