Solved

Need to know yes or no from looking at lots of records with yes or no answers.

Posted on 2016-08-01
17
82 Views
Last Modified: 2016-08-05
I am working on a production report.  I need to know if a assembly has been fully issued to based on the issued state of each part.
I think the info will help to explain.

If we look at assemblyseq 0 it is easy to see it is not issued to because all the issued data is no and on assemblyseq 1 they are all yes, but assemblyseq 2 has  2 yes and 1 no.  Results I am looking for would be like this,
F100,0,200-001,No
F100,1,200-002,Yes
F100,2,200-003,No

If they are not all yes I want it to return a no, would be nice as it check the query or table which ever is easiest that it would create  or import the results into a new table.


JobNum,AssemblySeq,Asm-PartNum,Asm-Material,Issued
F100,0,200-001,123-123,No
F100,0,200-001,123-124,No
F100,0,200-001,123-125,No
F100,0,200-001,123-126,No
F100,0,200-001,123-127,No
F100,1,200-002,123-128,Yes
F100,1,200-002,123-129,Yes
F100,1,200-002,123-130,Yes
F100,1,200-002,123-131,Yes
F100,1,200-002,123-132,Yes
F100,1,200-002,123-133,Yes
F100,1,200-002,123-134,Yes
F100,2,200-003,123-135,No
F100,2,200-003,123-136,Yes
F100,2,200-003,123-137,Yes
F100,3,200-004,123-138,No
F100,3,200-004,123-139,Yes
F100,3,200-004,123-140,Yes
F100,4,200-005,123-141,No
F100,4,200-005,123-142,No
F100,4,200-005,123-143,No
F100,4,200-005,123-144,Yes
F100,7,200-006,123-145,Yes
F100,8,200-006,123-146,Yes
0
Comment
Question by:fasse
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 3
  • +2
17 Comments
 
LVL 15

Expert Comment

by:WalkaboutTigger
ID: 41737922
Edit the title of the post to read:
"Need to determine yes or no from looking at many records with yes or no answers."

Essentially this is a logical AND operation - all sub-assembly levels' status MUST be "yes" or the assembly is not complete, resulting in a "No" answer.

So you could simply write a where clause wherein status, or whatever the yes/no column is name, is "no".  The moment you hit a "no" value in that sub assembly, you can stop querying that sub assembly and go to the next one.

If, after iterating through all the sub assembly records for an assembly, you have not hit a "no" value, it means all of the sub assemblies for that assembly are set to yes and you can presume that assembly is complete.

If you need drill-down capabilities, that will be additional work.

I am, in fact, trying to lead you to the answer instead of writing it for you - it sounds like you somewhat know what you're doing and just need a bit of a hint.
0
 

Author Comment

by:fasse
ID: 41737934
That is what I get for typing in a hurry and not proof reading anything.  Question how do I edit a post?  Your thinking is correct that is the logic I was looking for.  I will see what I can do with it from there.
0
 
LVL 2

Expert Comment

by:Antonio Salva Ripoll
ID: 41738067
Hi Fasse.

Please be patient if my English is not so good, as it's not my mother language.

Another way to get what you are looking for is creating a simple query.

You can count the items where iSSued = "No", Grouping By JobNum and AssemblySeq.

If the count is greater than 0, then the Assembly Seq uence is not completed.

If you only use group By JobNum, then you are checking all assemblies.

And, of course, you can use both queries and get all information.

You can use a recordset loaded from the queries and get the values.


Also, instead of using recordsets and queries, you can use domain aggregate functions; DCount will give you the values you need. If you need more help, I'll pleased in giving it.


Regards.

Antonio.
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 300 total points
ID: 41738083
I think you are looking for a query that looks something like:

Select Field1, Field2, Field3, iif(Count([Field4]) = Sum(iif(Field4 = "Yes", 1, 0)), "Yes", "No")
FROM yourTable
GROUP BY Field1, Field2, Field3

