display data from multiple records in a query based form

[Jen0dorf]

Hi

in the light of various issues I decided to rewrite my database - not a waste of time and I have learnt much.

So with my new found knowledge I realised that the tables I was provided with are flawed and as suspected all the relationships etc were contained in a compiled vb file.



on examination i realised that some of the entries in the memorial field - see attached database - were fairly useless as they related to an adjacent record.

ie an entry reads "And his wife Edna" without seeing the other part that is unusable

The database will allow users to search for a year of death or a surname this runs a query which displays all records that fulfill that criteria, clicking on a particular record runs a second query displaying all the information relating to that person. I have those forms created, tested and working.

When that second query is run It already shows all the data from that specific record but I need it to show all the memorials relating to that Mlink ie look up all the memorial fields with the same Mlink and the selected record in the first form, concatinate all those memorial fields and display it in the second form.

So I formulated two plans to solve this issue

1. to manualy view the records until I found a pair - some have up to six and then copy and paste all the memorial fields with a line between each memorial and then paste the new memorial into all 2,3,or 4 records.

However with 39201 records this is a mammoth task.

Is there a way to automate this?

2. My second plan was use my second query (that show the detail) and use some form of macro to populate the memorial field with the entries from the memorial fields of all records with the same Mlink

I'm really open to suggestions as I dont know if either solution is practicable

As usual

Many thanks

Ian

[Jen0dorf]

HI

forgot to say that Mlink was the original designers way of designating one family. So each Mlink relates to all members of family in the Graves table

cheers

Ian

[ssanfu]

1. to manualy view the records until I found a pair - some have up to six and then copy and paste all the memorial fields with a line between each memorial and then paste the new memorial into all 2,3,or 4 records.

However with 39201 records this is a mammoth task.

Is there a way to automate this?

I would use VBA to do this.


I looked at the dB you posted.. In that table, I didn't see a "memorial field", so I'm kinda confused there.

[Jen0dorf]

Hi

thanks for the reply.

I see you recommend VBA - I have zero experience so here goes the learning curve-!!

As to the fields in the database I posted Mlink is the memorial field and inscription is the memorial inscription.

The Mlink is the link to a family group not necessarily the same surname - marriages etc So all records with the same Mlink relate to the same family group.

When you say use VBA would you suggest that I use VBA to modify the text and in my first thought or use VAB to pull the information from the other records and display that retreived information on my form?

Sorry if the question seems stupid but I'm trying to learn

thanks

IAn

[ssanfu]

in the light of various issues I decided to rewrite my database

Haven't seen the original dB, so based on the quote and the question you asked, I would use VBA to do the searchs and updates.

It sounds like the "Mlink" field is the linking field between two (or more) flat files.
Have you drawn out an ERD or used a whiteboard to figure out the relationships? Are you going to change the linking fields from text to long integers?

The Mlink is the link to a family group not necessarily the same surname - marriages etc So all records with the same Mlink relate to the same family group.

But the grave site can have multiple family groups?