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

 

[Pat Hartman]

If you are asking - "can you store the text words LOW or HIGH in a numeric field" - the answer is no. You will need to change the data type to text (which will cause a raft of other problems) or assign arbitrary numeric values that you can interpret as LOW or HIGH.

 

[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.

 

[Pat Hartman]

If I understand the question, that won't work either. Combo's have two bound objects. One is the ControlSource which is the table field in which the value is actually stored. You would end up with two arbitrary values that you say mean high or low and the rest of the values would be actual numeric values. The second bound object is the RowSource. The RowSource is the list of items that may be selected from the dropdown. The RowSource in your case would have only the two values for high and low and could not possibly contain all the values that are actually possible. That means that for rows where you have selected the high or low value, the control would show the words high or low but for all other values, the control would be blank because the value contained in the ControlSource does not coorespond to any value of the RowSource so the display field could be found.

Combos only work for a discrete list of values and if the ControlSource contains a value that is not included in the RowSource, the control will appear blank even though it does actually contain a value.

 

[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

 

[Pat Hartman]

You still have to store the data in the same column. How would you distinguish one set of values from another?

 

[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

 

[Pat Hartman]

No. There is no way to distinguish "real" values from lookup values.