Query Emergency !!!!! (Access 97 SR 1)

Topic

New Reply

Using Access 97 SR1

Greetings,

I’ve inherited an emergency that I cannot seem to resolve in a payroll database. The DB is run strictly from Macro’s. Hoping someone can help with this ASAP, as this process must be run this morning (Now 08:22 in Indiana)

I need to add two APC codes to the 3 that are already in a MakeTable query called “IDT-INPUT – Table”. When I add the two new APC, the macro crashes, displaying a parameter textbox asking for TA.

Step1: Transfer Text from a remote file into a table called “IDT”.
Step2: Runs a Make Table query called “IDT-INPUT – Table”. The query come from table “IDT”. Note that this table is empty until the Transfer Text in Step1 is run. This is where it crashes if I add the two new APC’s.

Here is the SQL of the original Make Table Query:

SELECT DISTINCTROW IDT.TA AS Expr1, IDT.B1 AS Expr2, IDT.BLK AS Expr3, IDT.B2 AS Expr4, IDT.FY AS Expr5, IDT.B3 AS Expr6, IDT.DUEDATE AS Expr7, IDT.MANHRS AS Expr8, IDT.OC AS Expr9, IDT.B4 AS Expr10, IDT.EOE AS Expr11, IDT.B5 AS Expr12, IDT.APC AS Expr13, IDT.B6 AS Expr14, IDT.ODC AS Expr15, IDT.DOCNO AS Expr16, IDT.DOV AS Expr17, IDT.IC AS Expr18, IDT.AMOUNT AS Expr19 INTO [IDT INPUT]
FROM IDT
WHERE ((([IDT].[FY])=”8″ Or ([IDT].[FY])=”9″ Or ([IDT].[FY])=”0″ Or ([IDT].[FY])=”1″ Or ([IDT].[FY])=”2″ Or ([IDT].[FY])=”3″ Or ([IDT].[FY])=”4″) AND (([IDT].[APC])=”R151″ Or ([IDT].[APC])=”R152″ Or ([IDT].[APC])=”R153″));

Here is the same SQL including the two new APC’s (NOTE: This does not work for some reason):

SELECT DISTINCTROW IDT.TA AS Expr1, IDT.B1 AS Expr2, IDT.BLK AS Expr3, IDT.B2 AS Expr4, IDT.FY AS Expr5, IDT.B3 AS Expr6, IDT.DUEDATE AS Expr7, IDT.MANHRS AS Expr8, IDT.OC AS Expr9, IDT.B4 AS Expr10, IDT.EOE AS Expr11, IDT.B5 AS Expr12, IDT.APC AS Expr13, IDT.B6 AS Expr14, IDT.ODC AS Expr15, IDT.DOCNO AS Expr16, IDT.DOV AS Expr17, IDT.IC AS Expr18, IDT.AMOUNT AS Expr19 INTO [IDT INPUT]
FROM IDT
WHERE ((([IDT].[FY])=”8″ Or ([IDT].[FY])=”9″ Or ([IDT].[FY])=”0″ Or ([IDT].[FY])=”1″ Or ([IDT].[FY])=”2″ Or ([IDT].[FY])=”3″ Or ([IDT].[FY])=”4″) AND (([IDT].[APC])=”R151″ Or ([IDT].[APC])=”R152″ Or ([IDT].[APC])=”R153″ Or ([IDT].[APC])=”R155″ Or ([IDT].[APC])=”R157″));

I’ll keep checking back as often as I can, but any e-mail notifications are sent to my home e-mail address.

Thanking all who reply in advance,

Bob in Indy

Reply | Quote

Replies

Bob,

Check carefully that there is a field named TA in the most recent version of the IDT table. If it was present in previous incarnations, but not in the present one, that would cause the prompt for TA, it would have nothing to do with the addition of extra values for APC.

As an experiment, you could omit IDT.TA from the SELECT part of the query and see if it runs then.

Your WHERE condition can be written more efficiently using IN. If the above doesn’t help, you could try and see if it makes the problem go away (I don’t see why it should, but…) Here is the modified version:

WHERE [IDT].[FY] In (“8″,”9″,”0″,”1″,”2″,”3″,”4”) AND [IDT].[APC] In (“R151″,”R152″,”R153″,”R155″,”R157”)

Reply | Quote

Hans,

Thanks for the quick reply! I’ll try it out and let you know what I find.

