for loop in access 2007

[tiziana]

dear all, this is my first thread and, if I mistake the session, I apology myself.


I've a problme: I don't know how to do this.

I've got 5 tables:

table_1, where there are 3 fields: insertion_data1, serial_number, feature1;
table_2 (matrix sizes: Ix3), where there are 3 fields: insertion_data2a, insertion_data2b, serial_number, feature2;
table_3 (matrix sizes: Mx3), where there are 3 fields: insertion_data3, serial_number, feature3;
table_4 (matrix sizes: Nx3), where there are 3 fields: insertion_data4, serial_number, feature4;
table_5 (matrix sizes: Zx3), where there are 3 fields: insertion_data5, serial_number, feature5.

The serial_number is the same in each table and each table has more than 1 row, because the features change in time.

I've to make a last table, where there is the following fields:

insertion_data1
serial_number
feature1
the i-th feature2, when insertion_data2a<= insertion_data1 <=insertion_data2b
the m-th feature3, when min(diff(insertion_data3,insertion_data1))
the n-th feature4 when min(diff(insertion_data4,insertion_data1))
the z-th feature5, when min(diff(insertion_data5,insertion_data1))

I thought to create a macro, so constitued:

1) a query to create the table_6 with these fields: insertion_data1,serial_number,feature1,the i-th feature2

2) a function in VB where there are 3 "for loops" which find:

the m-th insertion_data3 where min(diff(insertion_data3,insertion_data1)),

the n-th insertion_data3 where min(diff(insertion_data4,insertion_data1)) and

the z-th insertion_data3 where min(diff(insertion_data5,insertion_data1))

and this function returns the table_7, with this fields: insertion_data1,m-th insertion_data3,n-th insertion_data4,z-th insertion_data5

3) a query which returns the table_8, which iassembles:

insertion_data1,
serial_number,
feature1,
the i-th feature2,
m-th feature3,
n-th feature4,
z-th feature5.

So, I wrote the query 1) and query 3, but I don't know:

A) to write the function in VB: I don't know how it is possible to create the table_ with the results of each "for loop";
B) if it is possible to write a macro which compiles the pts. 1), 2) and 3);
C) it the tables_6 and table_7 are generated automatically and automatically updated, everytime I run the macro.

thank you so much

tiziana

[pbaldy]

Welcome to the site. It could be that nobody has answered because they don't really follow what you're trying to do (I don't). Perhaps you can post some sample data and the result expected from that data?

[Dal Jeanis]

Forget all your functions, this could be done in a single SQL. (I'll write a few simple queries instead, so you can follow how it works.)

To simpify the look of the code, I've shortened all the names like this -

tbl1        (Was Table_1)
   SerNo,   (was Serial_Number)
   D1,      (Was Insertion_Data1)
   F1       (Was Feature1)

tbl2
   SerNo,
   D2A,
   D2B,
   F2

tbl3
   SerNo,
   D3,
   F3

(same changes as tbl3 for tbl4 and tlb5)

Here's the query that will get you the feature, if any, in a range in tbl2. I asssumed it was possible for there to be no range match, which is why I'm using a left join. If "feature" is not a text field, then change the NZ function to numeric as appropriate.

Query QRng2
   SELECT tbl1.SerNo, First(NZ(tbl2.F2,"")) as MinF2 
   FROM tbl1 LEFT JOIN tbl2 ON tbl1.SerNo = tbl2.SerNo
   WHERE tbl1.D1 BETWEEN tbl2.D2A AND tbl2.D2B
   GROUP BY SerNo;

Here's the queries that will get you the features 3-5 with the smallest differences.

Query QMin3
   SELECT tbl1.SerNo, First(NZ(tbl3.F3,"")) As MinF3
   FROM tbl1 INNER JOIN tbl3 ON tbl1.SerNo = tbl3.SerNo
   WHERE ABS(tbl1.D1 - tbl3.D3) IN 
      (SELECT MIN(ABS(t1.D1 - t3.D3))
      FROM tbl1 AS t1 INNER JOIN tbl3 AS T3 ON t1.SerNo = t3.SerNo
      WHERE t1.SerNo = tbl1.SerNo)
   GROUP BY SerNo;

Query QMin4
   SELECT tbl1.SerNo, First(NZ(tbl4.F4,"")) As MinF4
   FROM tbl1 INNER JOIN tbl4 ON tbl1.SerNo = tbl4.SerNo
   WHERE ABS(tbl1.D1 - tbl4.D4) IN 
      (SELECT MIN(ABS(t1.D1 - t4.D4))
      FROM tbl1 AS t1 INNER JOIN tbl4 AS T4 ON t1.SerNo = t4.SerNo
      WHERE t1.SerNo = tbl1.SerNo)
   GROUP BY SerNo;

Query QMin5
   SELECT tbl1.SerNo, First(NZ(tbl5.F5,"")) As MinF5
   FROM tbl1 INNER JOIN tbl5 ON tbl1.SerNo = tbl5.SerNo
   WHERE ABS(tbl1.D1 - tbl5.D5) IN 
      (SELECT MIN(ABS(t1.D1 - t5.D5))
      FROM tbl1 AS t1 INNER JOIN tbl5 AS T5 ON t1.SerNo = t5.SerNo
      WHERE t1.SerNo = tbl1.SerNo)
   GROUP BY SerNo;

Each of those queries will return exactly one record per serial_number in tbl1. You should test each one independently to make sure that it is pulling the data you want.

And here's your final query:

SELECT 
   tbl1.SerNo,
   tbl1.F1,
   QRng2.MinF2,
   QMin3.MinF3,
   QMin4.MinF4,
   QMin5.MinF5
FROM
((((tbl1 INNER JOIN QRng2 ON tbl1.SerNo = QRng2.Serno)
         INNER JOIN QMin3 ON tbl1.SerNo = QMin3.Serno)
         INNER JOIN QMin4 ON tbl1.SerNo = QMin4.Serno)
         INNER JOIN QMin5 ON tbl1.SerNo = QMin5.Serno);

Once that's tested, you can change it into a MakeTable query and you're done.

[tiziana]

Hi I did as you had said but it didn't work.
I explained me worst.

In the first query, the primary key is D1+SerNo.

And when I calculate the diff, I'm looking for the D3m which is the nearest to D1.

What is wrong?

[Dal Jeanis]

1) Are you saying that in Table 1, there can be more than one record with the same serial number, and thus a serial number can have more than one Description?

2) Please post the exact field names for Table 1, Table 2, and Table 3. (Ignore Table 4 and Table 5, which should work the same as table 3.)

