Solved

Automatic Master Table Updates

Posted on 2002-04-29
24
483 Views
Last Modified: 2008-02-26
I have established a single master table with a unique number assigned and a multiple table with detail records linked to the unique number established for the master record.  I am updating a field in the multiple table called status using a Master Form and a linked SubForm, and I maintain all occurrences of the multiple records for each master record.  As I post the latest record for status in the multiple table,I would like to have the latest status value in the multiple table automatically posted to a target field in the Master table (a single occurrence). This way the master record will always show the latest status, and the multiple table shows the latest occurrence and all prior status record entries.    
0
Comment
Question by:etjordan
  • 10
  • 6
  • 6
  • +2
24 Comments
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
I wouldn't store this in the master table as it causes data-redundancy.
When someone changes your sub-table manually, the status can be out of line...

Personally I would just sort the subform descending on the (autonumber?) key, to show the last row first.
This also shows the latest status as the first row.

Nic;o)
0
 

Expert Comment

by:lschuele
Comment Utility
If you are using SQL server, you can use a trigger along the following lines:

CREATE TRIGGER testTrigger ON [dbo].[slave]
FOR INSERT
AS

UPDATE master SET master.status = INSERTED.status FROM master INNER JOIN INSERTED ON master.id = INSERTED.master_id

This trigger copies the status from the slave to the related master when a new record is inserted into the slave table.

There is another option that is more database independent and also eliminates the normalization problem mentioned by Nico.  Use a view that has a sub query that looks up the value.  Here is an example that will work in SQL Server:

SELECT *, (SELECT TOP 1 status FROM slave WHERE slave.master_id = master.id ORDER BY id DESC) FROM master

0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Hi lschuele,

I see you're rather new to EE, so welcome ;-)

You're right about the MS SQL trigger, but "normally" tables here are within access and not having the SP/trigger functionality. Thus my precaution with storing redundant data, as users can access the tables rather "easy" without using a form.....

I also thought about the select of the last status, however in access you'll need to "expand" your query to an update query to be activated from code or by using a DLOOKUP of the field status from your TOP 1 query.
Activating the query from code will have to be done whenever the last row is updated or a new row is added, so probably on multiple forms...

Thats why I try to advise to just sort the subform descending. My motto is always KISS (Keep It Stupid Simple)

Glad to have you aboard !

Nic;o)

0
 

Expert Comment

by:lschuele
Comment Utility
Thanks Nic.  

I thought that I was in VB rather than Access... oops

In the world of Access, I agree with your solution as the simplest.  
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
I see you're still finding your way around ;-)
Hope to see more comments of you, as I like to discuss solutions with others. You'll find out it improves your own skills tremendously !

C U

Nic;o)
0
 
LVL 4

Expert Comment

by:archery
Comment Utility
Am going to take nico5038 to task on one item, and that is the suggestion of (blindly) using a sort on the autonumber field to get the latest/last record. I develop applications that (in a lot of cases) start out as or become "replicated" databases (we run across a WAN) and within a "replicated" database ALL autonumber fields are created/changed to "random" as to how they are generated and thus can be both positive and negative values. This also means that there is NO guarantee that the "last" record inserted into a table will be the "largest" value, so any dependence on "order based on autonumber" goes out the window (as does any other other dependence that you may have placed into a "functioning" application.
If you "know" that your system will NEVER become a replicated system then nico's suggestion is valid, I am only bringing up the other matter as a reference point. If not, an application that had worked fine for years may suddenly give different (non-obvious) results &/or have to be addressed then.  
0
 

Author Comment

by:etjordan
Comment Utility
nico5038,

The reason that I wanted the value posted to the Master record is that I need to run summary counts by the latest status for the master record.  I agree that setting the value in the master table would be redundant and run the risk of being out of sync with the detail.

I had already sorted the subform descending on the (autonumber?) key, to show the last row first.
That does allow me to show the latest status as the first row on the subform.  

For these summary reports/counts, my extract only needs records with the latest status value.  I assign a date that runs with status as the detail is built (status is posted).   I had tried to set a query to Top 1 but it was top one for the entire query.

How do I select the latest detail record from the detail table with multiple status occurrences per Master Table unique number?

Master Table:

Key Number:   4061
Title          Test


Multiple Detail Table:
          Key Number   Status       Date
               4061    Complete     29 Apr 02
               4061    Inprocess    25 Apr 02
               4061    Assigned     24 Apr 02
               4061
Since
 
0
 

Author Comment

by:etjordan
Comment Utility
nico5038,

The reason that I wanted the value posted to the Master record is that I need to run summary counts by the latest status for the master record.  I agree that setting the value in the master table would be redundant and run the risk of being out of sync with the detail.

I had already sorted the subform descending on the (autonumber?) key, to show the last row first.
That does allow me to show the latest status as the first row on the subform.  

For these summary reports/counts, my extract only needs records with the latest status value.  I assign a date that runs with status as the detail is built (status is posted).   I had tried to set a query to Top 1 but it was top one for the entire query.

How do I select the latest detail record from the detail table with multiple status occurrences per Master Table unique number?

Master Table:

Key Number:   4061
Title          Test


Multiple Detail Table:
          Key Number   Status       Date
               4061    Complete     29 Apr 02
               4061    Inprocess    25 Apr 02
               4061    Assigned     24 Apr 02
               4061
Since
 
0
 

Expert Comment

by:lschuele
Comment Utility
On reports, you can use the above query that has a subselect in the select clause to look up the status:


SELECT *, (SELECT TOP 1 status FROM detail WHERE detail.master_id = master.id ORDER BY id DESC) AS Status FROM master

The TOP 1 is in the subquery.  What the subquery does is, for each record in master, looks up the status from the detail record with the highest id.  

If don't know or use SQL, the instructions are long...
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
For this I would use two queries:

1) A groupby query (named qryMax) to extract the most recent date like:
select KeyNumber, MAX(SubDate) AS MaxDate from tblDetails groupby KeyNumber;

