0

breaking my head about this and searching as far as I could, to no avail...

What I have:

Two XLS Sheets to be imported into one Access DB.

Data is structured the following way:

Table 1 - Master Data (one row per Submission)

User Project Date From Date To

Table 2 - Detail Data (up to ten rows per Submission)

Date Start Time End Time ....

While those two tables are imported into the same Access db, they are going into different db tables, so I Need a master key to link the master to ist subsequent Detail data set.

What I am therefore looking for is to generate a GUID which will be attached to both data sets and can be used for joining the sets in Excel.

I have been trying both, to generate an ID in Excel (VBA or formula) or add it when importing into acccess, when importing, have not found a proper way to achieve this.

Maybe there's an alternative Approach to my requirement, curious for ideas.

Thanks!

Community
  • 1
  • 1
BorisTheBlade
  • 1
  • 1
    This is tought to answer since, if there isn't already a relationship between your two data sets... it's difficult to say how you would establish one. Is the relationship established by the DateFrom and DateTo? – JNevill Dec 04 '14 at 16:46
  • Excellent Point. The relationship is established by the unique combination of Project, user and date(s). For Example: Project ABC / User Boris / Stayed in: Los Angeles; Day 1 From X to Y, Day 2 from Y to Z. – BorisTheBlade Dec 09 '14 at 13:57

1 Answers1

1

I've had luck with the following:

Public Function GenerateGUID() As String 
    GenerateGUID = Mid$(CreateObject("Scriptlet.TypeLib").GUID, 2, 36) 
End Function

(Found here: https://stackoverflow.com/a/23126614/1240745)

Community
  • 1
  • 1
Tim Hall
  • 1,475
  • 14
  • 16