3) Yes, MIN(ABS(D1-D3)) gets you the lowest absolute difference between D1 and D3.

[tiziana]

1) yes, because in Table 1, there's a field which is a counter and is univocal. Also other tables have a counter as primary key. There is a Table 0 where the primary key is the SerNo.
2) I don't understand. The only modifications are: F1, test; F2, job; F3, change. The other fields are called as I wrote on Tuesday.

[Dal Jeanis]

Then you need to add an autokey field to each table in order to make it work. There's currently nothing to refer uniquely to a record in each table, and the SQL must be able to uniquely identify which record is being sleected.

Let me think about this, and get back to you at lunch.

[Dal Jeanis]

1) Add an autokey field to table_1, call it PK1. Your tables will look like this:

table_1
  PK1
  serial_number
  insertion_data1 
  feature1

table_2 
  serial_number
  insertion_data2a 
  insertion_data2b 
  feature2

table_3 
  serial_number
  insertion_data3 
  feature3

Open new queries in SQL view and post these in with these names:

Query QRng2:
   SELECT table_1.PK1 AS Q2PK, First(table_1.serial_number) AS Q2SerNo, First(NZ(table_2.Feature2,"")) as Q2Feature2
   FROM table_1 LEFT JOIN table_2 ON table_1.serial_number = table_2.serial_number
   WHERE table_1.Insertion_data1 BETWEEN table_2.Insertion_data2A AND table_2.Insertion_data2B
   GROUP BY table_1.PK1;

Query QMin3:
   SELECT table_1.PK1 As Q3PK, First(table_1.serial_number) As Q3SerNo, First(NZ(table_3.Feature3,"")) As Q3Feature3
   FROM table_1 INNER JOIN table_3 ON table_1.serial_number = table_3.serial_number
   WHERE ABS(table_1.Insertion_data1 - table_3.Insertion_data3) IN 
      (SELECT MIN(ABS(t1.Insertion_data1 - t3.Insertion_data3))
      FROM table_1 AS t1 INNER JOIN table_3 AS T3 ON t1.serial_number = t3.serial_number
      WHERE t1.serial_number = table_1.serial_number)
   GROUP BY table_1.PK1;

Query QFinal:
SELECT 
   table_1.PK1,
   table_1.serial_number,
   table_1.Feature1,
   QRng2.Q2Feature2,
   QMin3.Q3Feature3
FROM
  ((table_1 INNER JOIN QRng2 ON table_1.PK1 = QRng2.Q2PK)
            INNER JOIN QMin3 ON table_1.PK1 = QMin3.Q3PK)
ORDER BY table_1.serial_number, table_1.PK1

If that works, then duplicate QMin3 as QMin4 and QMin5 for tables 4 and 5, and join the results into the Qfinal query.