This will count the number of elements in each combination of field1, Field2, Field3, and will compare that number to the count of the number of [Field4] = "Yes".  If they are all "Yes", then it will return "Yes", otherwise, "No"
1
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 200 total points
ID: 41738147
check this.
SELECT JobNum,AssemblySeq,
       IIF(MAX(Issued) = MIN(Issued), MAX(Issued), "No") AS IssuedColumn
  FROM your_table
 GROUP BY JobNum,AssemblySeq

Open in new window

1
 

Author Comment

by:fasse
ID: 41740741
Dale it looks like your solution is what I am looking for.  This is my statement.
SELECT tbl-target-jobs.[jobnum], tbl-target-jobs.[assemblyseq], tbl-target-jobs.[asm-partnum], iif(Count(tbl-target-jobs.[issued]) = Sum(iif(tbl-target-jobs.[issued] = "Yes", 1, 0)), "Yes", "No")
FROM 'tbl-target-jobs'
GROUP BY jobnum, assemblyseq, asm-partnum

I get the error incomplete query clause.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 41741909
Did you try my query?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 41742294
You had single quotes around the table name, that may have been your problem, so remove those and see if that works.  Otherwise, lets start with this:

SELECT tbl-target-jobs.[jobnum]
, tbl-target-jobs.[assemblyseq]
, tbl-target-jobs.[asm-partnum]
, Count(tbl-target-jobs.[issued]) as CountIssued
, Sum(iif(tbl-target-jobs.[issued] = "Yes", 1, 0)) as CountIssuedYes
FROM tbl-target-jobs
GROUP BY jobnum, assemblyseq, asm-partnum

Please confirm that the [Issued] column is text, and not boolean.  If it is boolean, they you will have to replace "Yes" in either of the previous queries with -1
0
 

Author Comment

by:fasse
ID: 41744657
Dale issued is text and boolean.

I get an syntx error in the from clause.

Also Sharath I get the same error with yours.  I am not very good at sql so I really have no idea what is wrong with it.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 41744687
Sorry, cannot be Text and Boolean

A text field would contain the values "Yes" or "No", with a text field, you would do a criteria that looks like:

WHERE [Included] = "Yes"
or
WHERE [Included] = "No"

A boolean field, when viewing the table in design view will have a data type of Yes/No.  With a boolean field, you can either say:

WHERE [Issued] = Yes
or
WHERE [Issued] = -1
or
WHERE [Issued] = No
or
WHERE [Issued] = 0

If you are getting an Error in the FROM clause, then you probably have misspelled the table name, but it might also be a result of using [Issued] = "Yes" instead of [Issued] = -1
0
 

Author Comment

by:fasse
ID: 41744694
Sorry my head is not working right.  It is a text field not a boolean field.  I am sorry
0
 

Author Comment

by:fasse
ID: 41744699
The misspelling on the table  gave me a hint.  It does not like the - in the table name.  I removed it and it worked.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 41744735
"it worked"

Which query worked, the first one, or the second one?
0
 

Author Comment

by:fasse
ID: 41744740
SELECT tbltargetjobs.jobnum, tbltargetjobs.assemblyseq, tbltargetjobs.asmpartnum, Count(tbltargetjobs.issued) AS CountIssued, Sum(IIf(tbltargetjobs.[issued]="Yes",1,0)) AS CountIssuedYes
FROM tbltargetjobs
GROUP BY tbltargetjobs.jobnum, tbltargetjobs.assemblyseq, tbltargetjobs.asmpartnum;
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 41744861
so, have you tried the first one?
0
 
LVL 41

Expert Comment

by:Sharath
ID: 41744866
Can you post the error message with my query?
0
 

Author Comment

by:fasse
ID: 41744950
The name of the table was what was causing issues on both Sharath and Dale's statements.  Once change both original suggestions worked perfectly.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

730 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