Table design query

[oli82]

Hi,


I am designing this database I have decided that using the row entry route for date is the way to go. However I have a couple of questions as it stands I have tables like so:

Analytes lookup table

ANALYTE_ID​

CLASS​

ANALYTE​

ANALYTE_FULL​

DESCRIPTION​

1​

ALKANES
iC13
isoprenoid C13

2​

ALKANES
iC14
isoprenoid C14

Sample Data Table
(column is numerical)

SAMPLE_ID​

HEADER_ID​

USER_BIBLIO_ID​

ANALYTE_ID​

SAMPLE_VALUE​

1​

1​

12233​

1​

456​

2​

1​

12233​

2​

56​

3​

1​

12233​

3​

0.001​

4​

1​

12233​

4​

233​

My question is what do I do for values that are non numerical, where the value can be High or Low for example or a range of text values. My first though would be to use a lookup ID in the sample value field. How doable is this, would you then encounter issues when trying to crosstab query the data, also I would need to use a subform for each analyte with its own lookup or an actively modifying lookup piece of VBA.

My other questions is when there is a comments field for example – I think at the last count there are 5 comments categories which are free text is it going to be easier to have a table similar to the Sample Data Table but with Sample_Value being a text field?


Any experience would be greatly appreciated.


Cheers,


Oliver

 

[oli82]

Sorry didnt format right picture attached.

Very Sorry

 

[oli82]

I am asking if its possible to put up lookup numerical id's in the field and then have a lookup table with id, which includes low or high or med - for example.

Thanks for the help.

 

[oli82]

Pat,

Many thanks for this, have managed to sort something with VBA looks like below. Do you foresee any problems with this method.

Thanks for your help.

Oliver



Private Sub ANALYTE_ID_AfterUpdate()

Select Case Me.ANALYTE_ID

Case 5

Me.SAMPLE_VALUE.RowSource = "SELECT dbo_V_R_KEROGEN_TYPE.ID, dbo_V_R_KEROGEN_TYPE.KEROGEN_TYPE FROM dbo_V_R_KEROGEN_TYPE ORDER BY dbo_V_R_KEROGEN_TYPE.KEROGEN_TYPE;"
Me.SAMPLE_VALUE.ColumnCount = 2
Me.SAMPLE_VALUE.ColumnWidths = "2;5"
Case 1

Me.SAMPLE_VALUE.RowSource = "SELECT dbo_V_R_coal_rank.ID, dbo_V_R_coal_rank.coal_rank FROM dbo_V_R_coal_rank ORDER BY dbo_V_R_coal_rank.coal_rank;"
Me.SAMPLE_VALUE.ColumnCount = 2
Me.SAMPLE_VALUE.ColumnWidths = "2;5"

Case Else
Me.SAMPLE_VALUE.RowSource = ""
Me.SAMPLE_VALUE.ColumnCount = 2
Me.SAMPLE_VALUE.ColumnWidths = "2;5"

End Select


End Sub

 

[oli82]

After the cross tab querying of the data into columns I would run a select query which link each column with its representative lookup table together to produce the end result. Does this sounds like it will work ok.

Cheers,

Oliver