SQL Query Problem

[shane8960]

Hi All,

I have a query:

SELECT Sum(tbl_ExtrasOrder.OrderExtrasValue) AS SumOfOrderExtrasValue, Sum(tbl_FramesOrder.OrderFramesValue) AS SumOfOrderFramesValue, Sum(tbl_GlassOrder.OrderGlassValue) AS SumOfOrderGlassValue, Sum(tbl_ServicesOrder.OrderServicesValue) AS SumOfOrderServicesValue, Sum(tbl_WindowsOrder.OrderWindowsValue) AS SumOfOrderWindowsValue, tbl_ExtrasOrder.OrderExtrasDate, tbl_FramesOrder.OrderFramesDate, tbl_GlassOrder.OrderGlassDate, tbl_ServicesOrder.OrderServicesDate, tbl_WindowsOrder.OrderWindowsDate, tbl_Clients.ClientID, tbl_Clients.ClientSurname, tbl_Clients.ClientDesigner
FROM ((((tbl_Clients LEFT JOIN tbl_ExtrasOrder ON tbl_Clients.ClientID = tbl_ExtrasOrder.ClientID) LEFT JOIN tbl_FramesOrder ON tbl_Clients.ClientID = tbl_FramesOrder.ClientID) LEFT JOIN tbl_GlassOrder ON tbl_Clients.ClientID = tbl_GlassOrder.ClientID) LEFT JOIN tbl_ServicesOrder ON tbl_Clients.ClientID = tbl_ServicesOrder.ClientID) LEFT JOIN tbl_WindowsOrder ON tbl_Clients.ClientID = tbl_WindowsOrder.ClientID
GROUP BY tbl_ExtrasOrder.OrderExtrasDate, tbl_FramesOrder.OrderFramesDate, tbl_GlassOrder.OrderGlassDate, tbl_ServicesOrder.OrderServicesDate, tbl_WindowsOrder.OrderWindowsDate, tbl_Clients.ClientID, tbl_Clients.ClientSurname, tbl_Clients.ClientDesigner
HAVING (((Sum(tbl_WindowsOrder.OrderWindowsValue)) Is Not Null) AND ((tbl_WindowsOrder.OrderWindowsDate) Between #" & dStartDate & "# And #" & dEndDate & "#)) OR (((Sum(tbl_ServicesOrder.OrderServicesValue)) Is Not Null) AND ((tbl_ServicesOrder.OrderServicesDate) Between #" & dStartDate & "# And #" & dEndDate & "#)) OR (((Sum(tbl_GlassOrder.OrderGlassValue)) Is Not Null) AND ((tbl_GlassOrder.OrderGlassDate) Between #" & dStartDate & "# And #" & dEndDate & "#)) OR (((Sum(tbl_FramesOrder.OrderFramesValue)) Is Not Null) AND ((tbl_FramesOrder.OrderFramesDate) Between #" & dStartDate & "# And #" & dEndDate & "#)) OR (((Sum(tbl_ExtrasOrder.OrderExtrasValue)) Is Not Null) AND ((tbl_ExtrasOrder.OrderExtrasDate) Between #" & dStartDate & "# And #" & dEndDate & "#))
ORDER BY tbl_Clients.ClientSurname;

Basically its a query that links 5 order tables to a clients table using ClientID. Each order table DOES contain more than 1 record for each client. This query gets the sum on all the records for the client for each category. However there is a further twist, I need to be able to specify what dates I want the results to appear for (dStartDate & dEndDate, set when the user pressed the search button using VBA).

However the problem I am having is that say if the client has ordered some windows on the 01/01/2008 and some glass on the 01/02/2008, when I search for orders in january it brings both the orders in and the same for february, however I only want it to bring in the orders marked with the dates which fall into the category.

I know its a big query, if anybody has any advice on how I can fix it to do what I want or an alternative way of doing it, it would be much appreciated.

Thanks, Shane

[FishVal]

Hi, Shane.

Are you sure the query will return right results at all ???
Table join is basically table multiplying.
So, if, let us say, Client1 has 3 records in a first orders table and 4 records in the second one, then joining these three tables will give 12 records and each order in the first orders table will appear 4 times and from the second one 3 times. After that they will be summed.

• It looks like you should UNION tables, not to JOIN
• Why do you have 5 tables for orders ???
• Did you design your db being aware of Database Normalization and Table structures ?

Kind regards,
Fish

[shane8960]

1) Yes the query does return the right results, if I dont put the SUM for each field it brings up multiple records as you stated. However the SUM is only the SUM of the actual records, rather than the duplicates that where shown.

2) Ill try UNION instead of join see if that fixes it.

3) Theres 5 order tables as there are 5 different types of item they can order (Window, Glass, Frame, Extras & Services), each type of order is completely different (ie the tables have 10-20 fields - all different for each order type so would mean having 80 or so fields in a table with about a 80% or so redundancy).

Thanks, Shane

[FishVal]

Ok.

How do you set values of dStartDate and dEndDate?

[shane8960]

Ok.

How do you set values of dStartDate and dEndDate?

Basically I have a form, with controlsource set to the above (without the extra date criteria), I then two text boxes where the user enters a date, they then press a command button which takes the dates from the text boxes, assigns them to the dStartDate and dEndDate and sets the query above to the forms source and then refreshes the form.

Also - I cant see how Union would work in this case. I need to be able to distinguish which table each of the values came from as I need to break it down into "Windows Value", "Glass Value" etc for each client.

[FishVal]

Basically I have a form, with controlsource set to the above (without the extra date criteria), I then two text boxes where the user enters a date, they then press a command button which takes the dates from the text boxes, assigns them to the dStartDate and dEndDate and sets the query above to the forms source and then refreshes the form.

Are you sure you get it in proper m/d/y format?

Also - I cant see how Union would work in this case. I need to be able to distinguish which table each of the values came from as I need to break it down into "Windows Value", "Glass Value" etc for each client.

Not a problem. You may design UNION subqueries to return constant identifying source table.

SELECT 'Windows Value' AS txtOrderType, .... FROM tbl_WindowsOrder
UNION
SELECT 'Glass Value' AS txtOrderType, .... FROM tbl_GlassOrder;
 

Regards,
Fish

P.S. I'm still confused as for what prevent JOIN to multiply your records. :)
P.P.S. Have you tried to move date criteria from HAVING clause to WHERE clause?

[shane8960]

Thanks Mate,

Used UNION and it works a treat.

Thanks again,

Shane

[FishVal]

You are welcome.

Best regards,
Fish