Access Query headaches!
May 27, 2006 6:33 PM Subscribe
I have a access query that is making me tear my hair out. My database has 40 fields that have three possible values (OK, NG, NA). I need to list only the entries that have NG in any one of those 40 fields.
Basically each entry has a series of questions and each time the answer is NG the person using the database will put some additional information in the field after the OK/NG/NA field. This repeats for 40 different questions.
So each seperate entry into the table has all 80 fields with the possible NG info.
I need to craft a query that will only pull out the entries in the table that have NG in ANY of the 40 spots.
I tried putting "NG" in the criteria for each of the 40 fields and I get no output at all. If I just do it in one it works ok.
Basically each entry has a series of questions and each time the answer is NG the person using the database will put some additional information in the field after the OK/NG/NA field. This repeats for 40 different questions.
So each seperate entry into the table has all 80 fields with the possible NG info.
I need to craft a query that will only pull out the entries in the table that have NG in ANY of the 40 spots.
I tried putting "NG" in the criteria for each of the 40 fields and I get no output at all. If I just do it in one it works ok.
Response by poster: Sorry. The 40 OK/NG/NA fields are all columns in each row.
They appear in every row that is entered into the database.
posted by JonnyRotten at 6:43 PM on May 27, 2006
They appear in every row that is entered into the database.
posted by JonnyRotten at 6:43 PM on May 27, 2006
select * from tablename where field1+field2+field3+[...]+field40 like '%NG%'
posted by blue mustard at 6:43 PM on May 27, 2006
posted by blue mustard at 6:43 PM on May 27, 2006
Response by poster: Where would I put that in my query? Right now I am trying to do it in the design view.
posted by JonnyRotten at 6:45 PM on May 27, 2006
posted by JonnyRotten at 6:45 PM on May 27, 2006
Or:
SELECT * FROM tablename WHERE 'NG' IN (field1, field2, field3...)
Not sure how big your table is, but that may be quicker than concatenating the fields and doing a wildcard search.
posted by Doofus Magoo at 6:47 PM on May 27, 2006
SELECT * FROM tablename WHERE 'NG' IN (field1, field2, field3...)
Not sure how big your table is, but that may be quicker than concatenating the fields and doing a wildcard search.
posted by Doofus Magoo at 6:47 PM on May 27, 2006
Switch to SQL view:
While in Design View, go to "View" in the menu at the top, then click on "SQL View". Now you can type anything you want.
posted by SuperSquirrel at 6:50 PM on May 27, 2006
While in Design View, go to "View" in the menu at the top, then click on "SQL View". Now you can type anything you want.
posted by SuperSquirrel at 6:50 PM on May 27, 2006
If you're going with blue mustard's suggestion, you'd put the following formula in the first "row" of a new column in the layout grid (where the field name normally goes):
Expr1: field1+field2+field3+[...]+field40
Then for the criteria, you'd enter (I think) LIKE '%NG%'
Although I think maybe Access uses * for the wildcards instead of %.
posted by Doofus Magoo at 6:51 PM on May 27, 2006
Expr1: field1+field2+field3+[...]+field40
Then for the criteria, you'd enter (I think) LIKE '%NG%'
Although I think maybe Access uses * for the wildcards instead of %.
posted by Doofus Magoo at 6:51 PM on May 27, 2006
Try this in Design view:
Field: [Field1]+[Field2]+[Field3]+...+[Field40]
Criteria: Like '*NG*'
posted by blue mustard at 6:51 PM on May 27, 2006
Field: [Field1]+[Field2]+[Field3]+...+[Field40]
Criteria: Like '*NG*'
posted by blue mustard at 6:51 PM on May 27, 2006
Response by poster: [Audits]![OpStdA] + [Audits]![DefectSampleA] + [Audits]![MasterLabelA] + [Audits]![BoardConditionA] + [Audits]![PartRankingA] + [Audits]![AssocOpStdA] + [Audits]![ScalesA] + [Audits]![AssyUsedA] + [Audits]![QualityAlertA] + [Audits]![SUCheckA] + [Audits]![MatLotTraceA] + [Audits]![ProdTicketA] + [Audits]![PokeYokeA] + [Audits]![5SA] + [Audits]![SORA] + [Audits]![CushionAlarmA] + [Audits]![CushLocA] + [Audits]![CycleTimeAlarmA] + [Audits]![CycleTimeA] + [Audits]![ChargeAlarmA] + [Audits]![ChargeTimeA] + [Audits]![KSSMoldTempA] + [Audits]![KSSFoamAgentA] + [Audits]![KSSStaplerA] + [Audits]![EORA] + [Audits]![CorrectLabelsA] + [Audits]![ClockNumberInfoA] + [Audits]![LabelsNumberedA] + [Audits]![LabelsAfterBinFullA] + [Audits]![CorrectLabelsOnBinA] + [Audits]![RepackProdA] + [Audits]![LabelsOnContA] + [Audits]![RelabelProdA] + [Audits]![WorkInstructionsA] + [Audits]![LotControlA] + [Audits]![QARoundsA] + [Audits]![FlashOKA] + [Audits]![ShortsA] + [Audits]![VisDefectsA]
Thats what the field list looks like.. Good Grief.
posted by JonnyRotten at 7:06 PM on May 27, 2006
Thats what the field list looks like.. Good Grief.
posted by JonnyRotten at 7:06 PM on May 27, 2006
Response by poster: OK. Now I have it saying this:
Expr1: [OpStdA]+[DefectSampleA]+[MasterLabelA]+[]![BoardConditionA]+[PartRankingA]+[AssocOpStdA]+[ScalesA]+[AssyUsedA]+[QualityAlertA]+[SUCheckA]+[MatLotTraceA]+[ProdTicketA]+[PokeYokeA]+[5SA]+[SORA]+[CushionAlarmA]+[CushLocA]+[CycleTimeAlarmA]+[CycleTimeA]+[ChargeAlarmA]+[ChargeTimeA]+[KSSMoldTempA]+[KSSFoamAgentA]+[KSSStaplerA]+[EORA]+[CorrectLabelsA]+[ClockNumberInfoA]+[LabelsNumberedA]+[LabelsAfterBinFullA]+[CorrectLabelsOnBinA]+[RepackProdA]+[LabelsOnContA]+[RelabelProdA]+[WorkInstructionsA]+[LotControlA]+[QARoundsA]+[FlashOKA]+[ShortsA]+[VisDefectsA]
With the criteria of LIKE "NG"
But when I run the query i get the error:
Query must hav eat least one destination field
posted by JonnyRotten at 7:18 PM on May 27, 2006
Expr1: [OpStdA]+[DefectSampleA]+[MasterLabelA]+[]![BoardConditionA]+[PartRankingA]+[AssocOpStdA]+[ScalesA]+[AssyUsedA]+[QualityAlertA]+[SUCheckA]+[MatLotTraceA]+[ProdTicketA]+[PokeYokeA]+[5SA]+[SORA]+[CushionAlarmA]+[CushLocA]+[CycleTimeAlarmA]+[CycleTimeA]+[ChargeAlarmA]+[ChargeTimeA]+[KSSMoldTempA]+[KSSFoamAgentA]+[KSSStaplerA]+[EORA]+[CorrectLabelsA]+[ClockNumberInfoA]+[LabelsNumberedA]+[LabelsAfterBinFullA]+[CorrectLabelsOnBinA]+[RepackProdA]+[LabelsOnContA]+[RelabelProdA]+[WorkInstructionsA]+[LotControlA]+[QARoundsA]+[FlashOKA]+[ShortsA]+[VisDefectsA]
With the criteria of LIKE "NG"
But when I run the query i get the error:
Query must hav eat least one destination field
posted by JonnyRotten at 7:18 PM on May 27, 2006
Rather then trying to concat, just do
You want to put something like
"select * from Audits where ...."
where the ellipsis is that expression.
Also if I were you I would just to = rather then this concatination thing. It should be a lot faster.
"select * from audits where = OpStdA = 'NG' or DefectSampleA = 'NG' or BoardConditionA = 'NG' or..."
And just include one expression for each column.
posted by delmoi at 7:31 PM on May 27, 2006
You want to put something like
"select * from Audits where ...."
where the ellipsis is that expression.
Also if I were you I would just to = rather then this concatination thing. It should be a lot faster.
"select * from audits where = OpStdA = 'NG' or DefectSampleA = 'NG' or BoardConditionA = 'NG' or..."
And just include one expression for each column.
posted by delmoi at 7:31 PM on May 27, 2006
Response by poster: I uploaded it to here if anyone wants to grab it and see if they can get it to work. I tried doing it the SQL expression way, but I still get no results from that either.
posted by JonnyRotten at 7:35 PM on May 27, 2006
posted by JonnyRotten at 7:35 PM on May 27, 2006
Just CASCADE your criterion slection like this and it will work:
LOOK AT ME!!!
posted by bim at 7:35 PM on May 27, 2006
LOOK AT ME!!!
posted by bim at 7:35 PM on May 27, 2006
"I tried putting 'NG' in the criteria for each of the 40 fields and I get no output at all. If I just do it in one it works ok."
No kidding: you criteria query ask for rows where ALL the answers where 'NG'. You need 39 "or" clauses, or to do the clever hack blue musrtard suggests.
You've got naive table structure with multiple (39) redundancies, and a good example of why thorough table design maters.
As it happens, I encountered something similar while volunteering on a campaign; the voter-database vendor (Blaemire) voter table included each voter's votes for several campaigns, arranged just like your questionnaire: field after filed of "Primary 92", "General 92", etc.
This made looking for patterns in the data a real chore: e.g., to find swing voters who voted in the republican primaries in off year elections, select * from voters where "Primary 92" = 'D' and "Primary 94' = 'R' and "Primary 96" = 'D' and "Primary 98" ....
(Even more fun as they may not have voted in the primary, etc.)
The solution is to re-do your database, trading space for less cumbersome queries:
create table election( id autoincrement, year int, is_primary int ) -- don't use bit for is_primary, bit is less time-efficient
insert election values ( 1996, 1 ) -- 1996 primary
insert election values ( 1996, 0 ) -- 1996 general
-- etc.
create table vote_type ( id auto_increment, vote char(1) ) ;
insert vote_type values ( 'D' ) -- etc., one row for vote type
create table votes ( id autoincrement, election_id int, voter_id int, vote_type_id int )
-- put a clustered index on (voter_id, election_id), and an index on election id, and add referential integrity constraints
-- Now populate your new tables; this will take a long time, and create millions of rows, but it's worth it
insert votes
select a.voter_id, ( select election_id from election e where e.year = 1996 and e.is_primary = 1 ), a."Primary 1996"
where a."Primary 1996" is not null
insert votes
select a.voter_id, ( select election_id from election e where e.year = 1996 and e.is_primary = 0 ), a."General 1996"
where a."General 1996" is not null
-- etc., one select per election.
Now instead of a horizontal list of elections with arbitrary names, we have a vertical list of one row per voter per election.
To find voters who voted in Dem Presidential primaries and Republican off-year primaries, we just have to ask:
Voters who voted 'D' (or didn't vote) in all Presidential primaries, and voted 'R' (or didn't vote) in all off-year primaries:
select voter_id from voter a
where not exists ( select * from votes b, elections c, voter_type d where b.election_id = c.id and b.vote_type_id = d.id
and d.vote = 'R' and c.year % 4 = 0 and c.is_primary = 1 and b.voter_id = a.voter_id)
and not exists ( select * from votes b, elections c, voter_type d where b.election_id = c.id and b.vote_type_id = d.id
and d.vote = 'D' and c.year % 4 = 2 and c.is_primary = 1 and b.voter_id = a.voter_id)
Of course, creating views named primary_votes or off_year_primaries will make the SQL more self-explanatory, and is left as an exercise for the reader.
The election problem, of course, is (almost) isomorphic to your problem, and the solution is (almost) the same.
1. create a table of possible answers, "OK", "NG", "NA" -- this is isomorphic to the vote_type table
2. create a table of questions, in your case forty of them -- isomorphic to the reelections table
3. create a table that relates an answer-series (a voter, each row in your current table) to each of the forty questions. This is just like a normal many-to-many table, but includes an additional field for the actual answer.
4. either add to the third table a column for the additional NG information (sloppy) or put that in another table that joins on the id in the third table (better normalization).
Finally, select all rows in the many-to-many table with answer_type = "NG'
posted by orthogonality at 7:36 PM on May 27, 2006
No kidding: you criteria query ask for rows where ALL the answers where 'NG'. You need 39 "or" clauses, or to do the clever hack blue musrtard suggests.
You've got naive table structure with multiple (39) redundancies, and a good example of why thorough table design maters.
As it happens, I encountered something similar while volunteering on a campaign; the voter-database vendor (Blaemire) voter table included each voter's votes for several campaigns, arranged just like your questionnaire: field after filed of "Primary 92", "General 92", etc.
This made looking for patterns in the data a real chore: e.g., to find swing voters who voted in the republican primaries in off year elections, select * from voters where "Primary 92" = 'D' and "Primary 94' = 'R' and "Primary 96" = 'D' and "Primary 98" ....
(Even more fun as they may not have voted in the primary, etc.)
The solution is to re-do your database, trading space for less cumbersome queries:
create table election( id autoincrement, year int, is_primary int ) -- don't use bit for is_primary, bit is less time-efficient
insert election values ( 1996, 1 ) -- 1996 primary
insert election values ( 1996, 0 ) -- 1996 general
-- etc.
create table vote_type ( id auto_increment, vote char(1) ) ;
insert vote_type values ( 'D' ) -- etc., one row for vote type
create table votes ( id autoincrement, election_id int, voter_id int, vote_type_id int )
-- put a clustered index on (voter_id, election_id), and an index on election id, and add referential integrity constraints
-- Now populate your new tables; this will take a long time, and create millions of rows, but it's worth it
insert votes
select a.voter_id, ( select election_id from election e where e.year = 1996 and e.is_primary = 1 ), a."Primary 1996"
where a."Primary 1996" is not null
insert votes
select a.voter_id, ( select election_id from election e where e.year = 1996 and e.is_primary = 0 ), a."General 1996"
where a."General 1996" is not null
-- etc., one select per election.
Now instead of a horizontal list of elections with arbitrary names, we have a vertical list of one row per voter per election.
To find voters who voted in Dem Presidential primaries and Republican off-year primaries, we just have to ask:
Voters who voted 'D' (or didn't vote) in all Presidential primaries, and voted 'R' (or didn't vote) in all off-year primaries:
select voter_id from voter a
where not exists ( select * from votes b, elections c, voter_type d where b.election_id = c.id and b.vote_type_id = d.id
and d.vote = 'R' and c.year % 4 = 0 and c.is_primary = 1 and b.voter_id = a.voter_id)
and not exists ( select * from votes b, elections c, voter_type d where b.election_id = c.id and b.vote_type_id = d.id
and d.vote = 'D' and c.year % 4 = 2 and c.is_primary = 1 and b.voter_id = a.voter_id)
Of course, creating views named primary_votes or off_year_primaries will make the SQL more self-explanatory, and is left as an exercise for the reader.
The election problem, of course, is (almost) isomorphic to your problem, and the solution is (almost) the same.
1. create a table of possible answers, "OK", "NG", "NA" -- this is isomorphic to the vote_type table
2. create a table of questions, in your case forty of them -- isomorphic to the reelections table
3. create a table that relates an answer-series (a voter, each row in your current table) to each of the forty questions. This is just like a normal many-to-many table, but includes an additional field for the actual answer.
4. either add to the third table a column for the additional NG information (sloppy) or put that in another table that joins on the id in the third table (better normalization).
Finally, select all rows in the many-to-many table with answer_type = "NG'
posted by orthogonality at 7:36 PM on May 27, 2006
LOL. Dudes, this is crazy. Don't bring in a cannon to kill a mouse!
I looked at the file that was uploaded. You have a table but haven't created a query yet. If you create the query then do as I said above, it will work. I did a test example and it worked just fine.
posted by bim at 7:42 PM on May 27, 2006
I looked at the file that was uploaded. You have a table but haven't created a query yet. If you create the query then do as I said above, it will work. I did a test example and it worked just fine.
posted by bim at 7:42 PM on May 27, 2006
Here's the database table that I created in excel and then imported into ACCESS.
posted by bim at 7:48 PM on May 27, 2006
posted by bim at 7:48 PM on May 27, 2006
And here's the little database that I created as a test example. One table one query.
sample database
Just open the database, double click on query1 to see the data, then click on that little pencil & protractor icon to the left of the save disk icon (ie.e the one on the far left) to switch to the design view and see how I set the query up, then I click the exclamation point icon to run the query and voila.
It's easy.
posted by bim at 7:59 PM on May 27, 2006
sample database
Just open the database, double click on query1 to see the data, then click on that little pencil & protractor icon to the left of the save disk icon (ie.e the one on the far left) to switch to the design view and see how I set the query up, then I click the exclamation point icon to run the query and voila.
It's easy.
posted by bim at 7:59 PM on May 27, 2006
Response by poster: Bim
I deleted the queries as they didn't work, but believe me I have been making and remaking them for hours.
The cascading would work perfect if it wasn;t so many fields. I tried doing it, and could only cascade to the 8th level down. Then I ran out of spots to go down.
In regards to it being poor table design, From the Process Audit Sheet I trying to replicate, there are 40 lines each one has a Ok/NG/NA check box, followed by a line for description of the problem, then a third YES/NO box for if the problem was addressed.
Each line is a completely different question, and I only the lines marked NG Need to have anything in the other two places.
I need to make one form that they can quickly go through and combo box the OK/NG/NA part and eloborate from there. I have been beating my head for days trying to think of a better table structure. So I guess if its "naive table structure" I'll have to admit I could not for the life of me think of a better way to do it.
posted by JonnyRotten at 8:05 PM on May 27, 2006
I deleted the queries as they didn't work, but believe me I have been making and remaking them for hours.
The cascading would work perfect if it wasn;t so many fields. I tried doing it, and could only cascade to the 8th level down. Then I ran out of spots to go down.
In regards to it being poor table design, From the Process Audit Sheet I trying to replicate, there are 40 lines each one has a Ok/NG/NA check box, followed by a line for description of the problem, then a third YES/NO box for if the problem was addressed.
Each line is a completely different question, and I only the lines marked NG Need to have anything in the other two places.
I need to make one form that they can quickly go through and combo box the OK/NG/NA part and eloborate from there. I have been beating my head for days trying to think of a better table structure. So I guess if its "naive table structure" I'll have to admit I could not for the life of me think of a better way to do it.
posted by JonnyRotten at 8:05 PM on May 27, 2006
Best answer: From design view, move into SQL view (view menu), and paste the following, overwriting whatever is there:
select * From Audits where [OpStdA]+[DefectSampleA]+[MasterLabelA]+[BoardConditionA]+[PartRankingA]+[AssocOpStdA]+[ScalesA]+[AssyUsedA]+[QualityAlertA]+[SUCheckA]+[MatLotTraceA]+[ProdTicketA]+[PokeYokeA]+[5SA]+[SORA]+[CushionAlarmA]+[CushLocA]+[CycleTimeAlarmA]+[CycleTimeA]+[ChargeAlarmA]+[ChargeTimeA]+[KSSMoldTempA]+[KSSFoamAgentA]+[KSSStaplerA]+[EORA]+[CorrectLabelsA]+[ClockNumberInfoA]+[LabelsNumberedA]+[LabelsAfterBinFullA]+[CorrectLabelsOnBinA]+[RepackProdA]+[LabelsOnContA]+[RelabelProdA]+[WorkInstructionsA]+[LotControlA]+[QARoundsA]+[FlashOKA]+[ShortsA]+[VisDefectsA] like '*NG*'
You canthen switch back to Design view, if you like.
posted by blue mustard at 8:05 PM on May 27, 2006
select * From Audits where [OpStdA]+[DefectSampleA]+[MasterLabelA]+[BoardConditionA]+[PartRankingA]+[AssocOpStdA]+[ScalesA]+[AssyUsedA]+[QualityAlertA]+[SUCheckA]+[MatLotTraceA]+[ProdTicketA]+[PokeYokeA]+[5SA]+[SORA]+[CushionAlarmA]+[CushLocA]+[CycleTimeAlarmA]+[CycleTimeA]+[ChargeAlarmA]+[ChargeTimeA]+[KSSMoldTempA]+[KSSFoamAgentA]+[KSSStaplerA]+[EORA]+[CorrectLabelsA]+[ClockNumberInfoA]+[LabelsNumberedA]+[LabelsAfterBinFullA]+[CorrectLabelsOnBinA]+[RepackProdA]+[LabelsOnContA]+[RelabelProdA]+[WorkInstructionsA]+[LotControlA]+[QARoundsA]+[FlashOKA]+[ShortsA]+[VisDefectsA] like '*NG*'
You canthen switch back to Design view, if you like.
posted by blue mustard at 8:05 PM on May 27, 2006
Response by poster: Blue Mustard! That worked perfectly! I cannot for the life of me see whats different in yours then the ones I had tried, but it works so You're my hero.
orthogonality I get what your saying about doing this over several different tables, But I am just using this and a few other small tasks to learn Access. This is about my third project I've done, so I expect my data to be on the cumbersom side.
Thanks to everyone for the great advice!
posted by JonnyRotten at 8:17 PM on May 27, 2006
orthogonality I get what your saying about doing this over several different tables, But I am just using this and a few other small tasks to learn Access. This is about my third project I've done, so I expect my data to be on the cumbersom side.
Thanks to everyone for the great advice!
posted by JonnyRotten at 8:17 PM on May 27, 2006
In case you are interested, this is probably how I would do the model, based on what I gleaned from your description:
Person table
=========
PersonID (PK autoincrement not null)
FirstName (varchar (50) not null)
LastName (varchar (50) not null)
Issue table
========
IssueID (PK autoincrement not null)
PersonID (int not null)
IssueStatusID (int not null)
Description (varchar (1000) null)
Resolved (bit null)
IssueStatus table
=============
IssueStatusID (PK autoincrement not null)
Name (varchar (10) not null)
posted by SNACKeR at 5:28 AM on May 28, 2006
Person table
=========
PersonID (PK autoincrement not null)
FirstName (varchar (50) not null)
LastName (varchar (50) not null)
Issue table
========
IssueID (PK autoincrement not null)
PersonID (int not null)
IssueStatusID (int not null)
Description (varchar (1000) null)
Resolved (bit null)
IssueStatus table
=============
IssueStatusID (PK autoincrement not null)
Name (varchar (10) not null)
posted by SNACKeR at 5:28 AM on May 28, 2006
Response by poster: Well. Let me set the scenerio a bit better for you. The auditor goes from one press to the next in our factory at each press they fill out a 40 question audit form. Starting a new form each press. Each press visit will need to have all 40 questions tied to that visit, and they will be doing Audits on 60 presses a night. I figured that 60 rows X 3 shifts would be more efficient then 40 seperate rows (one for each questions) X 60 visits X 3 shifts.
Currently the department manager takes all the filled out forms from the previous three shifts each day and sorts them for any ones that have any No Good fields marked. Then he inputs them into this crazy excel spreadsheet, and the entire process takes him 3 hours a day to do.
Now he wants to go and pay 50 thousand for a program lets the auditors enter the data on a mobil device, and then gives him the reports he needs.
As the IT guy at our factory I feel thats just silly and I think it can be done with a access database just as easily, and save the company some money.
posted by JonnyRotten at 6:07 AM on May 28, 2006
Currently the department manager takes all the filled out forms from the previous three shifts each day and sorts them for any ones that have any No Good fields marked. Then he inputs them into this crazy excel spreadsheet, and the entire process takes him 3 hours a day to do.
Now he wants to go and pay 50 thousand for a program lets the auditors enter the data on a mobil device, and then gives him the reports he needs.
As the IT guy at our factory I feel thats just silly and I think it can be done with a access database just as easily, and save the company some money.
posted by JonnyRotten at 6:07 AM on May 28, 2006
Yeah, $50,000 is crazy. We are talking about a very simple application here. Good luck on your quest!
posted by SNACKeR at 6:17 PM on May 28, 2006
posted by SNACKeR at 6:17 PM on May 28, 2006
« Older Help Fix My Dell^H^H^H^HMac, please. | Know of a good place to watch the World Cup in... Newer »
This thread is closed to new comments.
SELECT * FROM tableName WHERE (tableName.column1 LIKE '%NG%') or (tableName.column2 LIKE '%NG%') etc. etc. etc...
If this isn't what you mean, please clarify your terms: columns, rows, cells, etc.
posted by Civil_Disobedient at 6:37 PM on May 27, 2006