Help needed to generate a specific kind of output table from other table

Hi Geof..
I think from an example it will be more clear what I want.

Me.Pcycle is my original table that has all fields and data too.

Code:

---

product | parameter | Item Des.|  jan'05  feb'05  jan'06  jan'07.....| Revision
________________________________________________________________

  M1        test time    SED            .2        .3        .4                      0
  M1        burn time    SED            .2        .5        .8                      0
  M1        test time      SED            .2        .2        .4                      1
  M2        test time      SED            .1        .3        .4                      0
  M2        test time      SED            .2        .9        .5                      1
  M2        test time      SED            .1        .1        .1                      2

---

so this table has all the data in it.

I am running a query for Max(Revised) and by grouping all the rest of critical fields to find out latest records from this table and that is Latest table. Higher revision number indicates latest record.

Since I am running the query
"SELECT s.product, s.parameter, s.Item Des., Max(Revison)" & _
"INTO [Table]" & _
"FROM [" & Me.PCycle & "] s " & _
"GROUP BY s.product, s.parameter, s.Item Des;"

So latest table is,

Code:

---

product |  parameter | Item Des.|  Revision
________________________________________

  M1        burn time      SED              0
  M1        test time        SED              1
  M2        test time        SED              2

---

So, this table has only the latest records from the previous table.
As the SELECT clause in this query does not allow me to put s.* over there.....

I want a result table that has records only from latest table records, but they should have their corrosponding data value columns too from the me.Pcycle table.

[in the browser, The spacing of table might cause confusion...so
Product = M1, M2
parameter = burntime, testtime
Item Des. = SED
Revision = 0,1,2,3 etc.]

That is what I am trying to do.
Please help regarding this.

Thanks..

I am also trying with inner join.....Me.Pcycle is the table with all the fields that is data fields too. Duplicate is the summary table with latest records and I am saving the result table as Final_Duplicate table

strSQL4 = "SELECT p.* " & _
"INTO [Final_Duplicate] " & _
"FROM [" & Me.PCycle & "] p " & _
"INNER JOIN Duplicate d ON d.product, d.parameter, d.ItmDes, d.Revision;"

But the error is "Inner join expression not supported"

Can you modify the above query statement? What is the cause of this error?

Thanks for your continuous help and guidance.

Ah, I see now - I didn't quite get what you meant from the first post (got distracted before I could comment).

The join is the right way to go, you just need to use the right syntax. The ON part needs to be basically the same as a Where clause, so what you should have is more like this:

VB Code:

strSQL4 = "SELECT p.* " & _
  "INTO [Final_Duplicate] " & _
  "FROM [" & Me.PCycle & "] p " & _
  "INNER JOIN Duplicate d ON (d.product = p.product AND d.parameter = p.parameter AND d.ItmDes = p.ItmDes AND d.Revision = p.Revision);"

Note that you may get errors with the brackets, they are required by some database systems but cause problems for others. You may also have problems with having that many conditions in the ON clause, in which case you can put some into the Where clause instead.

Thanks Si,
But if I do this each time it opens up an input box and asks to enter d.product, p.product, d.parameter, p.parameter, d.ItmDes, p.ItmDes, d.Revision, p.Revision

But I want that it should make direct comparision and should not ask the user to enter each time for each parameter.....

How to do that?

You need to put in exactly the same field names as your table has ;)

Yes Si,
that is ok, it works...
But do u see any error in this logic for the kind of output I want.

What happens is in my Duplicate table there are 489 rows, so there should be the same 489 rows be there in the final table but their data columns attached from Me.PCycle table, but that is not happening...

Si..
I want Duplicate table's all records to be there and for those records data should be there from Me.PCycle table...

But that is not happening .... Logically what I am trying to do is to compare the whole record of duplicate table with all its fields to the records of Me.Pcycle table, so if there are all the fields have same data, then putting that whole record from Me.Pcycle table to the new table....But there is some error...Plz help..

If all of the rows from Duplicate are also in Me.PCycle then it should be ok. How many rows are being written?

Try this, it will show you which rows from Duplicate are not matched - so you may be able to see the problem:

VB Code:

strSQL4 = "SELECT d.*, p.product " & _
  "INTO [Not_written] " & _
  "FROM Duplicate d  " & _
  "LEFT JOIN [" & Me.PCycle & "] p ON (d.product = p.product AND d.parameter = p.parameter AND d.ItmDes = p.ItmDes AND d.Revision = p.Revision) " & _
  "WHERE p.product IS NULL"

