?
Solved

Query Syntax Help!

Posted on 2011-05-12
18
Medium Priority
?
290 Views
Last Modified: 2012-05-11
Hi Everyone-

Attached is my database. In the update query I am attempting to update the recommended action field in tbldelinquent.

Here is the outcome I want from the scenario:

Everything in the query is working except when it goes to update record 10 in the CMDelinquent table. The recommended action should be Delinquent to HR.

Here is what I am trying to accomplish-

1st time person exceeds their required date - Delinquent to HR (for each course they must go to HR first)
Then everytime after this the recommended action is progressive
so next time no would be Notice to File and the time after MEMO 1 no matter which course

But the first time they are delinquent for ANYcourse the recommended action must be Delinquent to HR

ComplianceDomestic-Blank.accdb
0
Comment
Question by:kwarden13
  • 7
  • 4
  • 4
  • +1
17 Comments
 
LVL 29

Expert Comment

by:Badotz
ID: 35746781
If you show us the query itself, then we might not need the entire DB, eh?
0
 
LVL 8

Expert Comment

by:Andrew_Webster
ID: 35746916
Please either post the SQL, or an Access 2003 version of the database, to make sure that the largest number of people can take a look at your problem.  We'd love to help!
0
 

Author Comment

by:kwarden13
ID: 35747220
Here is the code and the 2003 database.

 
UPDATE tblTempCMDelinquentMemo RIGHT JOIN (((tblCMinfo INNER JOIN tblDisposition ON tblCMinfo.[Learner ID] = tblDisposition.LearnerID) INNER JOIN tblCMDelinquent ON tblCMinfo.[Learner ID] = tblCMDelinquent.[Learner ID]) LEFT JOIN tblTempCMDelinquentNotice ON (tblCMDelinquent.[Learner ID] = tblTempCMDelinquentNotice.[Learner ID]) AND (tblCMDelinquent.Item_ID = tblTempCMDelinquentNotice.Item_ID) AND (tblCMDelinquent.Calendar_Year = tblTempCMDelinquentNotice.Calendar_Year)) ON tblTempCMDelinquentMemo.[Learner ID] = tblCMDelinquent.[Learner ID] SET tblCMDelinquent.Recommended_Action = IIf(([tblDisposition].[LOA_Days]>0 And [tblDisposition].[LOA_Days]<31) Or ([tblDisposition].[Bereavement Days]>0) Or ([tblDisposition].[Service_Center_Ticket#]<>""),"Add 30 Days",IIf([tblDisposition].[LOA_Days]>30,"Add 90 Days",IIf([tblCMinfo].[PA ID] In ("0055","0056","0471","1933","1934","2093","2094","2194"),"Alameda",IIf(Not IsNull([tblTempCMDelinquentMemo].[Learner ID]),"Memo 1",IIf(Not IsNull([tblTempCMDelinquentNotice].[Learner ID]),"Notice to File","Delinquent to HR")))))
WHERE (((tblCMDelinquent.Recommended_Action) Is Null));

Open in new window









ComplianceDomestic-Blank.mdb
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 8

Expert Comment

by:Andrew_Webster
ID: 35747363
Yow!  I used to make a living rebuilding stuff like this.  I'm sorry, I don't want to upset you, but this is a very muddled little database!

Burying logic in a thumping great IIf inside SQL is a recipe for ensuring that this become impossible to maintain.  Can I make a suggestion?  Spend some more time working on the data design.  Any time you're thinking about conditional rules, think out how they can be represented by data.  Can you have a table that handles some of the rules?  The LOA_Days ranges - those could be in a table, the PA_ID values that require an update, those should definitely be in a table.

I'm really not willing to help answer the question you've put to us, as it would be digging you into a deeper hole.  I've got fifteen years experience doing this, and this little database has what we call a "bad smell"!  Make it smell better and you and whoever has to maintain this after you, will be thankful you did.

Ok?
0
 

Author Comment

by:kwarden13
ID: 35747493
I cant exactly redo it. Its much bigger then that. I ahve over 100 tables and probably 150 queries. Its work just fine. I can't break up every little piece. It would be near impossible.

I have 100's of columns of data

If i broke up every little piece it just wouldn't make sense
0
 
LVL 29

Expert Comment

by:Badotz
ID: 35747559
And a composite key as the PK in table tblDisposition instead of an ID and LearnerID?

And no PK for tables tblTempCMDelinquentMemo and tblTempCMDelinquentNotice?
0
 
LVL 29

Expert Comment

by:Badotz
ID: 35747626
In Access, the PK for a main table is usually an AutoNumber field, which is an arbitrary number that has no meaning. Using a Text field means that you have to specify the value, which makes adding a duplicate key possible.

Further, using fields such as Date_Added and Calendar_Year as parts of the PK makes changing the values of these fields difficult should other tables be in relationship.

Far better to begin each table with
id .. AutoNumber

Open in new window

and then add any other FKs as needed
learner_id ... Number

Open in new window

You can then use id and learner_id as the PK.
0
 

Author Comment

by:kwarden13
ID: 35747631
Badotz-

Not sure what you are referring to in the 1st question?

The reason there are no PKs in the other 2 tables is that they are temp tables. I didn't think I needed them but its not a big deal to add them
0
 

Author Comment

by:kwarden13
ID: 35747642
The composite keys have been set for a reaon and carefully choosen. II understand if i convert more things to "number" it would run alot faster, however I am working with alot of different systems and the import of data is all text.
0
 

Author Comment

by:kwarden13
ID: 35747647
can anyone help with the original question?
0
 

Author Comment

by:kwarden13
ID: 35747657
I am only trying to change the value of the recommended action field if it is null.
0
 
LVL 8

Expert Comment

by:Andrew_Webster
ID: 35747681
Ok, sorry I can't help.  I'd urge you to read "Refactoring Databases" http://databaserefactoring.com/.  

Access gets a terrible reputation because there is so much awful work done in it.  If you can learn how to carefully and incrementally rebuild a bad Access database, you'll do your career a power of good, and your end users will doubtless appreciate it!
0
 

Author Comment

by:kwarden13
ID: 35747726
I am definitely going to check out that book, but I still don't have time right now to change things. Once I get my database working 100%, I will look at redoing some things.

Thanks
0
 
LVL 8

Expert Comment

by:Andrew_Webster
ID: 35749614
Sucks I know, but I'm guessing that people are looking at your database and running a mile!  Put it this way, when I was a consultant, dealing with rebuilding databases like that, my low end estimate for a rebuild was three months.  Once it was rebuilt, I'd expect maintenance to always be a matter of minutes to make simple changes.  

Everything comes back to a good data design.  Usually it's rushed or done by someone who's not very good at data architecture, and things always end up in a mess.

I'm a stickler for that, but that's why part of my resume includes having written code and data design standards for government departments.  Data isn't magic, it's all down to good design.
0
 
LVL 58

Expert Comment

by:harfang
ID: 35809577
Hello kwarden13

Like Andrew, I used to make a living cleaning up databases. I was intrigued by this question, but I don't normally look at full databases on EE. So I tried to work with only what you gave us in the question and the query itself... After some massage, it boils down to:
UPDATE (((
  tblCMDelinquent CM INNER JOIN
  tblCMinfo I ON CM.[Learner ID] = I.[Learner ID]) INNER JOIN
  tblDisposition D ON CM.[Learner ID] = D.LearnerID) LEFT JOIN
  tblTempCMDelinquentMemo M ON CM.[Learner ID] = M.[Learner ID]) LEFT JOIN
  tblTempCMDelinquentNotice T ON CM.[Learner ID] = T.[Learner ID] AND CM.Item_ID = T.Item_ID AND CM.Calendar_Year = T.Calendar_Year

SET
  CM.Recommended_Action =Switch(
    (D.LOA_Days>0 And D.LOA_Days<31) Or (D.[Bereavement Days]>0) Or (D.[Service_Center_Ticket#]<>""),
    "Add 30 Days",
    D.LOA_Days>30,
    "Add 90 Days",
    I.[PA ID] In ("0055","0056","0471","1933","1934","2093","2094","2194"),
    "Alameda",
    Not IsNull(M.[Learner ID]),
    "Memo 1",
    Not IsNull(T.[Learner ID]),
    "Notice to File",
    True,
    "Delinquent to HR"
    )

WHERE CM.Recommended_Action Is Null

Open in new window


I have cleaned up the table names, using aliases, and I have replaced the nested IIf's by one Switch statement, which is more readable. Hopefully, if there are no typos, this query should be functionally identical with yours. There seems to be nothing wrong with the actual Switch expression, but the FROM clause is strange (and Andrews comments are a strong hint that the problem might be there...)

So a "check" version would be the following SELECT query:
SELECT
  CM.[Learner ID],
  CM.Item_ID,
  CM.Calendar_Year,
  CM.Recommended_Action,
  D.LOA_Days,
  D.[Service_Center_Ticket#],
  I.[PA ID],
  M.[Learner ID] As M_LearnerID,
  T.[Learner ID] As T_LearnerID,
  Switch(
    D.LOA_Days>0 And D.LOA_Days<31 Or D.[Bereavement Days]>0 Or D.[Service_Center_Ticket#]<>"",
    "Add 30 Days",
    D.LOA_Days>30,
    "Add 90 Days",
    I.[PA ID] In ("0055","0056","0471","1933","1934","2093","2094","2194"),
    "Alameda",
    Not IsNull(M.[Learner ID]),
    "Memo 1",
    Not IsNull(T.[Learner ID]),
    "Notice to File",
    True,
    "Delinquent to HR"
    ) As New_Action,
  D.LOA_Days>0 And D.LOA_Days<31 Or D.[Bereavement Days]>0 Or D.[Service_Center_Ticket#]<>"" As Add30,
  D.LOA_Days>30 As Add90,
  I.[PA ID] In ("0055","0056","0471","1933","1934","2093","2094","2194") As Alameda,
  Not IsNull(M.[Learner ID]) As Memo1,
  Not IsNull(T.[Learner ID]) As NoticetoFile,
  True As DelinquentToHR

FROM (((
  tblCMDelinquent CM INNER JOIN
  tblCMinfo I ON CM.[Learner ID] = I.[Learner ID]) INNER JOIN
  tblDisposition D ON CM.[Learner ID] = D.LearnerID) LEFT JOIN
  tblTempCMDelinquentMemo M ON CM.[Learner ID] = M.[Learner ID]) LEFT JOIN
  tblTempCMDelinquentNotice T ON CM.[Learner ID] = T.[Learner ID] AND CM.Item_ID = T.Item_ID AND CM.Calendar_Year = T.Calendar_Year

Open in new window


It's basically the same Switch expression using the same source (the same FROM clause), with all the fields used in the expression. If I made no typos, this should actually run in your database. OK, little chance of that — you will have to correct them — but one can hope.

Now, tell me: does this query show the same number of records as the table you are trying to update?

My guess is that due to the relationships you set up, you could very well be updating the same record more than once... If so, you will have to check the way your tables are linked in the query.

In any case, the technique I showed here should help you solve your problem. Start with SELECT queries until you can reliably calculate the new value you want for a field. If you have doubts, break complex expressions down into smaller chucks, so you can validate each step separately. Once the SELECT works, turn it into an UPDATE query, but not before. It's almost impossible to debug UPDATE queries directly.

I hope this helps,
Good Luck!
(°v°)
0
 
LVL 58

Accepted Solution

by:
harfang earned 2000 total points
ID: 35973820
kwarden13,

I put quite some time into this. Perhaps my post was too scary? Perhaps the queries didn't work because of a small typo? Anyway I decided to test them and they work as described. Notice that the select query shows 29 rows to update, which means I correctly identified the problem: multiple updates of the same record. Have you tried these queries, and if yes do you have additional questions about them?

If you ask specifically for advice on how to improve your table structure, you will probably get more useful answers than those you received here initially. This is because we all know that helping you create this query (if it is at all possible) isn't helping you in the long run; helping you improve the data structure would be! A possible wording would be: “What is wrong with this table structure? Please be gentle and explain the problems one at a time.”

In any event, success with your project!

____________________

Dhaest,

Although the problem was identified, there is no “correct answer”. The database needs to be remodelled before a query like this one can work as intended, and this goes beyond the scope of this question.

I recommend: accept {http:#35809577}. Perhaps an assist for {http:#35747363} and {http:#35747626} (identifying some of the underlying problems). I'll let you be the judge and won't comment on your decision.

Cheers!
(°v°)
0
 
LVL 29

Expert Comment

by:Badotz
ID: 36105376
That works for me.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
When you discover the power of the R programming language, you are going to wonder how you ever lived without it! Learn why the language merits a place in your programming arsenal.
Six Sigma Control Plans
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

850 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