Update table from query automatically

[charly.csh]

Hello everyone!


I'm having the next issue described and I am sure somebody here could provide me support on this matter.

Into my database I have a table named "Main", In which could be found test results and it has the following fields: ID, Model, Batchnumber, TestType, Result, TestDate.



The issue I get is that on the table "tblMain" the serial number is recorded three times because are different test performed (test1, test2 and test3). It comes directly from the testequipment automatically.

For that reason I made a query named "QyFilter" in order to find the duplicated serial numbers (Batchnumbers) and observe the quantity of "real" products tested. This query has the following fields: Model, Batchnumber and TestDate.


Now I want to perform is send this info from the query "QyFilter" to a new table named "tblTestResults" in which can be found more than the three fields defined in the query "QyFilter", I attemped it in many ways: MakeTable, Append and Update but I am not suceeded.

When I made "MakeTable query" everytime was creating a new table.... and not updating the current data to the new table...
When I made "Append" everytime I performed the query the data were pasted again to the old ones...
When I made "Update" the other records in the fields are deleted.

I want to achieve is...


When the table "tblMain" will be updated automatically will be updated the query "QyFilter" and also the table "tblResults" (only the fields involved)


I hope somebody could help on this!!

Thanks in advance for the support!

[ranman256]

IF this is a single user db, then any of these querys should work, make, append.
I would have a 'entry' form / table.
when all data has been entered, click save button
the save button macro would append data to the main tbl (append)
and make table the data to tblTestResults.

Then next data entry, erase the 'entry' table, and start fresh.

[charly.csh]

Yes, this is a single database

You are right, but the issue with 'Append' but everytime when I run the query it paste all the records again and those now are duplicated and just I want to paste the new ones, how can I achieve that?. I really appreciate your help!

[June7]

I suspect need to apply a filter criteria so that previous records are not appended a second time. Or maybe just set compound unique index in tblTestResults to prevent duplication.

Why do you need to copy data into another table? Why not just use a query that joins the tables?

Do you want to provide db for analysis? Follow instructions at bottom of my post.

[charly.csh]

Of course I want to share it for analysis

Responding why a new table:

The reason is because the test-equipment records the same serial number 3 times on a table.... because the test performed is different (1, 2 & 3). I mean every run of the machine will create 3 records.

For me would be better if it would record once in different fields, but it does not do that.

so, the only solution I could find was:

1. Create a "General Query (tblYeild Query) from tblMain" with the data complete and with a logical sense,
2. Create a second query with the Filtered Batchnumbers (once, no three times as the original) to get separated the Batchnumbers and send those to a new table (tblResults)
3. Update the missing fields in tblResults from a form, using DLookup from tblMain to get the missing records in the fields

Basically I am doing all that to get tblMain with only one record by Batchnumber

Attached you can see the db

[June7]

Is the data in tblMain fully representative? I see 3 records each for two BatchSer numbers. Two are TestType 2 and one is TestType 3.

I see 15 tests in tblTestTypeText. So each test batch will have 3 tests but those 3 could be any combination of the 15 in any of the 3 test fields? If this is the case then you may be walking a path to insanity if you use Results table. Say you want to find all records where TestType 9 was performed - would have to apply same search criteria on each of the Type fields. This search would be easier to do in tblMain. I guess as long as both tables are retained, you have options.

tblMain structure is actually more normalized than the Results table. What if the procedure changed and each batch generated 4 or 5 or more tests? Results table (as well as queries, forms, reports, code) would have to be modified - tblMain would not. There is a limit of 255 fields in a table/query.

Technically, tblMain could be split into two tables but the data comes like this from another source and memory is cheap - so unless you expect to exceed the Access 2GB size limit, would not bother.

My question is, what do you gain by de-normalizing the data into Results table? What is output you want that makes you think this structures is required?

I have faced situation where I needed to de-normalize data for a report structure. I accomplish this with queries and VBA code and a temp table (the data lives in table only for duration of process to generate report).

[charly.csh]

Those are the answers:

1. Is the data in tblMain fully representative? I see 3 records each for two BatchSer numbers. Two are TestType 2 and one is TestType 3.
Yes is fully representative, I deleted a lot of records to share it just as example

