Data comparison and alignment

[creeping]

Hi

I have a set of data that is stored in 1 table 'Grass_data'. There are multiple sets of data in the table (18 to precise) each with a x and y value (size and height). The hard part for me is that I need to make comparison of one sample against another by matching peaksize from 1 sample to another. Think this would be an easy task if the peaksize were all the same values but they are not. To make the situation worse some samples do not have a 1 to 1 match with the peaksize field.

I will explain the comparison situation better by an example.

if I had two sets of data:


ID Peak_Size Peak_Height
1 118.49 79
1 119.25 73
1 121.01 62
1 121.99 90
1 123.98 73
1 131.58 99

ID Peak_Size Peak_Height
2 118.59 77
2 119.51 73
2 122.10 91
2 123.89 74
2 131.65 98

What I need to have happen is get the 2 data set to align so that mathematical operations can then be carried out on the data. In the above situation you will notice that data set 2 has 1 less piece of data (peak 121). In this case I still need the data to come through from dataset 1. Also you will notice that the peak sizes can only be matched by finding the closest number i.e within 1 integer. There will never be two numbers within 1 integer to match up.

The result that I would be hoping for from these two dataset would be a like so:

ID PS_1 PH_1 PS_2 PH_2
12 118.49 79 118.59 77
12 119.25 73 119.51 73
12 121.01 62 0 0 ***
12 121.99 90 122.10 91
12 123.98 73 123.89 74
12 131.58 99 131.65 98


*** note the zero against the set of Peak sizes of 121.01 in sample 2 as there is no data

Sorry to ask for so much help but I just can't figure out how to complete this, I'm guessing that you will have to use the Visual basic part of access to complete this (which I am not to good at) I have attached a access 97 database with the data if it helps which you can convert to 2000 or 2002 if it helps you.

I would be very grateful if someone could help me out as I need this for my school work and I need to process about 200 of these in the long run!

 

[Fornatian]

Try this as an SQL query

SELECT Grass_data.ID, Grass_data.Peak_Size, Int([Peak_Size]) AS Rounded
FROM Grass_data
ORDER BY Int([Peak_Size]);

From here you can build queries for each ID and them using the Rounded field as the joining expression, it's quite complicated and long winded so you may just choose to export the lot to excel-now in that environment I could give you a quick solution to your data conversion issue.

 

[creeping]

problem with that method is that when you round the number it goes down to the nearest integer. What needs to happens is that it finds the nearest peak number within 1 whole number?

 

[creeping]

Thanks anyway

 

[Fornatian]

When you say...

What needs to happens is that it finds the nearest peak number within 1 whole number?

Do you mean +/- 0.5(mid) or +/-1(fwd/bkwrd)

Sorry to sound confused, what is the end result you are looking for and why? This will help me assist you better.

 

[creeping]

An example:

IF I had 2 tables

ID Num
1 1.22
2 2.12
3 0.16
4 8.56
5 9.6

ID Num
1 1.96
2 2.51
3 8.86
4 10.0
5 0.86

I would want to match:

1.22 - 0.86 only not 2.12 as well
2.12 - 1.96
0.16 - nothing
8.66 - nothing
9.6 - 10.0

i.e the closest number within the range of 1. I don't want all the numbers within 1 just the nearest number

 

[Nouba]

Hi,

i assume Fornatian's first suggest (+-0.5). Maybe you have to correct these rows inside the query. It gave me 6819 tuples as result. I leave empty fields inside, which can by changed with the Format property inside the query. It seems to be not so bad having the data inside a table for further using. Change the query type to a maketable query, give it a name and execute the query. After that you can create appropriate indexes in the table and you're all set.

SELECT
  G1.SID AS G_1
, G2.SID AS G_2
, G1.PS AS PS_1
, G1.PH AS PH_1
, Max(IIf(G1.PS Between G2.PMIN And G2.PMAX,G2.PS,Null)) AS PS_2
, Max(IIf(G1.PS Between G2.PMIN And G2.PMAX,G2.PH,Null)) AS PH_2
FROM [SELECT
    G.SID
  , G.PS
  , [PS]-0.5 AS PMIN
  , [PS]+0.5 AS PMAX
  , G.PH
  FROM tblGrass AS G
  ORDER BY G.SID, G.PS]. AS G1
, [SELECT
    G.SID
  , G.PS
  , [PS]-0.5 AS PMIN
  , [PS]+0.5 AS PMAX, G.PH
  FROM tblGrass AS G
  ORDER BY G.SID, G.PS]. AS G2
WHERE G2.SID>G1.SID
GROUP BY
  G1.SID
, G2.SID
, G1.PS
, G1.PH
ORDER BY
  G1.SID
, G2.SID
, G1.PS

 

[creeping]

Hi Nouba I tried using your query in Access but It not quite right.

1) I need the match to be unique i.e Once a number has been matched neither of the numbers can be matched with other numbers

2) You have got the blank cells right on the second selected sample but this occurance blanks also happen on the first sample.

Sorry if my explanation was bad. I have Attached a excel sheet with a sample matchup between 1 and 6 that shows how the sample should work. I have compared it against the access created one from your SQL.

I sure that a Basic Modules is needed here as Access SQL statement cannot do this.

If you could held me out that would be so great as I not sure how to complete this -Thanks heaps you have got the closest so far

 

[creeping]

Sorry here is the excel file

 

[Nouba]

Possibly that is what you want.

 

[creeping]

Thanks for all your hard work on that. It really cool that you have been able to do this with SQL. There are still a couple of little things I don't know how to fix.

I need to have the range a +1 -1 To get the right answer. Currently you have used +-0.5 which sometimes works but not all the time. Have a look between sample 6 and 10. Look at this part once the range has been increased to 1

197.65 1079 197.76 543
197.65 1079 196.80 543
197.65 1079 197.76 443

Multiple returns. Is this because I increased the range?

Thanks again