Pairing records from excell

[Jen0dorf]

Hi

would apprecate some ideas as to how to pair records.

I have been asked to create a database for marriage bans. I have the data in an excell file with the following colums

ficheno, page no, entry number, date, year, forename,last name, condition, occupation, minister.

Ok no problems creating and inputing the data to a table and creating a form to show the data and add a search button.

However I have realised that in the spread sheet

Line one and line two are "a couple" as are 3 and 4 and 5 and 6 etc etc.

So on my form I would like the details from a pair to be shown , similalry when a search is done by surname I'd like the results to show the pair.

Wouls appreciate some pointers

thanks

Ian

[mrmmickle1]

Do any of these fields demarcate the relationship between the couples?

ficheno, page no, entry number, date, year, forename,last name, condition, occupation, minister

For example is ficheno or page no duplicated for these two records? ---> Letting you know that these two records are associated as 1 ceremony/marriage

[Jen0dorf]

Hi

thanks for the reply, regretably the answer is no.
Simply there is a colum marked entry number followed by an empty field

I've pasted some lines below as an example

As you can see each record/ marriage is made of of two lines in tejh spread sheet

thanks

Ian

FicheRef	PageNo	EntryNo	Date	Year	ForeName	SurName	Condition	Occupation	Abode	Minister	Notes
D/P AX 2/1/3	1	1	15,22 & 29 Sep	1754	John	Baker		Shoemaker	Axbridge	Tuthill
D/P AX 2/1/3					Avis	ROWSEL	spinster		Axbridge
D/P AX 2/1/3	1	2	20,27 Oct & 3 Nov	1754	James	SWERSE		Husbandman	Axbridge	J Tuthill & E Rebotier
D/P AX 2/1/3					Francrs	TANNER	spinster		Axbridge

[alansidman]

I would amend the Excel spreadsheet to place the spouse (2nd record) on the same line as the first.

This could be done with a simple loop macro. Assume that the second records are in column F, G and H of your spreadsheet.

After running the following VBA script, then reimport into Access

Sub Bans()
    Dim i As Long
    Dim lr As Long
    lr = Range("F" & Rows.Count).End(xlUp).Row
    Range("M1") = "SpouseForename"
    Range("N1") = "SpouseSurName"
    Range("O1") = "SpouseCondition"


Application.ScreenUpdating = False
For i = 2 To lr
If Range("E" & i) = "" Then
Range("F" & i & ":H" & i).Copy Range("M" & i - 1)
End If
Next i
Application.CutCopyMode = False


For i = lr To 1 Step -1
If Range("E" & i) = "" Then
Range("E" & i).EntireRow.Delete
End If
Next i


Application.ScreenUpdating = True
Msgbox "completed"


End Sub

[Jen0dorf]

Hi

Many many thanks for the code -it's a bit over my knowledge level at the moment but I'll use it and try to understand

cheers

Ian

[alansidman]

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

[Jen0dorf]

Thanks a lot

Ian