About 21 rows are written....

And actually all the rows from Duplicate table is also there in Me.Pcycle table, because duplicate table is generated from me.Pcycle table only....But duplicate table is not having all the data fields that is why I have to do this to get the data fields from me.Pcycle table...

I will try your suggestion above and will let you know how it goes...

Thanks again...

Rest of the 468 rows are written into not_written table?

Why? Actually all the rows are there in Me.Pcycle table. And since we are comparing each and every column data of both the tables with AND, that is basicall record to record comparision. Then why it is doing like this?
How will this help me to debug it out?

There must be some difference(s) between the rows, otherwise they would match up and be written to the Final_Duplicate table.

If the values seem to be exactly the same, you will need to check that the data types for each of the "matching" columns are the same (eg: d.product and p.product are the same data type), and also check that any text fields do not have any extra spaces at the end in one of the tables.

Hi Si.. I checked the datatypes in both the tables after they are generated and in the both the tables datatypes of all columns are same.

Actually are you sure that the SQL

strSQL4 = "SELECT p.* " & _
"INTO [Final_Duplicate] " & _
"FROM [" & Me.PCycle & "] p " & _
"INNER JOIN Duplicate d ON (d.product = p.product AND d.parameter = p.parameter AND d.ItmDes = p.ItmDes AND d.Revision = p.Revision);"

will give me what I want as shown in example. Will it do record by record comparision for that critical field?

Because what I tried is to do comparision on just 2 fields,

strSQL4 = "SELECT p.* " & _
"INTO [Final_Duplicate] " & _
"FROM [" & Me.PCycle & "] p " & _
"INNER JOIN Duplicate d ON (d.product = p.product AND d.Revision = p.Revision);"

and it created about 50000+ records in result table!! I think something wrong is there in comparision statement of inner join.

Product, Parameter, ItemDes and Revision are critical fields for me.
I want their combination to be same in both the tables, if that whole string is same then I want that record with all the fields to be transferred in result table from Me.Pcycle table....But somehow that is not working...

Hi Si..
Please help with this.

Actually looks like some of the fields in some records in both the tables are emply.
like in some records product name is not there, while in some records ItmDes is not there. So do you think this might be causing the problem?

Is there any way to deal with this? Because if one field is empty it is going to be empty in both the tables so won't the AND comparision will work?

Thanks again..

It's not surprising you got many records doing a join on only two fields, if one of them could be empty in several cases, while the other had a limited range of values. Take this case. You have two identical tables, with only two records, which are:
Product Revision ItmDES
1 <NULL> 2 Something
2 <NULL> 2 Somethingelse

Joining the tables on just product and revision will generate 4 records in the output table:
Product Revision ItmDES
<NULL> 2 Something
<NULL> 2 Somethingelse
<NULL> 2 Something
<NULL> 2 Somethingelse
as record 1 in the first table will join with both records 1 and 2 from the second, and record 2 will join with both 2 and 1.

"Empty" is actually Null, which unfortunately does not work with = comparisons, so this is the reason that it wasnt working before, now we just need to fix that issue.

As anguswalker explained, if you care about fields matching, they must all be in the ON clause.

Here is a 'long' version that should work for you; I would try to give you a shorter version for whichever database you are using (or possibly a statement to avoid the use of the Duplicate table), but I'm off on holiday soon!

VB Code:

strSQL4 = "SELECT p.* " & _
  "INTO [Final_Duplicate] " & _
  "FROM [" & Me.PCycle & "] p " & _
  "INNER JOIN Duplicate d ON (" & _
     "(d.product = p.product OR (d.product Is Null AND p.product Is Null)) AND " & _
     "(d.parameter = p.parameter OR (d.parameter Is Null AND p.parameter Is Null)) AND " & _
     "(d.ItmDes = p.ItmDes OR (d.ItmDes Is Null AND p.ItmDes Is Null)) AND " & _
     "(d.Revision = p.Revision OR (d.Revision Is Null AND p.Revision Is Null)) " & _
  "); "