2) A join to extract the status of that date.
select KeyNumber, MaxDate, Status from tblMaster join qryMax on qryMax.KeyNumber = tblMaster.KeyNumber

This will only work when you have just one entry per day. If not, you'll need to add the time or use an autonumber on the detail-table.

This is "aircode", but I guess you get the idea !

Nic;o)
0
 

Author Comment

by:etjordan
Comment Utility
lschuele,

See if my assumptions are right.

Master Table name is "RDD Records"
RDD Number:   4061
Title          Test


Multiple Detail Table name is "status":
Status is also the field in the status table.

         RDD Number   Status       Date
              4061    Complete     29 Apr 02
              4061    Inprocess    25 Apr 02
              4061    Assigned     24 Apr 02
              4062

I built a select query with RDD Number and title from RDD Records table and RDD Number, status and date from status  detail table.  I then went to SQL view to install select stmt.  See below:


SELECT *, (SELECT TOP 1 status FROM status WHERE status.RDD Number = RDD Records.RDD Number ORDER BY date DESC) AS Status
FROM RDD Records

I am getting errors???  I believe the "AS Status" portion of the code has got me messed up (maybe)?
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
I would go for the "two query" approach as access (unlike other databases) is up to four times as fast using separate queries iso "nested" queries...

Nic;o)
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Expert Comment

by:lschuele
Comment Utility
Try adding the brackets.  You need brackets around table names and fields that have spaces.

SELECT *, (SELECT TOP 1 status FROM status WHERE status.[RDD Number] = [RDD Records].[RDD Number] ORDER BY
date DESC) AS Status
FROM [RDD Records]

I like to write queries this way, as I feel that they are easy to understand, and all the query is in one place.  The down sides are the performance, and the requirement to understand SQL.  I worked in Access several years before I learned any SQL.

Nico's solution, as he pointed out, should be faster.  Subqueries tend to be slow.  I think another join to the status table is required to get the status.
0
 

Author Comment

by:etjordan
Comment Utility
lschuele,

I used brackets as you recommended.  No longer getting syntax error, but I get a msg that "at most one record will be returned".  After I click OK, I never get a query result.  I have ran the query without the nested subquery and I get 90 records with unique RDD Numbers.  With both queries, I guess I am not getting any records although the system gives me the msg above.   With the query/subquery, I should still get 90 records.

You had included the following sentence as the last sentence of your last comment:
"I think another join to the status table is required to get the status."   Is this why I am not getting records?

Sample tables -----------------------------

 Master Table name is "RDD Records".  It has 2 records.

RDD Number:            Title
4061                   Test
4062                   Testa

Multiple Detail Table name is "status":  It has 4 records,
Status is also the field in the status table.

        RDD Number   Status       Date
             4061    Complete     29 Apr 02
             4061    Inprocess    25 Apr 02
             4061    Assigned     24 Apr 02
             4062    Assigned     17 Apr 02

Query result should be the following 2 records (the Top one for each of the 2 records in the master table (RDD Records):

RDD Number   Status       Date
             4061    Complete     29 Apr 02
             4062    Assigned     17 Apr 02
             
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Did you try my suggestion Date: 04/29/2002 05:22PM PST ?

Nic;o)
0
 

