Access multiples of four 2

[Cillies]

Hi!
I am using Access 2000, and I am trying to do a query that is totalling an attribute. I.E. I have an entry that has several numerical entries spread over a range of dates, I want to be fit to just retain the total of these figures between selected dates. The problem is that in the query builder when I try to sum this entity I get a returned result of the correct figure only its Multiplied by 4. my result should be 485 but is returned 1940.

Below is the three entries which I want to total in my query,


Date CallsAmount
05/12/03 246
05/12/03 139
05/12/03 100

Below I have a copy of the SQL

SELECT Clients.CompanyName, Projects.ProjectName, Projects.ProjectStartup, Projects.ProjectLeads, Projects.ProjectCall, Projects.ProjectTotalBillingEstimate, [Time Card Expenses].CallsAmount

FROM (Clients INNER JOIN (Projects INNER JOIN [Time Card Expenses] ON Projects.ProjectID = [Time Card Expenses].ProjectID) ON Clients.ClientID = Projects.ClientID) INNER JOIN [Time Card Hours] ON Projects.ProjectID = [Time Card Hours].ProjectID

GROUP BY Clients.CompanyName, Projects.ProjectName, Projects.ProjectStartup, Projects.ProjectLeads, Projects.ProjectCall, Projects.ProjectTotalBillingEstimate, [Time Card Expenses].CallsAmount, [Time Card Hours].DateWorked

HAVING (((Clients.CompanyName)=[Enter Clients Name]) AND (([Time Card Hours].DateWorked) Between [Enter Start Date] And [Enter Ending Date]));

If you need anymore information just drop me a line

 

[SteveR77]

Cillies,

Is this the output that you have in your group by query prior to setting the Calls Amount field to be summed?

Date CallsAmount
05/12/03 246
05/12/03 139
05/12/03 100

I am just wondering if there is something with the joins that is causing you to have more data returned that you expected.


Steve

 

[jrbarnett]

Cillies

The multiplication of rows normally comes up when tables haven't been joined properly together. This is because SQL works in a set oriented nature (think back to school maths lessons for what I mean), ie you can never guarantee a particular number of results in a query.

Other than that, the "HAVING" section can be moved to between the end of "FROM" and before "GROUP BY" - and the word "HAVING" can be replaced with "Where". This will speed up the query slightly because it means the joins can be done on the data in the tables rather than after it has been retrieved.

John

 

[Cillies]

Cheers!
I got it!
I was just about to type back that I figured it out.
It was a problem with the relationships, they weren't joined correctly.

Thanks very much for your help

Kindest regards
Cillies

 

[SteveR77]

Outstanding Cilies.


Cheers,

Steve

 

[SemperFiDownUnda]

Some rules about the different "Clauses" of a SQL statement

SELECT - Fields you want displayed
FROM - tables you want to use and how they are joined
INNER JOIN - record added if there is a matching record(s) in the joined table based on the ON clause.

OUTER JOIN (LEFT & RIGHT) - record is added to the result set even if a matching record(s) is/are not found in joined table based on the ON clause
(FULL) Similar to the (LEFT & RIGHT) joins this join will add all records to the result set from both tables matching where it can based on the ON clause.
When a match is not found in the other table for a given record that columns of the other table will be NULL


INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN may have different syntax depending on the db and version you are using.
I will not go into cross joins for this discussion

WHERE clause - When a record passes all join conditions the WHERE clause is evaluated to see if it gets placed in the result set.

GROUP BY clause - used when using aggrigate functions like SUM() MAX() MIN() AVG() etc. Multiple records are rolled up into 1 based on all the non aggrigate column. Ie all the rows that have non aggragiate columns with the same value get combined into 1 row.
This is done after the final result set is produced. Think it of a 2nd pass.

HAVING clause - used for filtering on aggrigate columns. ie

SELECT InvoiceNum, SUM(LIAmount)
  FROM InvDetail 
 GROUP BY InvoiceNum
 HAVING SUM(LIAmount) > 500

would give you all invoices that have a total over 500. Like the GROUP BY. It is done after the initial result set has been made. This is a 3rd pass over the datat. Never put a comparision in the HAVING clause that doesn't involve a aggrigate function. They perform better when in the WHERE clause. Aggrigate functions can't be used in the WHERE clause because at the time the WHERE clause is examined that rows final answer for the aggrigate is not known

ORDER BY clause - This clause dictates the final sort order of the result set. If possible RDMS will extract the data in the order it wants if not it will do a final pass over the data to order it.




Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[r937]

I will not go into cross joins for this discussion

should've




rudy

http://r937.com/

SQL Consulting

 

[SemperFiDownUnda]

CROSS JOIN - This is a stupid join that i've only used to fill up tables with masses of data to test performance.

A cross join joins every record in the left table to every record on the right table.

It has no ON clause as you don't need a join condition if you are joining EVERY record.

If you have 10,000 records in table A and 5,000 in table b and cross join them you'll end up with a result set of 50,000,000 records. Ie the
Take a record 1 from Table A join it with record 1 in table b
Take a record 1 from Table A join it with record 2 in table b
Take a record 1 from Table A join it with record 3 in table b
.
.
.
Take a record 1 from Table A join it with record 5,000 in table b
Take a record 2 from Table A join it with record 1 in table b
.
.
.
Take a record 2 from Table A join it with record 5,000 in table b
.
.
.
Take a record 1 from Table A join it with record 1 in table b
Take a record 1 from Table A join it with record 2 in table b
Take a record 1 from Table A join it with record 3 in table b
.
.
.
Take a record 10,000 from Table A join it with record 5,000 in table b

You get the picture.



Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[Cillies]

Thanks Very much for all that information Wayne and rudy, its greatly appreciated, you have been very helpful.

Kindest Regards

Cillies

 

[r937]

oh wayne, there's nothing "stupid" about a cross join

perhaps you have just not yet encountered a situation where a cross join is an appropriate solution



rudy

http://r937.com/

SQL Consulting

 

[SemperFiDownUnda]

r937 - I have (filling up a table with dummy data for performance testing). Hmmm I thought I asked the question of has anyone found a better use for a cross join... Has anyone found a good use for a cross join besides the one I've used it for?

Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[r937]

besides generating test data? here are three examples:

thread183-755853 -- triple cross-join of the integers 0 through 9 to create the numbers which are used to generate a range of dates

thread436-755873 -- cross join a single row of one table to unrelated rows of another

thread701-728879 -- cross join two tables to get all possible combinations, then outer join to find the ones that are missing

rudy

http://r937.com/

SQL Consulting

 

[SemperFiDownUnda]

Thanks

Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first