at a loss

[Valix]

I have a table where i keep mission information. i am trying to build a
query to pull the date (for date specific info), the type of aircraft, and
the number of inbound and outbound mission numbers. I run a simple query
GROUPED BY date and type aircraft and COUNT both inbound and outbound mission
numbers. my problem is this:

lets say on 1 July i had 2 inbounds and 3 outbounds of this aircraft. when
i run the query i need to see 2 and 3 for that date. but i get 5 and 5. ive
tried critria Is Not Null and that changes nothing. What else can i do to
get it to not count the blanks?

 

[ghetto_banjo]

Is there a field for whether a mission is inbound or outbound? Group
by that one too if its a column.

 

[Valix]

i have two fields on the table. one for inbound mission number and one for
outbound mission number. if the aircraft is coming in and not going out then
there is only the inbound mission number, but for some reason it is counting
that as 1 in and 1 out.

 

[ghetto_banjo]

I dont think it will work in a query then. The only way it would work
in a query is if you had a field specifying it as inbound or outbound
and then a single numeric field for number of flights.


you could create a report with groupings and subtotals though.

 

[Valix]

just had a thought, I am recreating a database and imported all the mission
info. could that have something to do with why it is counting the blanks?
im struggling to figure this out, so as a test, i input some bogus info and
it counted it correctly.

 

[Bob Barrows [MVP]]

What are the datatypes of those fields?
I woud have used a single field myself ...

 

[Valix]

they are both text fields, due to the mission numbers being alpha and numeric
combinations. i need both fields due to some missions are in and out,
sometimes with different mission numbers.

 

[Lord Kelvan]

ok it is more than possible but it is not simple there may be a more
simple way but i cannot be bothered thinking of it

you need to do a query in three stages

i am presuming you are using the yes/no data type for the inbound
outbound fields and a date for the mission date

