Record status (1 Viewer)

Gismo

Registered User.
Local time
Tomorrow, 00:16
Joined
Jun 12, 2017
Messages
1,298
Hi all,

Not sure how to handle this one

I have a table with approximately 1000 records

I would like to write some of the information into a new table as a summary for a quick view of outstanding transactions

If a registration has any of the 6 fields as incomplete, I want to update the table with just a no
If all the records in that field for the same registration has been completed, a yes to be saved in the new table

Will I have to create a query for each control I need to check or will i be able to do this utilizing one query?

Deferred will have a different criteria, if all are not deferred, I need a yes indication for completed
Sample

RegistrationSequenceApprovedCS RaisedRectifiedDeferred
XYZ
1​
YesYesYesNo
XYZ
2​
YesYesYesNo
XYZ
3​
YesYesYesNo
XYZ
4​
YesYesNoNo
XYZ
5​
YesYesYesNo
XYZ
6​
YesYesYesNo
XYZ
7​
YesYesNoNo
XYZ
8​
YesYesYesNo
Result
RegistrationApprovedCS RaisedRectifiedDeferred
XYZYesYesNoYes
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:16
Joined
May 7, 2009
Messages
19,169
just create a Queries:

1.first Query (query1)

select Registration, Count("1") As CountOfRegistration From yourTable Group by Registration;

2.second query:

Select Registration,
IIF(Nz(DCount("1","yourTable","Registration = '" & [Registration] & "' And Approved = 'Yes'"), 0) = [CountOfRegistration], "Yes", "No") As Approved,
IIF(Nz(DCount("1","yourTable","Registration = '" & [Registration] & "' And [CS Raised] = 'Yes'"), 0) = [CountOfRegistration], "Yes", "No") As [CS Raised],
IIF(Nz(DCount("1","yourTable","Registration = '" & [Registration] & "' And Rectified = 'Yes'"), 0) = [CountOfRegistration], "Yes", "No") As Rectified,
IIF(Nz(DCount("1","yourTable","Registration = '" & [Registration] & "' And Deffered = 'No'"), 0) = [CountOfRegistration], "Yes", "No") As Deffered
From Query1
 

June7

AWF VIP
Local time
Today, 14:16
Joined
Mar 9, 2014
Messages
5,423
I see only 4 yes/no fields, not 6.

Consider:

SELECT Table3.Registration, Not Count(IIf([Approved]=0,1,Null)) AS A, Not Count(IIf([CS_Raised]=0,1,Null)) AS C, Not Count(IIf([Rectified]=0,1,Null)) AS R, Not Count(IIf([Deferred]=0,Null,1)) AS D
FROM Table3
GROUP BY Table3.Registration;
 

Gismo

Registered User.
Local time
Tomorrow, 00:16
Joined
Jun 12, 2017
Messages
1,298
just create a Queries:

1.first Query (query1)

select Registration, Count("1") As CountOfRegistration From yourTable Group by Registration;

2.second query:

Select Registration,
IIF(Nz(DCount("1","yourTable","Registration = '" & [Registration] & "' And Approved = 'Yes'"), 0) = [CountOfRegistration], "Yes", "No") As Approved,
IIF(Nz(DCount("1","yourTable","Registration = '" & [Registration] & "' And [CS Raised] = 'Yes'"), 0) = [CountOfRegistration], "Yes", "No") As [CS Raised],
IIF(Nz(DCount("1","yourTable","Registration = '" & [Registration] & "' And Rectified = 'Yes'"), 0) = [CountOfRegistration], "Yes", "No") As Rectified,
IIF(Nz(DCount("1","yourTable","Registration = '" & [Registration] & "' And Deffered = 'No'"), 0) = [CountOfRegistration], "Yes", "No") As Deffered
From Query1
Something like this?

Query 1
SELECT [DAW Status].Registration, Count([DAW Status].[DAW No]) AS [DAW No Count]
FROM [DAW Status]
GROUP BY [DAW Status].Registration
HAVING ((([DAW Status].Registration)="bps-03"));

Query 2
1651650552440.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:16
Joined
May 7, 2009
Messages
19,169
you remove the DAW Status table from your query design.
you don't need to join them.
 

Gismo

Registered User.
Local time
Tomorrow, 00:16
Joined
Jun 12, 2017
Messages
1,298
you remove the DAW Status table from your query design.
you don't need to join them.
In my first query I dont have the other fields as required in the second query
Wont I need the original table?
 

