Query with Multiple conditions to return result (1 Viewer)

dlafko1

New member
Local time
Today, 01:52
Joined
May 7, 2014
Messages
1
I am very New to SQL and ACCESS so please be patient with me while providing answers

Hello I am trying to do this I have a database that checks for Burlary Alarms at Locations and then need the Citation Part to return specific values. I can get it to return when there are greater than 3 alarms but it returns all records even ones that should not be cited. :banghead:

IN the Checking for Citations qry it pulls alarms greater than 3 as we cite on the 4th alarm. and generates a report this report shows all alarms previous and current and Should only be returning the listed group when there is a no for citation.

Example Freedom Toyota again has 4 alarms where they recived citations and 1 new alarm on 4/19/2014 where they did not recive a citation.. The qry pulls up all 5 so when they go to court the trooper can show the judge how many total alarms they had that were fault (which shows as #1) when they were cited previously and the new alarm to wich they are citing them for now.

PROBLEM: It is also pulling companies that have already been cited but show no new alarms that need to be cited on..

Example: OUtten Chrysler Dodge and Jeep.. Shows 4 alarms with all of them being cited I do not need these on the report since they are not up for citation. They all Say yes

The basic premise on citation would be they need to have been sent a warning letter so that columb would say yes and the cited columb would need to be a no.. If it is a No i need it to pull the previous yes's as well with the no as stated above.

I have attached the database as well
 

Attachments

  • Burg working.zip
    290 KB · Views: 76

June7

AWF VIP
Local time
Today, 00:52
Joined
Mar 9, 2014
Messages
5,423
So you only need businesses where there are >3 citations and only if 1 of those citations is No for WarningLetterSent and only records where OPTIONGROUP_FAULT_NOFAULT=1?

Consider:

SELECT FAULT.[BUSINESS NAME/ RESIDENCE NAME], FAULT.ADDRESS, FAULT.[Mailing Address], FAULT.CITY, FAULT.STATE, FAULT.ZIP, FAULT.Incident, FAULT.Date, FAULT.TROOPER, FAULT.[TWS Name], FAULT.[Warning Letter Sent], FAULT.CITED, FAULT.[NTC #], FAULT.OPTIONGROUP_FAULT_NOFAULT
FROM FAULT
WHERE OPTIONGROUP_FAULT_NOFAULT=1 AND [BUSINESS NAME/ RESIDENCE NAME] In (SELECT [BUSINESS NAME/ RESIDENCE NAME] FROM [FAULT] GROUP BY [BUSINESS NAME/ RESIDENCE NAME] HAVING Count(*)>3) AND [BUSINESS NAME/ RESIDENCE NAME] In (SELECT [Business Name/ Residence Name] FROM FAULT WHERE [Warning Letter Sent]="NO")
ORDER BY FAULT.ADDRESS, FAULT.Date;
 

plog

Banishment Pending
Local time
Today, 03:52
Joined
May 11, 2011
Messages
11,611
I am very New to SQL and ACCESS...

Why is step 1 locking down the database to make it inaccessible to developers? I can't get to your Relationship Tool, I have to save the thing to view the tables, I can't get into Design View of anything. Help us help you, give us a database that isn't a pain to get into.

With what I do see, I don't think you've structured your database correctly and will have to go back to step 1 of setting up your tables. 1 action query (DELETE, APPEND, INSERT) is a bad sign, 7 of 8 of your queries are those.

I suggest you unlock the thing, set up your relationship tool and post back here. Otherwise you are just going to face more issues that require more hacks in the future.
 

June7

AWF VIP
Local time
Today, 00:52
Joined
Mar 9, 2014
Messages
5,423
@Plog, what "Step 1" do you mean? I opened with shift key bypass and no problem viewing Relationships window (no relationships built).
 

plog

Banishment Pending
Local time
Today, 03:52
Joined
May 11, 2011
Messages
11,611
"Step 1" was an idiom. He prioritized what he shouldn't have. He spent time researching how to add unnecessary restrictions to the development of his database instead of researching the fundamentals of normalization. Not having the relationship tool completed furthers my point.
 

June7

AWF VIP
Local time
Today, 00:52
Joined
Mar 9, 2014
Messages
5,423
I agree development was bass ackwards but got around with shift key bypass.
 

Users who are viewing this thread

Top Bottom