To decide which fields need to be in the inner join you need to work out what combination of fields guarantees a one-to-one relation (i.e. what combination of fields identifies a unique record). You may or may not need all of the fields in Si's example, or you may need more. To decide, try this query:
strSQL = "SELECT count(product) as ProductCount, product, itemDES, parameter from [" & me.Pcycle & "] group by product, itmDES, parameter WHERE count(product) > 1"
If this returns any rows, then you know that just joining on product, itemDES and parameter is not enough as there are multiple records (productCount will tell you how many) with identical combinations of these three fields. So you try a different combination, until you get a query that returns no records. Then you will have identified a combination of fields that guarantees uniqueness, and use all of those (joined with ANDs) in your INNER JOIN.

Thanks Si and Anguswalker...

The other thing what I tried to do is I added autonum field after the generation in Me.Pcycle table to make a unique primary key, so that I can use that to compare between two tables and to find out records.

but this creates another problem. Now Me.Pcycle table has myID autonum field. But this gives me problem in creating the Duplicate table itself by strSQL3. strSQL3 generates Duplicate table based on max revision number based on the group of critical fields. I can not add myID in group by statement as it will make each record unique, and there will be no effect of Max(Revised) now as each record is unique so it will not find the latest record based on revised field.

So I just kept myID field in select statement and not in group by statement and it gave me an error "You tried to execute a query that does not include a specified expression 'myID' as part of an aggregate function" This is because myID is not there in group by statement, but I can not put it there because I want latest based on Revised field for product, parameter and ItmDes only. Now How to solve this?

Meanwhile I will try to implement Si's statement....and let you know
_______________________________________________________________________
strSQL3 = "SELECT s.product, s.parameter, s.ItmDes, Max(Revised) AS Revision, s.myID " & _
"INTO [latest] " & _
"FROM [" & Me.PCycle & "] s " & _
"GROUP BY s.product, s.parameter, s.ItmDes;"
Debug.Print strSQL3
DoCmd.RunSQL strSQL3

strSQL4 = "SELECT p.* " & _
"INTO [Final_Duplicate] " & _
"FROM [" & Me.PCycle & "] p " & _
"INNER JOIN Duplicate d ON (d.myID = p.myID);"
_______________________________________________________________________

I'm afraid you'll need something like this:

VB Code:

strSQL3 = "SELECT * "INTO [latest] " & _
"FROM [" & Me.PCycle & "] a " &_
"INNER JOIN (SELECT s.product, s.parameter, s.ItmDes, Max(Revised) AS Revision, s.myID " & _
"FROM [" & Me.PCycle & "] s " & _
"GROUP BY s.product, s.parameter, s.ItmDes) p " &_
"ON a.product = p.product AND a.parameter = p.parameter AND a.itmDes = p.ItmDes AND a.Revised = p.Revision)
Debug.Print strSQL3
DoCmd.RunSQL strSQL3

Again not tested, but what it should do is create your original query, with alias p and inner join it on product, parameter and itmDes to another instance of the original table, with alias a. You then get the value of ID from instance a.
If it works exactly as written it'll be a miracle, but you should be able to work it out I reckon.

Hi Anguswalker...

Thanks for the help...
But still it gives the same error as

"You tried to execute a query that does not include a specified expression 'myID' as part of an aggregate function"

Could you post the contents of the immediate window- maybe I can work it out from there.

This is printed in the Immediate window.

SELECT * INTO [Latest] FROM [march'05] a INNER JOIN (SELECT s.product, s.parameter, s.ItmDes, Max(Revised) AS Revision, s.myID FROM [march'05] s GROUP BY s.product, s.parameter, s.ItmDes) p ON a.product = p.product AND a.parameter = p.parameter AND a.itmDes = p.ItmDes AND a.Revised = p.Revision;

I see it now. Leave out s.myID from the subquery:

VB Code:

strSQL3 = "SELECT * "INTO [latest] " & _
"FROM [" & Me.PCycle & "] a " &_
"INNER JOIN (SELECT s.product, s.parameter, s.ItmDes, Max(Revised) AS Revision " & _
"FROM [" & Me.PCycle & "] s " & _
"GROUP BY s.product, s.parameter, s.ItmDes) p " &_
"ON a.product = p.product AND a.parameter = p.parameter AND a.itmDes = p.ItmDes AND a.Revised = p.Revision)
Debug.Print strSQL3
DoCmd.RunSQL strSQL3

MyID is returned from the main query anyway.