excel conversion/import/manipulation

[Jen0dorf]

Hi

would like to pick a few brains

The attached spreadsheet contains details of marriages and I have imported it into a table.

However nothing is that simple and |I realised I have missed one basic requirement in my design structure.

The spread sheet contains a column called entry - each entry no relates to two rows in the spread sheet

one for the groom and one for the bride. Alternate rows have the entry number so the brides entry no is always empty

When I import into access each record is equivalent to a row as I would expect but when I set-up a search form I need it to show both groom and bride in the results.

So my thoughts are I have the following options

in excel merge the two records- if possible

in excel manually go through and type in the entry no for every bride

In access merge the two records if possible

In access create a table for brides and a table for grooms and link them

I suspect I am over thinking this so would appreciate any thoughts



cheers

Ian

[ranman256]

relational data does not work this way. (not positional)
you need a MARRIED field to join the 2 people.
this code could be derived from witnesses.

you don't need 2 tables/brides grooms. They are all people in 1 table, but a field to determine which BRIDE or GROOM.

[Jen0dorf]

Hi

thanks for that

off to have a look

cheers

Ian

[rpeare]

Are you looking to convert your entire dataset to access and looking for a way to do it in a correct structure, or is this a one time deal to do some analysis?


If you are looking to convert your whole structure to Access you can import this dataset using code as long as you have a well defined structure, you'd just have to use code to rip it apart into it's component pieces rather than relying on the import wizard.

if you're looking for a structure and this is the entirety of your data (in terms of columns) then I would have something like

tblChurch
CH_ID  CH_Name       CH_Address ----> other church related fields
1      Not Specified  
2      BlahBlah      1313 Mockingbird Lane

tblMinister
MI_ID  MI_FirstName  MI_LastName ----> minister specific fields
1      E             Foster

tblMarriage
M_ID  CH_ID  FicheRef        PageNo  EntryNo  DateofMarriage  MarriageNum  MI_ID
1     1      D/P WBY 2/1/7   1       1        3/16/2016       1813         1

tblRole
R_ID  R_Desc
1     Bride
2     Groom
3     Witness - Bride
4     Witness - Groom

tblCondition
CO_ID  CO_Desc
1      Bachelor
2      Spinster
3      Widower
4      Widow

tblMarriagePeople
MP_ID  MP_FirstName  MP_LastName  R_ID  CO_ID
1      James         Phelps       2     1
2      Mary          Wall         1     2
3      Joseph        Carver       4
4      Jacob         Dix          3
5      Charles      Dix           3

All of this can be programmatically pulled out of the dataset you show I'm just confused because you have a couple of fields that look like they're coming from another database (fk_parishID, fMarriage).

[Jen0dorf]

Hi

thanks for that, it's an ongoing project to convert all the spreadsheets - one for each parish into a database.

So if I can get one doen it's then just a case of repeating for all 58 others.

I've already converted the births and deaths but the marriages are the problem because of the two entries for each marriage.

In reality I suppose I need to add additional fields in the ist record to record the brides data it is only the forename, surname, abode and parents name

food for thought

cheers

Ian