Accepted Solution

by:
lschuele earned 60 total points
Comment Utility
SELECT *, (SELECT TOP 1 status FROM status WHERE status.[RDD Number] = [RDD Records].[RDD Number] ORDER BY date DESC) AS Status FROM [RDD Records]

worked for me with your table structure and data.  

I haven't seen that message in a long time, I forget what it means.  It sounds similar to the SQL Server error where a subquery returns more than one value.  The subquery here must return only one value to work.  The TOP 1 gives you one row, just status gives you one field.

Another option is that the query returns no data.  In that case, status will be NULL, so that may be your problem, depending on how you are using the query.
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Perhaps there's a RDD number with two highest dates.
In such a case access will receive TWO rows from the subquery, even if TOP 1 has been specified!
The only 100% solution for that is to add e.g. the time or to add an autonumber with a +1 increment.

Nic;o)
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Oops, the first line should read:
Perhaps there's a RDD number with two *equal* highest date entries.

BTW my query solution would have returned two rows...

Nic;o)
0
 

Expert Comment

by:lschuele
Comment Utility
I was able to reproduce your error by creating a status record with the same status and date, thanks to Nico.  I tried to add a DISTINCT to the subquery, but it gave me an error.

A difference in behavior between SQL Server and Access.  TOP 1 in SQL Server always returns only one row, as far as I know.  Access returns multiple rows in this query if the status and the date are identical.  The solution is to add a field to the subquery that makes the records used in the TOP 1 unique. I added an autonumber field id to the status table and added it to the query to get rid of your error.

SELECT (SELECT TOP 1 status FROM status WHERE status.[RDD Number] = [RDD Records].[RDD Number] ORDER BY date DESC,id) AS Status, * FROM [RDD Records];


0
 

Author Comment

by:etjordan
Comment Utility
lschuele/nic0538

I got the query/subquery to work this morning at my other job by trial and error in the query grid and cleaning my detail records.  I was using real tables vs the samples that I provided to yall.

Most of the problem rested in my detail table, there were duplicate dates per unique RDD number.

   lschuele, I loaded the test data and the SQL code you last sent and it worked with the test data.  Incidentally, it didn't need the id field (found out by accident).  It works either way.

The two of you were the only ones providing input, and you both helped me greatly to arrive at a nested query solution (first).     As such, I propose a 60/40 split of the points (lschuele/nic0538).

Please advise if you agree with this proposal.
Thanks to both of you, your help kept me trying.
-------------------------------------------------------
     nic0538,

I am now curious about the 2 query solution.
Your earlier comment follows:

For this I would use two queries:

1) A groupby query (named qryMax) to extract the most recent date like:
select KeyNumber, MAX(SubDate) AS MaxDate from tblDetails groupby KeyNumber;

2) A join to extract the status of that date.
select KeyNumber, MaxDate, Status from tblMaster join qryMax on qryMax.KeyNumber = tblMaster.KeyNumber

This will only work when you have just one entry per day. If not, you'll need to add the time or use
an autonumber on the detail-table.

This is "aircode", but I guess you get the idea !

What is , MAX(SubDate)in query 1??  Can you show how the 2 queries would work with the test table data above?

What did you mean by "This is "aircode"??

0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Hi,

Two additional Q's ;-)

1) MAX is the selection of the highest date you can select when you change the querytype to "GroupBy". It will provide the MAX value found within the field(s) you group on.
To see them work, just construct them based on your tables and fields, then press the "grid-button"

2) "aircode" stands for created without using access (e.g. to verify for syntax errors).

For a point split you can place a 0 points Q at:
http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
with a link to this Q and a moderator will guide you further.

Glad it works ;-)

Nic;o)
0
 
LVL 1

Expert Comment

by:Computer101
Comment Utility
Points reduced for split.  Now you can accept an experts comment as an answer.  After that make another question in this topic area for 40 points for the other expert.

Computer101
E-E Moderator
0
 

Author Comment

by:etjordan
Comment Utility
I accepted lschuele answer for 60 points.  I went to build a 2d question just like it for 40 points for the other expert, but I guess the other question didn't take.   if it had built a 2d question, how would I accept the other expert if his comment was not on new question?

Can ee assign the 40 points to nico538?
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
If you just add a Q with as title "Points for nico5038", then I'll add a comment ;-)

All you need to place in that Q is the reference to this Q, that's all.

C U

Nic;o)
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now