update last record

[Jen0dorf]

Hi

I'm trying to create an update query with the criteria that the last/newest record will be updated.

UPdates will be rare at most one a month and often none.



Is this possible?

thanks

Ian

[June7]

What determines the 'last/newest' - a date or ID?

This seems an odd requirement. Why is it needed?

[JamesDeckert]

sort by the field which determines last record in reverse order then choose the top (ribbon) 1.

[Jen0dorf]

Hi

I think I've dug myself into a hole having got the database working. It's searching how I want and displaying search results and detail.

However despite my best intentions I am unable to add a new record accross the four tables each record uses.

Despite several ideas from the forum I have failed to have any sucess - my own stupidy no doubt.

I then had a revalation and created four update queries based on a new table called tblupdate

SO this new table had all the fields and I could of course add a new record.

I then googled, read up and attatched this code to a button:

DoCmd.OpenQuery "NewRecordGraves"
DoEvents
DoCmd.OpenQuery "NewRecordDeceased"
DoEvents
DoCmd.OpenQuery "NewRecordMemorial"
DoEvents

And it worked, populated the tables in the last record of each table

Hooray! success but.....

I forgot the foreign keys which were not updated. So I typed the fks in by hand and all worked, I then created another query to update one fk from the main key which was an autonumber but this is not work because I needed it to update the last or newest record.

So I thought set the update criteria to either the last record or the newest record - updates will be one a month at most
Each of the tables has an autonumber ID field butI couldn't work out how to use that as a criteria

Hence my question, hope this is clear

THanks

Ian

[Homegrownandy]

Ian, Can you explain to me how the memorials work. What I want to know is..

Are they assigned only to grave plots?
are they assigned to individuals?

that's the complicated bit. Ill try and come up with a solution for you.

[Jen0dorf]

You are correct it's the memorials that are complicated and Im still not sure I've got my head around it!.

A memorial is attached to a plot ie where the memorial stone is.


IN that grave there can be a husband and wife so the memorial stone can have two memorials on it relating to Husband and Wife

IN a few cases if it's a family grave plot then their can be even more.

However in that same grave there can be multiple deceased not related so there can be more than one memorial stone to each grave.

The final complication ( I think) is that if for example a window dies and there is no room in the husbands grave she can be buried elsewhere in the cemetery but her memorial can be with her husbands on his grave.

So my thought was

Each grave can have multiple deceased
Each Grave can have multiple memorials
Each memorial can apply to multiple Graves

All rather confusing

cheers

Ian

[JamesDeckert]

Ian,
What is your database supposed to track? Burials, Memorials?
I'm assuming this is for a cemetery, I'm surprised they need to know about each stone.
In the U.S. (at least in Kansas) a family plot may or may not have a family stone with just the family name. Then each grave may have it's own marker for an individual. Or a couple could have a monument containing the husband and wife info. It is also possible in this situation to have a marker (usually a military marker) in addition to the monument. The marker is usually at the other end of the grave from the monument, or the bronze marker mounted on the back of the monument. There is a lot of flexibility between cemeteries, and I assume different parts of the country/world are different. My dad owned a monument business and I helped him for several years.

[Jen0dorf]

Ian,
What is your database supposed to track? Burials, Memorials?
I'm assuming this is for a cemetery, I'm surprised they need to know about each stone.
In the U.S. (at least in Kansas) a family plot may or may not have a family stone with just the family name. Then each grave may have it's own marker for an individual. Or a couple could have a monument containing the husband and wife info. It is also possible in this situation to have a marker (usually a military marker) in addition to the monument. The marker is usually at the other end of the grave from the monument, or the bronze marker mounted on the back of the monument. There is a lot of flexibility between cemeteries, and I assume different parts of the country/world are different. My dad owned a monument business and I helped him for several years.

Hi

The database is to allow researchers to search for a family ancestor, - like the US/BBS program "Who do you think you are"

You normaly search by year of Death or Surname. so the database is used to locate either of these and from there date of death, full name and any memorail etc. The memorials are important because they have clues to other relatives and wives etc.