Best regards,

Bob

Reply | Quote

Hans,

This is my first opportunity to post again. Indeed, the IDT.TA field exists…but the table that the query comes from is deleted after the make-table runs. Your query didn’t work…but not your fault.

I don’t pretend to understand how this happens, but there was ANOTHER query….a DELETE query, where the APC criteria also existed….and I had to ensure that the same criteria from the Make-Table query also existed in the Delete query. I don’t understand why criteria is needed in this query, which seems to delete all of the data in the IDT table. That @#$%^&* Delete query was there all the time, but I didn’t look at it. I’m posting the SQL from this query for any who are interested.

I predict that I’ll be called upon to rewrite this app, as I’ve had to rewrite another similar app in the past. Thank you again for your effort, and the rapid response.

Bob in Indy (where it’s over 30 degress warmer today than yesterday !!)

DELETE DISTINCTROW IDT.TA AS Expr1, IDT.B1 AS Expr2, IDT.BLK AS Expr3, IDT.B2 AS Expr4, IDT.FY AS Expr5, IDT.B3 AS Expr6, IDT.DUEDATE AS Expr7, IDT.MANHRS AS Expr8, IDT.OC AS Expr9, IDT.B4 AS Expr10, IDT.EOE AS Expr11, IDT.B5 AS Expr12, IDT.APC AS Expr13, IDT.B6 AS Expr14, IDT.ODC AS Expr15, IDT.DOCNO AS Expr16, IDT.DOV AS Expr17, IDT.IC AS Expr18, IDT.AMOUNT AS Expr19, [IDT].[FY], [IDT].[APC], [IDT].[B2]
FROM IDT
WHERE ((([IDT].[FY])=”8″ Or ([IDT].[FY])=”9″ Or ([IDT].[FY])=”0″ Or ([IDT].[FY])=”1″ Or ([IDT].[FY])=”2″ Or ([IDT].[FY])=”3″ Or ([IDT].[FY])=”4″) AND (([IDT].[APC])=”R151″ Or ([IDT].[APC])=”R152″ Or ([IDT].[APC])=”R153″)) OR ((([IDT].[B2]) Like “T*”));

Reply | Quote

First of all, the fields in your new table will be name Expr1, Expr2, etc. Is that particularly useful?

Is the IDT.FY field really a text field, or is it numeric?

And as Hans suggested, using IN will make the query easier to read:
… WHERE IDT.FY IN (“0”, “1”, “2”, “3”, “4”, “8”, “9”) AND IDT.APC IN (“R151”, “R152”, “R153”)

If this isn’t working, run the query without the selection criteria on APC. Then look at the APC field in the results. Where any of them R151, R152, or R153?

Reply | Quote

Mark,

Thanks for replying. I inherited the mess that generated my plea for help on Friday. It’s now working. As I said, I don’t pretend to understand why the person who created this app did it as she did. In the past I was forced to overhaul another app she had created which was similar in structure to the current mess.

I had intended to upload a copy of this mess, but even when I remove everything I can to reduce it’s size, it’s still over 100K (max upload limit). If anyone is interested in seeing this mess, let me know, and I can e-mail it to you.

I still don’t understand why she uses criteria in a Delete query that delete all records anyway. That’s what had me confused last Friday.

Bob in Indy

Reply | Quote

Sorry, Bob, but I hardly ever accept downloads for review, nor will I download attachments. Occasionally if it is a specific problem and there is no other practical way, then I might. It’s just that I’ve found it becomes too easy for people to say “see attachment for my problem” rather than explaining it. Besides, this starts getting into what I do for a living (and for which I charge a consulting fee).

Reply | Quote

No prob, Mark. But I think you’ve misunderstood. Didn’t intend my e-mail offer as a means of asking for assistance…but merely to show whomever was interested the crazy way this db was set up. I’ve already begun the process of revamping the db. I want to thank you and all the others that provide inspiration and answers in this forum.

….now, if I could just get the Government to pay for some Access training for me….

Bob in Indy

Reply | Quote

>>….now, if I could just get the Government to pay for some Access training for me….<<

Hey, if you find a way, let me know! I'd like to get in on that also!

Reply | Quote

I’m sorry to say that most of the Access training I’ve seen is distinctly low level, at least the live instructor-led classes. Some of the CBT training from AppDev is pretty good, though.

Reply | Quote