Help writing SQL query

[Francois]

Hi, I need some help with a Microsoft Access query that I have been trying
to get working all day. Here is the scenario. The example below is a
contrived "car-parts" one. The real system is for a report in a Haberdashery
order entry system.


In my simplified example, I have is three tables.

Table 1: Car
Field 1: CarID, number

Table 2: Radio
Field 1: CarID,number 'the Car ID in the Car table
Field 2: RadioName,text 'The Radio name
Field 3: Red,number 'either 1 or 0
Field 4: Blue,number 'either 1 or 0
Field 5: Green,number 'either 1 or 0
Field 6: Black,number 'either 1 or 0


Table 3: Seat
Field 1: CarID,number 'the Car ID in the Car table
Field 2: SeatName,text 'The Seat name
Field 3: Red,number 'either 1 or 0
Field 4: Blue,number 'either 1 or 0
Field 5: Green,number 'either 1 or 0
Field 6: Black,number 'either 1 or 0

In English, what I want is to perform a database query to count the all the
car IDs that have the count of
Seat.Red+Seat.Blue+Seat.Green+Seat.Black+Radio.Red+Radio.Blue+Radio.Green+Radio.Black
= to a certain number, say 3 or 4 or 5 or 6 or whatever, I will change this
in my VBA code as required.

I have been working on a query like this. This works!

SELECT COUNT(Car.CarID)
FROM Car,
Radio
WHERE Car.CarID=Radio.CarID
HAVING Radio.Red+Radio.Blue+Radio.Green+Radio.Black=3



But, it isn't really what I want. But I am close. I want it to count the
number of CAR IDs where

Radio.Red+Radio.Blue+Radio.Green+Radio.Black+Seat.Red+Seat.Blue+Seat.Green+Seat.Black=3

but the following won't work

SELECT COUNT(Car.CarID)
FROM Car,
Radio,
Seat
WHERE Car.CarID=Radio.CarID
AND Car.CarID=Seat.CarID
HAVING
Radio.Red+Radio.Blue+Radio.Green+Radio.Black+Seat.Red+Seat.Blue+Seat.Green+Seat.Black=3

because, although there is always a Car.CarID, there is sometimes only a
corresponding CarID in the Seats table, sometimes there is a only a
corresponding CarID in the Radio table, sometimes there is neither, and
sometimes there is both. The query above only works when there are both, I
want it to work in all situations.

I know this has something to do with outer joins but I can't work out the
syntax and when I attempt to use the design tool in Access, is creates this
big really overcomplicated messy query that I can't work out. I am using
Access 2000 and Access 97.

Thank you for your help.
Francois

 

[PC Datasheet]

Why don't you show us the real Haberdashery order entry system. The tables
in your contrived example are all wrong!

 

[Francois]

in your contrived example are all wrong!
The real system is very complicated and there would be too much irrelevant
information to post. The contrived system represents the part of the real
system I am having difficulty with simplicity. Irrespective of whether you
think the tables in my contrived system are right or wrong, I still require
some help writing an SQL query for the table structure as it stands. And I
know it can be done with outer joins can't work out the syntax.
Francois

 

[PC Datasheet]

If the contrived tables design represents the table design of the real
system, then it can be concluded that table design of the real system is
wrong. Thus the crux of the problem!

 

[tina]

try

SELECT Count(CarID) AS CountOfCarID
FROM (SELECT Car.CarID, Radio.Red + Radio.Blue + Radio.Green + Radio.Black +
Seat.Red + Seat.Blue+ Seat.Green + Seat.Black As TotalColors
FROM (Car LEFT JOIN Radio ON Car.CarID = Radio.CarID) LEFT JOIN Seat ON
Car.CarID = Seat.CarID)
WHERE TotalColors=3;

hth

 

[Francois]

Thank you Tina. I am sure the SQL query is something trivial like we have
both written. However your query is not correct either.

For example, try it with the following data:

Car table, 6 rows as follows.
CarID: 1, 2, 3, 4, 5, and 6

Radio Table
CarID=1, RadioName=one,red=1,blue=0,green=0,black=0
CarID=2, RadioName=two,red=1,blue=1,green=0,black=0
CarID=3, RadioName=three_1,red=1,blue=1,green=1,black=0
CarID=4, RadioName=three_2,red=1,blue=1,green=0,black=1

Seat Table
CarID=1, SeatName=seat1,red=0,blue=1,green=1,black=0
CarID=2, SeatName=seat2,red=1,blue=0,green=0,black=0
CarID=3, SeatName=seat3,red=1,blue=0,green=0,black=0
CarID=6, SeatName=seat6,read=1,blue=1,green=1,black=0

Your query returns 2 (for CarIDs being 1 and 2). The correct answer is 3
(for CarIDs=1, 2, and 6).
Thank you for spending time on this.

Francois

 

[tina]

oops, so much for limited testing! <g>
try this instead

SELECT Count(CarID) AS CountOfCarID
FROM (SELECT Car.CarID, Nz(Radio.Red + Radio.Blue + Radio.Green +
Radio.Black,0) +Nz( Seat.Red + Seat.Blue+ Seat.Green + Seat.Black,0) As
TotalColors
FROM (Car LEFT JOIN Radio ON Car.CarID = Radio.CarID) LEFT JOIN Seat ON
Car.CarID = Seat.CarID)
WHERE TotalColors=3;

hth

 

[Francois]