In my posts I have used the term memorial as a generic term for markers, plaques, stones etc. Of course a grave does not have to have any monument/marker.

Here in the UK most graves I believe are "used for 75 years" then can be used again, of course there are exceptions. The cemetery I'm writing this for is full so new burials are rare.

The 39201 records were given to me in a flat database with little or no search facility hence my re-write.

I did like your suggestion of re ordering the table so that the last record is at the top and I implemented it no problem.

I'm now trying to modify my update query to update record one but after a days research and trial I've failed

However tomorrows another day

thanks for all the help

Ian

[June7]

I am confused. Your OP said need to UPDATE last/newest record, what you really seem to be doing is INSERT new records.

This statement "I am unable to add a new record accross the four tables each record uses" expresses a reasonable situation.

A form can do data entry for only one table. It follows that an SQL INSERT can work on only one table.

If you are inserting records into master and dependent tables, then the master must be committed first, then generated PK must be retrieved for saving with the new dependent records as FK.

Why are you not using form/subform arrangement for data entry/edit?

[Jen0dorf]

Hi

simple answer is I hadn't considered using a form subform arrangement. I'll investigate that option and see how I get on.

I did use an insert query to add new records but that did not update fks so I thought a way around it was to use an update query to copy the fk- clearly not a good idea!

thanks for the input

Ian

[June7]

Use filter criteria Is Null under the FK field to restrict update to the newly inserted records. Real trick is pulling the newly created PK value to use as FK. Or pull the FK and include it with the INSERT actions for the dependent records. All can be done with VBA that constructs the SQL action statements - not opening saved Access objects.

[ssanfu]

From post #4

Hi

<snip>

I then had a revalation and created four update queries based on a new table called tblupdate

SO this new table had all the fields and I could of course add a new record.

I then googled, read up and attatched this code to a button:

DoCmd.OpenQuery "NewRecordGraves"
DoEvents
DoCmd.OpenQuery "NewRecordDeceased"
DoEvents
DoCmd.OpenQuery "NewRecordMemorial"
DoEvents

And it worked, populated the tables in the last record of each table

Hooray! success but.....

I forgot the foreign keys which were not updated. So I typed the fks in by hand and all worked, I then created another query to update one fk from the main key which was an autonumber but this is not work because I needed it to update the last or newest record.

So I thought set the update criteria to either the last record or the newest record - updates will be one a month at most
Each of the tables has an autonumber ID field butI couldn't work out how to use that as a criteria

Ian

An alternative........

So somehow you know what the FK should be and you have the data to append the data to the 4 tables somewhare.
You could do the appends using VBA.
Convert the saved append queries to SQL, add the data to the SQL statements..... You can even build the SQL on the fly if some data is not available at the time you are adding records.


For example, I use VBA to import, validate & process data from CSV (text) files.
Due to the data validation, calculations, update code, append code, etc, the code for this procedure (with comments) is about 56 pages long.

[Homegrownandy]

A junction table between deceased and memorials I would suggest.

so everything is relative to the graves... memorials are only relative to graves, and deceased are only relative to the graves.. then your junction table links these two...your junction table will just be the primary keys from other tables like..


Join_ID - Memorial_ID - deceased_ID
1-5-4
2-5-5

you don't need any grave information there at all, its already been defined at memorial level and deceased level.

So how does that work on forms?

I can only suggest what I thinks best, other people may disagree or improve it.

have a big list form for all your graves.. possibly with a search if you need it.

the user selects the grave, it opens that in tabular with more detail and a subform to add details for the deceased.

you always have a deceased before a memorial, and your grave plots are defined from the start (im assuming).

so entering data that way should allow you to work with it later with a 'memorial manager' form.

see what you think/how you do with that before we entertain that haha.

[Jen0dorf]

THanks,

off to have a try

regards

Ian

[Jen0dorf]

THanks

sounds good to me as does Mandarin and Japanese ;-) But I'm off to continue the learning curve.

thanks

Ian