I see 15 tests in tblTestTypeText. So each test batch will have 3 tests but those 3 could be any combination of the 15 in any of the 3 test fields?
No, the test performed for this application will be always Test 2 (HiPot), and Test 3 (Consumption) I don't delete those yet.

So that means Test 2 will be run just 2 times one with the property 17, and the second one with the property 16 (Always will be in this way)

and Test 3 always will run with the property 19 (just once)

tblMain structure is actually more normalized than the Results table. What if the procedure changed and each batch generated 4 or 5 or more tests? Results table (as well as queries, forms, reports, code) would have to be modified - tblMain would not. There is a limit of 255 fields in a table/query.
Always will be recorded 3 records and never will be added more for this application as I mentioned I need to deleted the other ones


My question is, what do you gain by de-normalizing the data into Results table? What is output you want that makes you think this structures is required?
The reason is because I would like to have separated each Batchnumber and its results (Test2: Property 16, Test2:Property 17, Test3: Property19, just this 3 ) by record in a table in order to calculate efficiency by the total of Batchnumbers and also print conformance labels with all the test results and stuff like those


But I am open mind to other solutions, I am not close to other ways to solve it. I really appreciate your support!!!

[June7]

There are ways to achieve those results without having to maintain another table.

Query 1 - qryYield
SELECT tblMain.PartID, tblPartNumbersList.ModelNumber, tblMain.BatchSer, tblTestTypeText.TestType, tblTestTypeText.Value AS TestTypeText, tblpropertytxt.Property, tblpropertytxt.Value AS PropertyText, tblMain.Date, tblMain.TimeStamp, tblMain.OperatorNumber, tblPassFail.Text, tblMain.Value
FROM tblPassFail INNER JOIN (tblpropertytxt INNER JOIN (tblPartNumbersList INNER JOIN (tblTestTypeText INNER JOIN tblMain ON tblTestTypeText.TestType = tblMain.TestType) ON tblPartNumbersList.PartID = tblMain.PartID) ON tblpropertytxt.Property = tblMain.Property) ON tblPassFail.Value = tblMain.Result
ORDER BY tblMain.Date DESC;

Query 2 - qryResults
SELECT qryYield.BatchSer, qryYield.ModelNumber, qryYield.Date, Max(IIf([TestType]=2,[TestType],Null)) AS TestType2, Max(IIf([TestType]=2,[TestTypeText],Null)) AS TestType2Text, Max(IIf([Property]=16,[Value],Null)) AS ValueProperty16, Max(IIf([Property]=16,[Property],Null)) AS Property16, Max(IIf([Property]=16,PropertyText,Null)) AS Property16Text, Max(IIf([Property]=17,[Value],Null)) AS ValueProperty17, Max(IIf([Property]=17,[Property],Null)) AS Property17, Max(IIf([Property]=17,PropertyText,Null)) AS Property17Text, Max(IIf([TestType]=3,[TestType],Null)) AS TestType3, Max(IIf([TestType]=3,[TestTypeText],Null)) AS TestType3Text, Max(IIf([Property]=19,[Value],Null)) AS ValueProperty19, Max(IIf([Property]=19,[Property],Null)) AS Property19, Max(IIf([Property]=19,PropertyText,Null)) AS Property19Text
FROM qryYield
GROUP BY qryYield.BatchSer, qryYield.ModelNumber, qryYield.Date;

Now if you want, can take Query 2 and use it as source for copying records into Results table or can avoid that extra step and just use the query for further data manipulation.

[charly.csh]

Wooooow this is incredible way to solve it!!
that is exactaly I was looking for!

Just quick question, in the tblMain I have a field named Result, "Pass" or "Fail", in the second query qryResult how can I add this to the "ValueProperty16", "ValueProperty17" & "ValueProperty19" ??

Thank you very much!!!

[June7]

With more calculated fields using IIf() and Max like the other calculated fields.

[charly.csh]

Perfect!, you know, this is the first time I heard about the IIF() for the calculated fields...

Could you tell me please how should be the sentence for the ValueProperty16 as example, in order to fill the other ones with that

Thank you!!!

[June7]

Why don't you try it first and if have issue, post the attempt for analysis. There are plenty of examples in the query I posted. Make fieldname substitution as appropriate.

[charly.csh]

I tried and I could do it!!! Thank you very much

ResultProperty16: Max(IIf([Property]=16,[Text],Null))