Gismo

Registered User.
Local time
Tomorrow, 00:16
Joined
Jun 12, 2017
Messages
1,298
In my first query I dont have the other fields as required in the second query
Wont I need the original table?
I receive an error "You may have entered an operand without an operator"


1651653942427.png
 
Last edited:

June7

AWF VIP
Local time
Today, 14:16
Joined
Mar 9, 2014
Messages
5,423
Do you want to try suggestion in post #3?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:16
Joined
May 7, 2009
Messages
19,169
see [Aircraft Status Qry 1] in design view.
now, see the final query, Query1.
 

Attachments

  • DAWStatus.accdb
    556 KB · Views: 146

Gismo

Registered User.
Local time
Tomorrow, 00:16
Joined
Jun 12, 2017
Messages
1,298
see [Aircraft Status Qry 1] in design view.
now, see the final query, Query1.
I tried this as well, I see where I went wrong
I do have my DAW Status format as yes/no tick boxes
When I changed your sample to tick boxes it gives error but i am sorted now
Thank you
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:16
Joined
May 7, 2009
Messages
19,169
for Yes/No field, see Quer2.
 

Attachments

  • DAWStatus.accdb
    756 KB · Views: 162

SHANEMAC51

Active member
Local time
Tomorrow, 01:16
Joined
Jan 28, 2022
Messages
310
I would like to write some of the information into a new table as a summary for a quick view of outstanding transactions
Code:
SELECT [Registration], count([Sequence]) AS [wcount],
 iif(Sum([Approved] ='yes')=-wcount,"yes","no") AS wapp,
 iif(Sum([CS Raised]='yes')=-wcount,"yes","no") AS wcs,
 iif(Sum([Rectified]='yes')=-wcount,"yes","no") AS wrec,
 iif(Sum([Deferred] ='no') =-wcount,"yes","no") AS wdef
FROM [Daw Status 2]
GROUP BY [Registration];
 

Gismo

Registered User.
Local time
Tomorrow, 00:16
Joined
Jun 12, 2017
Messages
1,298
for Yes/No field, see Quer2.
Hi,

Just another update on this one

I need to calculate the sum off all the controls (Approved, CS raised ...) which has an indicator of -1 in TBL DAW Status

I would like draw a graph to indicate the progress of each section

Only Deferred should be all zero to have a 100% completion

Basically something like below
Will this be possible?

1652864968632.png
 

Gismo

Registered User.
Local time
Tomorrow, 00:16
Joined
Jun 12, 2017
Messages
1,298
I Managed to get this far but having issues dividing by zero

SELECT [Aircraft Status Qry].Registration, [Aircraft Status Qry].RegCount, [Aircraft Status Qry 2]![Approved Count]/[Aircraft Status Qry]![RegCount] AS [Approved %], [Aircraft Status Qry 2]![CS Raised Count]/[Aircraft Status Qry]![RegCount] AS [CS Raised %], [Aircraft Status Qry 2]![Commercial Count]/[Aircraft Status Qry]![RegCount] AS [Comm %], [Aircraft Status Qry 2]![Rectification Count]/[Aircraft Status Qry]![RegCount] AS [Rectification %], [Aircraft Status Qry 2]![Deferred Count]/[Aircraft Status Qry]![RegCount] AS [Deferred %]
FROM [Aircraft Status Qry] INNER JOIN [Aircraft Status Qry 2] ON [Aircraft Status Qry].Registration = [Aircraft Status Qry 2].Registration

1652879418644.png



1652879436751.png
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:16
Joined
Sep 21, 2011
Messages
14,048
You cannot divide by zero, so either exclude them, or perhaps set to 1, if formula permits.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:16
Joined
May 7, 2009
Messages
19,169
i created Query3 that will Count all those Entities.
then i created Query4 for the percentage.
 

Attachments

  • DAWStatus.accdb
    784 KB · Views: 133

Gismo

Registered User.
Local time
Tomorrow, 00:16
Joined
Jun 12, 2017
Messages
1,298
i created Query3 that will Count all those Entities.
then i created Query4 for the percentage.
Ah, thank you so much

I was on the right track
just had to tweek my summary code a bit to match your's

I now need to find out how to make a graph, I dont have much experience with graphs
 

Users who are viewing this thread

Top Bottom