append query

[Jen0dorf]

Hi

Have a spread sheet with 1500 records and every record has one field exactly the same
the name of the Parish.

My database has two tables

parish and baptism based on a one to many realtionship.

The parish has an id field and the church has fkId

is there a way to run an append query to two tables or do I have to run two separate appends?

thanks

Ian

[vicsaccess]

sounds like more information is needed. if i understand right you have two tables, parish and baptism, both have a field called parish. what are you trying to update?

[Jen0dorf]

Hi

point taken I should have included the database :-(

Simply there are three tables

one for Parish

One for Births

these are in a relationship based on 1 to many as one parish can have many births

The third is an import from the original spreadsheet

I'm trying to append the contents of the" tbl_westburyBaptismsOne" to the other two tables whilst creating / retaining referential integrity between keys.

The reason why there is a field in tbl_Parish called form name is that I intend to use the access property of using the 1st field as in

Private Sub Search_Click()
If Me.Select_Parish <> "" Then DoCmd.OpenForm "frm_" & Replace(Me.Select_Parish, " ", "")
End Sub

No applicable to my current issue but thought I'd mention it.

I will have over the next few months 54 parishes each with up to 10 spreadsheets so I'm trying to get my methodology right with just one to start with and from there set up a trial form sequence.

IN case anyone wonders why this is the format it is a transcription of a 1600-1700 church birth register typed into a spreadsheet by hand ( not by me)


Thanks

Ian

[vicsaccess]

are you wanting to run the append through code or macro? not really understanding why you are updating tbl_parish though since you know that you will have 54 parishes this becomes a static known table to be referred to in the tbl_baptismone with the parish id. if this is the case all you need is one insert query.

[orange]

Ian,

With over 1400 records in your sample, have you identified the logic to take you from your raw data to your expected/desired output?

Do you have a plan to deal with no Birth year, no forename, no FicheNo... etc?

Forename with extra data
eg

Tamar (privately baptised 04 Jul 1802, received into church this day)

It seems to me there are a number of steps involved before final code.
Some decisions/trials for transformation; some testing to show/prove/disprove viability of the option etc.

Just some thoughts for consideration.
Good luck.

[Jen0dorf]

Hi

thanks for the thoughts.

Yes I have ghosted out "The front end" and the only data that will be searched for in reality is either the year of baptism or the surname. All other information is needed or may be needed if the record is used in research. The exisiting users simply load up each spreadsheet in turn and search the year colum for say 1654 and then look at those records.

As to the records with no no Birth year, no forename, no FicheNo. they are as was entered in the register and to a researcher it's non apearance is informative.

However later parish records contain more data.

I have ghosted up a crude front end and shown it to the committee ( yep I'm dealing with a committee of volunteers) and they approve the concept.

All that being said back to my original question which I may have confused every one and my self ;-)

I want to split the spreadsheet over two tables so that the parish is in one table and the entries are in the other.

I can run two append queries to populate the two tables but an at a loss as to how to setup the foreign key in the Baptism table. I do appreciate that at the moment the parish table contains only one parish Westbury but I have 54 to import into the database.

I wondered it there was a way to run an update query to populate two tables and create the refererntial integrity

Hope thats clear

cheers

Ian

[orange]

Ian,
Re: ghosted up a crude front end

Here's a link that I have recommended to others re: the approach to getting tables and rules aligned/matching the business needs. It's one I have used many times. It may be helpful, if only as a reference to help put things into context.

Good luck.