Perhaps it is wrong. Perhaps the design isn't wrong. However is that there
is insufficient information given in the post for you to draw the conclusion
that the real system is right or wrong. Regardless, you can't go changing
the design everytime in the application lifecycle you find you need more out
of the system than was originally intended or perceived. Furthermore
refactoring the data model is likely to be very error prone and extend the
development time considerably. Doing a quick search of the newsgroup I think
that possibly pcdatasheet have staff that know a bit.. However I don't think
you know as much as you think you know and the type of comments you made for
my post were not that helpful.
Francois

 

[John Spencer (MVP)]

You might try a UNION ALL Query to get the data and save that as QNormal.
UNTESTED SQL statements follow.

SELECT CarID, Field3 + Field4 + Field5 + Field6 as ItemTotal
FROM Table2
UNION ALL
SELECT CarID, Field3 + Field4 + Field5 + Field6
FROM Table3

Then using that as a source.
SELECT CarID, Sum(ItemTotal) as SumTotal
FROM qNormal
GROUP BY CarID
HAVING SUM(ItemTotal)=3

In some cases (All fields with no special characters such as spaces) you can do
that all in one query
SELECT T.CarID, Sum(T.ItemTotal) as SumTotal
FROM [SELECT CarID, Field3 + Field4 + Field5 + Field6 as ItemTotal
FROM Table2
UNION ALL
SELECT CarID, Field3 + Field4 + Field5 + Field6
FROM Table3]. as T
GROUP BY T.CarID
HAVING SUM(T.ItemTotal)=3

Note the square brackets around the subordinate Select clause and the period
immediately after the last square bracket.

 

[Francois]

Thanks !

oops, so much for limited testing! <g>
try this instead

SELECT Count(CarID) AS CountOfCarID
FROM (SELECT Car.CarID, Nz(Radio.Red + Radio.Blue + Radio.Green +
Radio.Black,0) +Nz( Seat.Red + Seat.Blue+ Seat.Green + Seat.Black,0) As
TotalColors
FROM (Car LEFT JOIN Radio ON Car.CarID = Radio.CarID) LEFT JOIN Seat ON
Car.CarID = Seat.CarID)
WHERE TotalColors=3;

hth

 

[Francois]

Thanks !

You might try a UNION ALL Query to get the data and save that as QNormal.
UNTESTED SQL statements follow.

SELECT CarID, Field3 + Field4 + Field5 + Field6 as ItemTotal
FROM Table2
UNION ALL
SELECT CarID, Field3 + Field4 + Field5 + Field6
FROM Table3

Then using that as a source.
SELECT CarID, Sum(ItemTotal) as SumTotal
FROM qNormal
GROUP BY CarID
HAVING SUM(ItemTotal)=3

In some cases (All fields with no special characters such as spaces) you
can do
that all in one query
SELECT T.CarID, Sum(T.ItemTotal) as SumTotal
FROM [SELECT CarID, Field3 + Field4 + Field5 + Field6 as ItemTotal
FROM Table2
UNION ALL
SELECT CarID, Field3 + Field4 + Field5 + Field6
FROM Table3]. as T
GROUP BY T.CarID
HAVING SUM(T.ItemTotal)=3

Note the square brackets around the subordinate Select clause and the
period
immediately after the last square bracket.

Hi, I need some help with a Microsoft Access query that I have been
trying
to get working all day. Here is the scenario. The example below is a
contrived "car-parts" one. The real system is for a report in a
Haberdashery
order entry system.

In my simplified example, I have is three tables.

Table 1: Car
Field 1: CarID, number

Table 2: Radio
Field 1: CarID,number 'the Car ID in the Car table
Field 2: RadioName,text 'The Radio name
Field 3: Red,number 'either 1 or 0
Field 4: Blue,number 'either 1 or 0
Field 5: Green,number 'either 1 or 0
Field 6: Black,number 'either 1 or 0

Table 3: Seat
Field 1: CarID,number 'the Car ID in the Car table
Field 2: SeatName,text 'The Seat name
Field 3: Red,number 'either 1 or 0
Field 4: Blue,number 'either 1 or 0
Field 5: Green,number 'either 1 or 0
Field 6: Black,number 'either 1 or 0

In English, what I want is to perform a database query to count the all
the
car IDs that have the count of
Seat.Red+Seat.Blue+Seat.Green+Seat.Black+Radio.Red+Radio.Blue+Radio.Green+Radio.Black
= to a certain number, say 3 or 4 or 5 or 6 or whatever, I will change
this
in my VBA code as required.

I have been working on a query like this. This works!

SELECT COUNT(Car.CarID)
FROM Car,
Radio
WHERE Car.CarID=Radio.CarID
HAVING Radio.Red+Radio.Blue+Radio.Green+Radio.Black=3

But, it isn't really what I want. But I am close. I want it to count the
number of CAR IDs where

Radio.Red+Radio.Blue+Radio.Green+Radio.Black+Seat.Red+Seat.Blue+Seat.Green+Seat.Black=3

but the following won't work

SELECT COUNT(Car.CarID)
FROM Car,
Radio,
Seat
WHERE Car.CarID=Radio.CarID
AND Car.CarID=Seat.CarID
HAVING
Radio.Red+Radio.Blue+Radio.Green+Radio.Black+Seat.Red+Seat.Blue+Seat.Green+Seat.Black=3

because, although there is always a Car.CarID, there is sometimes only a
corresponding CarID in the Seats table, sometimes there is a only a
corresponding CarID in the Radio table, sometimes there is neither, and
sometimes there is both. The query above only works when there are both,
I
want it to work in all situations.

I know this has something to do with outer joins but I can't work out the
syntax and when I attempt to use the design tool in Access, is creates
this
big really overcomplicated messy query that I can't work out. I am using
Access 2000 and Access 97.

Thank you for your help.
Francois

 

[tina]

you're welcome!

Thanks !