you need to build seven queries (i know it sounds like alot but it
breaks down each section and allows you to summarise the data for use

first using this sql

create (this will summarise all your inbound flights where there is an
inbound flight on that date)

SELECT mission.missiondate, Count(mission.inbound) AS inbound
FROM mission
WHERE (((mission.inbound)=True))
GROUP BY mission.missiondate;

and save it as qryinbound

next create (this will summarise all your outbound flights where there
is an outbound flight on that date)

SELECT mission.missiondate, Count(mission.outbound) AS outbound
FROM mission
WHERE (((mission.outbound)=True))
GROUP BY mission.missiondate;

and satve it as qryoutbound

next create (this will summarise all your outbound flights where there
is an outbound flight not on that date)

SELECT mission.missiondate, 0 AS outbound
FROM mission
GROUP BY mission.missiondate
HAVING (((Sum(mission.outbound))=0));

and save it as qrynotoutbound

next create (this will summarise all your inbound flights where there
is an inbound flight not on that date)

SELECT mission.missiondate, 0 AS inbound
FROM mission
GROUP BY mission.missiondate
HAVING (((Sum(mission.inbound))=0));

and save it as qrynotinbound

next create (this will combine the inbound and not inbound query into
one ready for the final query)

SELECT qryinbound.missiondate, qryinbound.inbound
FROM qryinbound
UNION SELECT qrynotinbound.missiondate, qrynotinbound.inbound
FROM qrynotinbound;

and save it as qryallinbound

next create (this will combine the outbound and not outbound query
into one ready for the final query)

SELECT qryoutbound.missiondate, qryoutbound.outbound
FROM qryoutbound
UNION SELECT qrynotoutbound.missiondate, qrynotoutbound.outbound
FROM qrynotoutbound;

and save it as qryalloutbound

and finnally create (this will summarise the data and give it to you
in the form i believe you are looking for

SELECT qryallinbound.missiondate, qryallinbound.inbound,
qryalloutbound.outbound
FROM qryallinbound INNER JOIN qryalloutbound ON
qryallinbound.missiondate = qryalloutbound.missiondate;

and save it as whatever and this query will give you the required data
this will of course only give you data

and of course name the fields and tables in my sql queries as that
ever you have named them i have just used obvious names such as
mission and inbound outbound and missiondate so you will understand

the test data i used was
id missiondate inbound outbound
1 1/01/2008 Yes No
2 1/01/2008 Yes No
3 1/01/2008 No Yes
4 1/01/2008 No Yes
5 1/01/2008 No Yes
6 1/02/2008 Yes No
7 1/03/2008 No Yes

the results i got was

missiondate inbound outbound
1/01/2008 2 3
1/02/2008 1 0
1/03/2008 0 1

i do hope this helps

regards
kelvan

 

[Lord Kelvan]

ok since they are text you need to make a few ammendments

insted of sum use count in qrynotinbound and qrynotoutbound

and insten of true use is not null in qryinbound and qryoutbound

 

[Lord Kelvan]

sorry for multiple postking i thought i woudl display this for aid

WHERE (((mission.outbound) is not null))

HAVING (((count(mission.inbound))=0))

 

[John Spencer]

SELECT [Date], Abs(Sum(Len(Trim(MissionIN & "")>0))) as Inbound
, Abs(Sum(Len(Trim(MissionOut & "")>0))) as OutBound
FROM YourTable
Group BY [Date]

I am making the assumption that you fields can contain zero-length
strings or that you are hooked to another data source that stores spaces
in a field by definition (char fields in SQL for instance).

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Valix]

Thank you all for the information, but I believe I lost something in my
translation to what i am trying for: (i will explain more in detail) sorry
for the confusion.

One table of information is used to input Mission Information. This Table
contains the date the mission happened (date format), the type of aircraft it
was (text format), the inbound mission number (text format) and the outbound
mission number (text format) was.

Not all missions have both an inbound and outbound.

What i am trying to build is part of a report that shows between date X and
date Y (a full months worth of information normally) that we had X many
inbound and outbound missions for type aircraft A, X many inbound and
outbound missions for type aircraft B, etc...

the issues I am having are the following:
- I have 15 different types of aircraft I need to report on
- I had previously built a database with all the information, and had
querys pulling the information (date specific), counting the mission numbers
and it worked fine. That database was built in '03 Access.

This new database is in 07 Access, but it counts all blocks. example:
DATE Type AC IN Mission Number OUT Mission Number

1 July 747 XXX XXXX XX XXX
1 July 747 XXX XXXX XX XXX
1 July 747
XXX XXXX XX XXX

what my query is doing is instead of showing for 747's on 1 July that there
was 2 ins and 1 out, it shows 3 ins and 3 outs.

hope this clears this up some.

thank you in advance for any help on this matter

 

[Lord Kelvan]

i know it is a bit of work but try my queries it sould give you a date
just add the ac type field to the select of each of the queries and
the group by's i have tested it and it did work and thoes are the sql
statements of my queries but it is up to you

 

[Lord Kelvan]

tell me is this an example of what you are looking for
missiondate actype outbound inbound
1/01/2008 747 1 2
1/01/2008 767 2 0
1/02/2008 767 0 1
1/03/2008 747 1 0

 

[Valix]

yes, that is what i need, and from that built a totals query to show 2 blocks
for the report, to show the full time frame:

TYPE AC INS OUTS
747 XX XX

 

[Lord Kelvan]

ok i got thoes resaults for a series of queries with some slight mods
to the ones above ill paste them here and then ill pase a summary
query to sum the blocks as well

SELECT mission.missiondate, Count(mission.inbound) AS inbound,0 as
outbound, mission.actype
FROM mission
WHERE (((mission.inbound) Is Not Null))
GROUP BY mission.missiondate, mission.actype
union all
SELECT mission.missiondate, 0 AS inbound,0 as outbound, mission.actype
FROM mission
GROUP BY mission.missiondate, mission.actype
HAVING (((Count(mission.inbound))=0))
union all
SELECT mission.missiondate, 0 as inbound, Count(mission.outbound) AS
outbound, mission.actype
FROM mission
WHERE (((mission.outbound) Is Not Null))
GROUP BY mission.missiondate, mission.actype
UNION ALL SELECT mission.missiondate, 0 as inbound, 0 AS outbound,
mission.actype
FROM mission
GROUP BY mission.missiondate, mission.actype
HAVING (((Count(mission.outbound))=0));

save this as qryinndoutsummary

SELECT qryinndoutsummary.missiondate, qryinndoutsummary.actype,
Sum(qryinndoutsummary.inbound) AS inbound,
Sum(qryinndoutsummary.outbound) AS outbound
FROM qryinndoutsummary
GROUP BY qryinndoutsummary.missiondate, qryinndoutsummary.actype;

save this as qrymissiondata

SELECT qrymissiondata.actype, Sum(qrymissiondata.inbound) AS
SumOfinbound, Sum(qrymissiondata.outbound) AS SumOfoutbound
FROM qrymissiondata
GROUP BY qrymissiondata.actype;

save this as qryblockdata

these three sql queries will get you exatally what you want all you
have to do is alter the fieldnames and table names do remember if your
field name has spaces put [] around it

i hope this helps

regards
kelvan

 

[Valix]

Thank you.

that worked for exactly what i needed.

 

[Lord Kelvan]

no problem