summing hours in a query

L

Lori

I have a query based on phone numbers. My database distributes billing based
on the number of hours each month an individual bills to a certain project.
The database looks at their timesheets and uses a formula to calculate the
percentage of time billed and then distributes the billing accordingly. I've
created a report "disbursed verification" which totals the amount billed and
the number of hours to verify that the correct number of hours and the
correct dollar value is being billed. The report highlights anyone whose time
doesn't match the correct number of hours for a given month or whose values
don't equal the actual bill.

Now here's the problem. Some users have more than one phone number so
instead of giving me the total hours based on the phone number, its giving me
the total based on the user name. I've tried removing the name from the
query and it doesn't seem to make a difference. I've also tried creating an
expression instead of using the sum feature in a query and that doesn't help
either.

Any Help would be GREATLY appreciated.
 
L

Lori

Okay, here's the SQL:

SELECT Sum([qry-Disbursed Billing Charges]![HOURS]) AS [Hours Billed],
[qry-Disbursed Billing Charges].PTN, [qry-Disbursed Billing Charges].Current,
[qry-Disbursed Billing Charges].Equipment, [qry-Disbursed Billing
Charges].Total, Sum([qry-Disbursed Billing Charges]!CurrentBilled) AS Billed,
Sum([qry-Disbursed Billing Charges]!EquipBilled) AS Equip, [qry-Disbursed
Billing Charges].[Account#], [qry-Disbursed Billing Charges].[Unit Name]
FROM [qry-Disbursed Billing Charges]
GROUP BY [qry-Disbursed Billing Charges].PTN, [qry-Disbursed Billing
Charges].Current, [qry-Disbursed Billing Charges].Equipment, [qry-Disbursed
Billing Charges].Total, [qry-Disbursed Billing Charges].[Account#],
[qry-Disbursed Billing Charges].[Unit Name];

strange thing is I have the query in another database and it works
perfectly. I'm trying to separate out the phones from the rest of my data and
when I imported it over from the good database, it doesn't want to work right.
 
K

KARL DEWEY

I assume that the phone number is PTN, so try omiting the phone number --
SELECT Sum([qry-Disbursed Billing Charges]![HOURS]) AS [Hours Billed],
[qry-Disbursed Billing Charges].Current, [qry-Disbursed Billing
Charges].Equipment, [qry-Disbursed Billing Charges].Total, Sum([qry-Disbursed
Billing Charges]!CurrentBilled) AS Billed, Sum([qry-Disbursed Billing
Charges]!EquipBilled) AS Equip, [qry-Disbursed Billing Charges].[Account#],
[qry-Disbursed Billing Charges].[Unit Name]
FROM [qry-Disbursed Billing Charges]
GROUP BY [qry-Disbursed Billing Charges].[Unit Name], [qry-Disbursed Billing
Charges].Current, [qry-Disbursed Billing Charges].Equipment, [qry-Disbursed
Billing Charges].Total, [qry-Disbursed Billing Charges].[Account#];


Lori said:
Okay, here's the SQL:

SELECT Sum([qry-Disbursed Billing Charges]![HOURS]) AS [Hours Billed],
[qry-Disbursed Billing Charges].PTN, [qry-Disbursed Billing Charges].Current,
[qry-Disbursed Billing Charges].Equipment, [qry-Disbursed Billing
Charges].Total, Sum([qry-Disbursed Billing Charges]!CurrentBilled) AS Billed,
Sum([qry-Disbursed Billing Charges]!EquipBilled) AS Equip, [qry-Disbursed
Billing Charges].[Account#], [qry-Disbursed Billing Charges].[Unit Name]
FROM [qry-Disbursed Billing Charges]
GROUP BY [qry-Disbursed Billing Charges].PTN, [qry-Disbursed Billing
Charges].Current, [qry-Disbursed Billing Charges].Equipment, [qry-Disbursed
Billing Charges].Total, [qry-Disbursed Billing Charges].[Account#],
[qry-Disbursed Billing Charges].[Unit Name];

strange thing is I have the query in another database and it works
perfectly. I'm trying to separate out the phones from the rest of my data and
when I imported it over from the good database, it doesn't want to work right.

KARL DEWEY said:
You need to post your query.
Click to expand...
Click to expand...
 
L

Lori

This isn't working either. You are correct the PTN is the phone number, the
grouping needs to be by the phone # not the name. I've tried eliminating the
name from the query but it doesn't help. What is happening is that the query
is totaling by name and not by phone number.

I guess I need to clarify. Example, if Charlie works 168 hours in June and
he has 2 phone numbers, #212-555-1234 should show 168 hours and phone #
212-555-4321 should show 168 hours. What I'm getting is both numbers showing
336 hours, which would be the total for the name because its listed twice.

Any other ideas?

KARL DEWEY said:
I assume that the phone number is PTN, so try omiting the phone number --
SELECT Sum([qry-Disbursed Billing Charges]![HOURS]) AS [Hours Billed],
[qry-Disbursed Billing Charges].Current, [qry-Disbursed Billing
Charges].Equipment, [qry-Disbursed Billing Charges].Total, Sum([qry-Disbursed
Billing Charges]!CurrentBilled) AS Billed, Sum([qry-Disbursed Billing
Charges]!EquipBilled) AS Equip, [qry-Disbursed Billing Charges].[Account#],
[qry-Disbursed Billing Charges].[Unit Name]
FROM [qry-Disbursed Billing Charges]
GROUP BY [qry-Disbursed Billing Charges].[Unit Name], [qry-Disbursed Billing
Charges].Current, [qry-Disbursed Billing Charges].Equipment, [qry-Disbursed
Billing Charges].Total, [qry-Disbursed Billing Charges].[Account#];


Lori said:
Okay, here's the SQL:

SELECT Sum([qry-Disbursed Billing Charges]![HOURS]) AS [Hours Billed],
[qry-Disbursed Billing Charges].PTN, [qry-Disbursed Billing Charges].Current,
[qry-Disbursed Billing Charges].Equipment, [qry-Disbursed Billing
Charges].Total, Sum([qry-Disbursed Billing Charges]!CurrentBilled) AS Billed,
Sum([qry-Disbursed Billing Charges]!EquipBilled) AS Equip, [qry-Disbursed
Billing Charges].[Account#], [qry-Disbursed Billing Charges].[Unit Name]
FROM [qry-Disbursed Billing Charges]
GROUP BY [qry-Disbursed Billing Charges].PTN, [qry-Disbursed Billing
Charges].Current, [qry-Disbursed Billing Charges].Equipment, [qry-Disbursed
Billing Charges].Total, [qry-Disbursed Billing Charges].[Account#],
[qry-Disbursed Billing Charges].[Unit Name];

strange thing is I have the query in another database and it works
perfectly. I'm trying to separate out the phones from the rest of my data and
when I imported it over from the good database, it doesn't want to work right.

KARL DEWEY said:
You need to post your query.

:

I have a query based on phone numbers. My database distributes billing based
on the number of hours each month an individual bills to a certain project.
The database looks at their timesheets and uses a formula to calculate the
percentage of time billed and then distributes the billing accordingly. I've
created a report "disbursed verification" which totals the amount billed and
the number of hours to verify that the correct number of hours and the
correct dollar value is being billed. The report highlights anyone whose time
doesn't match the correct number of hours for a given month or whose values
don't equal the actual bill.

Now here's the problem. Some users have more than one phone number so
instead of giving me the total hours based on the phone number, its giving me
the total based on the user name. I've tried removing the name from the
query and it doesn't seem to make a difference. I've also tried creating an
expression instead of using the sum feature in a query and that doesn't help
either.

Any Help would be GREATLY appreciated.
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

If both phone number record 168 hours each then the total WIL BE 336.

It seems to me that the problem is not in the query but in the method of
data collection.

Lori said:
This isn't working either. You are correct the PTN is the phone number, the
grouping needs to be by the phone # not the name. I've tried eliminating the
name from the query but it doesn't help. What is happening is that the query
is totaling by name and not by phone number.

I guess I need to clarify. Example, if Charlie works 168 hours in June and
he has 2 phone numbers, #212-555-1234 should show 168 hours and phone #
212-555-4321 should show 168 hours. What I'm getting is both numbers showing
336 hours, which would be the total for the name because its listed twice.

Any other ideas?

KARL DEWEY said:
I assume that the phone number is PTN, so try omiting the phone number --
SELECT Sum([qry-Disbursed Billing Charges]![HOURS]) AS [Hours Billed],
[qry-Disbursed Billing Charges].Current, [qry-Disbursed Billing
Charges].Equipment, [qry-Disbursed Billing Charges].Total, Sum([qry-Disbursed
Billing Charges]!CurrentBilled) AS Billed, Sum([qry-Disbursed Billing
Charges]!EquipBilled) AS Equip, [qry-Disbursed Billing Charges].[Account#],
[qry-Disbursed Billing Charges].[Unit Name]
FROM [qry-Disbursed Billing Charges]
GROUP BY [qry-Disbursed Billing Charges].[Unit Name], [qry-Disbursed Billing
Charges].Current, [qry-Disbursed Billing Charges].Equipment, [qry-Disbursed
Billing Charges].Total, [qry-Disbursed Billing Charges].[Account#];


Lori said:
Okay, here's the SQL:

SELECT Sum([qry-Disbursed Billing Charges]![HOURS]) AS [Hours Billed],
[qry-Disbursed Billing Charges].PTN, [qry-Disbursed Billing Charges].Current,
[qry-Disbursed Billing Charges].Equipment, [qry-Disbursed Billing
Charges].Total, Sum([qry-Disbursed Billing Charges]!CurrentBilled) AS Billed,
Sum([qry-Disbursed Billing Charges]!EquipBilled) AS Equip, [qry-Disbursed
Billing Charges].[Account#], [qry-Disbursed Billing Charges].[Unit Name]
FROM [qry-Disbursed Billing Charges]
GROUP BY [qry-Disbursed Billing Charges].PTN, [qry-Disbursed Billing
Charges].Current, [qry-Disbursed Billing Charges].Equipment, [qry-Disbursed
Billing Charges].Total, [qry-Disbursed Billing Charges].[Account#],
[qry-Disbursed Billing Charges].[Unit Name];

strange thing is I have the query in another database and it works
perfectly. I'm trying to separate out the phones from the rest of my data and
when I imported it over from the good database, it doesn't want to work right.

:

You need to post your query.

:

I have a query based on phone numbers. My database distributes billing based
on the number of hours each month an individual bills to a certain project.
The database looks at their timesheets and uses a formula to calculate the
percentage of time billed and then distributes the billing accordingly. I've
created a report "disbursed verification" which totals the amount billed and
the number of hours to verify that the correct number of hours and the
correct dollar value is being billed. The report highlights anyone whose time
doesn't match the correct number of hours for a given month or whose values
don't equal the actual bill.

Now here's the problem. Some users have more than one phone number so
instead of giving me the total hours based on the phone number, its giving me
the total based on the user name. I've tried removing the name from the
query and it doesn't seem to make a difference. I've also tried creating an
expression instead of using the sum feature in a query and that doesn't help
either.

Any Help would be GREATLY appreciated.
Click to expand...
Click to expand...
Click to expand...
 
L

Lori

The total is suppose to be by Number not by Name, so if both phone numbers
record 168, then the total for each number should be 168.

As said in the first post, I set this up before in a earlier database and it
worked fine. the only difference between the two queries is that I named the
query in the new database qry-disbursed verification, while the old was just
called disbursed verification. The other difference is I put the filter for
the query on a tab in the menu as opposed to a separate filter form (could
this be the issue?)

here is the query that works (which is identical that's why I'm confused).
SELECT [Disbursed Billing Charges -Sprint].[Unit Name], [Disbursed Billing
Charges -Sprint].PTN, Sum([Disbursed Billing Charges -Sprint].CurrentBilled)
AS SumOfCurrentBilled, Sum([Disbursed Billing Charges -Sprint].EquipBilled)
AS SumOfEquipBilled, [Disbursed Billing Charges -Sprint].[Account#],
Sum([Disbursed Billing Charges -Sprint].[Hrs Billed]) AS [SumOfHrs Billed],
[Disbursed Billing Charges -Sprint].[Equipment and Retail Purchases],
[Disbursed Billing Charges -Sprint].[Total Charges], [Disbursed Billing
Charges -Sprint].Current
FROM [Disbursed Billing Charges -Sprint]
GROUP BY [Disbursed Billing Charges -Sprint].[Unit Name], [Disbursed Billing
Charges -Sprint].PTN, [Disbursed Billing Charges -Sprint].[Account#],
[Disbursed Billing Charges -Sprint].[Equipment and Retail Purchases],
[Disbursed Billing Charges -Sprint].[Total Charges], [Disbursed Billing
Charges -Sprint].Current
HAVING ((([Disbursed Billing Charges -Sprint].[Account#])=[Forms]![Billing
Filter]![Account #]));



KARL DEWEY said:
If both phone number record 168 hours each then the total WIL BE 336.

It seems to me that the problem is not in the query but in the method of
data collection.

Lori said:
This isn't working either. You are correct the PTN is the phone number, the
grouping needs to be by the phone # not the name. I've tried eliminating the
name from the query but it doesn't help. What is happening is that the query
is totaling by name and not by phone number.

I guess I need to clarify. Example, if Charlie works 168 hours in June and
he has 2 phone numbers, #212-555-1234 should show 168 hours and phone #
212-555-4321 should show 168 hours. What I'm getting is both numbers showing
336 hours, which would be the total for the name because its listed twice.

Any other ideas?

KARL DEWEY said:
I assume that the phone number is PTN, so try omiting the phone number --
SELECT Sum([qry-Disbursed Billing Charges]![HOURS]) AS [Hours Billed],
[qry-Disbursed Billing Charges].Current, [qry-Disbursed Billing
Charges].Equipment, [qry-Disbursed Billing Charges].Total, Sum([qry-Disbursed
Billing Charges]!CurrentBilled) AS Billed, Sum([qry-Disbursed Billing
Charges]!EquipBilled) AS Equip, [qry-Disbursed Billing Charges].[Account#],
[qry-Disbursed Billing Charges].[Unit Name]
FROM [qry-Disbursed Billing Charges]
GROUP BY [qry-Disbursed Billing Charges].[Unit Name], [qry-Disbursed Billing
Charges].Current, [qry-Disbursed Billing Charges].Equipment, [qry-Disbursed
Billing Charges].Total, [qry-Disbursed Billing Charges].[Account#];


:

Okay, here's the SQL:

SELECT Sum([qry-Disbursed Billing Charges]![HOURS]) AS [Hours Billed],
[qry-Disbursed Billing Charges].PTN, [qry-Disbursed Billing Charges].Current,
[qry-Disbursed Billing Charges].Equipment, [qry-Disbursed Billing
Charges].Total, Sum([qry-Disbursed Billing Charges]!CurrentBilled) AS Billed,
Sum([qry-Disbursed Billing Charges]!EquipBilled) AS Equip, [qry-Disbursed
Billing Charges].[Account#], [qry-Disbursed Billing Charges].[Unit Name]
FROM [qry-Disbursed Billing Charges]
GROUP BY [qry-Disbursed Billing Charges].PTN, [qry-Disbursed Billing
Charges].Current, [qry-Disbursed Billing Charges].Equipment, [qry-Disbursed
Billing Charges].Total, [qry-Disbursed Billing Charges].[Account#],
[qry-Disbursed Billing Charges].[Unit Name];

strange thing is I have the query in another database and it works
perfectly. I'm trying to separate out the phones from the rest of my data and
when I imported it over from the good database, it doesn't want to work right.

:

You need to post your query.

:

I have a query based on phone numbers. My database distributes billing based
on the number of hours each month an individual bills to a certain project.
The database looks at their timesheets and uses a formula to calculate the
percentage of time billed and then distributes the billing accordingly. I've
created a report "disbursed verification" which totals the amount billed and
the number of hours to verify that the correct number of hours and the
correct dollar value is being billed. The report highlights anyone whose time
doesn't match the correct number of hours for a given month or whose values
don't equal the actual bill.

Now here's the problem. Some users have more than one phone number so
instead of giving me the total hours based on the phone number, its giving me
the total based on the user name. I've tried removing the name from the
query and it doesn't seem to make a difference. I've also tried creating an
expression instead of using the sum feature in a query and that doesn't help
either.

Any Help would be GREATLY appreciated.
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Are you saying that each of the phone numbers record 168 hours but this query
show 336 for each phone?
Does your query [qry-Disbursed Billing Charges] do any summing? That could
be the problem.


Lori said:
The total is suppose to be by Number not by Name, so if both phone numbers
record 168, then the total for each number should be 168.

As said in the first post, I set this up before in a earlier database and it
worked fine. the only difference between the two queries is that I named the
query in the new database qry-disbursed verification, while the old was just
called disbursed verification. The other difference is I put the filter for
the query on a tab in the menu as opposed to a separate filter form (could
this be the issue?)

here is the query that works (which is identical that's why I'm confused).
SELECT [Disbursed Billing Charges -Sprint].[Unit Name], [Disbursed Billing
Charges -Sprint].PTN, Sum([Disbursed Billing Charges -Sprint].CurrentBilled)
AS SumOfCurrentBilled, Sum([Disbursed Billing Charges -Sprint].EquipBilled)
AS SumOfEquipBilled, [Disbursed Billing Charges -Sprint].[Account#],
Sum([Disbursed Billing Charges -Sprint].[Hrs Billed]) AS [SumOfHrs Billed],
[Disbursed Billing Charges -Sprint].[Equipment and Retail Purchases],
[Disbursed Billing Charges -Sprint].[Total Charges], [Disbursed Billing
Charges -Sprint].Current
FROM [Disbursed Billing Charges -Sprint]
GROUP BY [Disbursed Billing Charges -Sprint].[Unit Name], [Disbursed Billing
Charges -Sprint].PTN, [Disbursed Billing Charges -Sprint].[Account#],
[Disbursed Billing Charges -Sprint].[Equipment and Retail Purchases],
[Disbursed Billing Charges -Sprint].[Total Charges], [Disbursed Billing
Charges -Sprint].Current
HAVING ((([Disbursed Billing Charges -Sprint].[Account#])=[Forms]![Billing
Filter]![Account #]));



KARL DEWEY said:
If both phone number record 168 hours each then the total WIL BE 336.

It seems to me that the problem is not in the query but in the method of
data collection.

Lori said:
This isn't working either. You are correct the PTN is the phone number, the
grouping needs to be by the phone # not the name. I've tried eliminating the
name from the query but it doesn't help. What is happening is that the query
is totaling by name and not by phone number.

I guess I need to clarify. Example, if Charlie works 168 hours in June and
he has 2 phone numbers, #212-555-1234 should show 168 hours and phone #
212-555-4321 should show 168 hours. What I'm getting is both numbers showing
336 hours, which would be the total for the name because its listed twice.

Any other ideas?

:

I assume that the phone number is PTN, so try omiting the phone number --
SELECT Sum([qry-Disbursed Billing Charges]![HOURS]) AS [Hours Billed],
[qry-Disbursed Billing Charges].Current, [qry-Disbursed Billing
Charges].Equipment, [qry-Disbursed Billing Charges].Total, Sum([qry-Disbursed
Billing Charges]!CurrentBilled) AS Billed, Sum([qry-Disbursed Billing
Charges]!EquipBilled) AS Equip, [qry-Disbursed Billing Charges].[Account#],
[qry-Disbursed Billing Charges].[Unit Name]
FROM [qry-Disbursed Billing Charges]
GROUP BY [qry-Disbursed Billing Charges].[Unit Name], [qry-Disbursed Billing
Charges].Current, [qry-Disbursed Billing Charges].Equipment, [qry-Disbursed
Billing Charges].Total, [qry-Disbursed Billing Charges].[Account#];


:

Okay, here's the SQL:

SELECT Sum([qry-Disbursed Billing Charges]![HOURS]) AS [Hours Billed],
[qry-Disbursed Billing Charges].PTN, [qry-Disbursed Billing Charges].Current,
[qry-Disbursed Billing Charges].Equipment, [qry-Disbursed Billing
Charges].Total, Sum([qry-Disbursed Billing Charges]!CurrentBilled) AS Billed,
Sum([qry-Disbursed Billing Charges]!EquipBilled) AS Equip, [qry-Disbursed
Billing Charges].[Account#], [qry-Disbursed Billing Charges].[Unit Name]
FROM [qry-Disbursed Billing Charges]
GROUP BY [qry-Disbursed Billing Charges].PTN, [qry-Disbursed Billing
Charges].Current, [qry-Disbursed Billing Charges].Equipment, [qry-Disbursed
Billing Charges].Total, [qry-Disbursed Billing Charges].[Account#],
[qry-Disbursed Billing Charges].[Unit Name];

strange thing is I have the query in another database and it works
perfectly. I'm trying to separate out the phones from the rest of my data and
when I imported it over from the good database, it doesn't want to work right.

:

You need to post your query.

:

I have a query based on phone numbers. My database distributes billing based
on the number of hours each month an individual bills to a certain project.
The database looks at their timesheets and uses a formula to calculate the
percentage of time billed and then distributes the billing accordingly. I've
created a report "disbursed verification" which totals the amount billed and
the number of hours to verify that the correct number of hours and the
correct dollar value is being billed. The report highlights anyone whose time
doesn't match the correct number of hours for a given month or whose values
don't equal the actual bill.

Now here's the problem. Some users have more than one phone number so
instead of giving me the total hours based on the phone number, its giving me
the total based on the user name. I've tried removing the name from the
query and it doesn't seem to make a difference. I've also tried creating an
expression instead of using the sum feature in a query and that doesn't help
either.

Any Help would be GREATLY appreciated.
Click to expand...
Click to expand...
Click to expand...
 
L

Lori

I need the query to sum. The data provides information based on the cell
phone bill and the employee timesheets. It distributes the cost based on
where the user bills their time. In order to verify that the right value is
being billed I created this query as a check to make sure that the
distributed hours and the distributed values equal the correct number of
hours for the month and the correct dollar value based on the bill.

KARL DEWEY said:
Are you saying that each of the phone numbers record 168 hours but this query
show 336 for each phone?
Does your query [qry-Disbursed Billing Charges] do any summing? That could
be the problem.


Lori said:
The total is suppose to be by Number not by Name, so if both phone numbers
record 168, then the total for each number should be 168.

As said in the first post, I set this up before in a earlier database and it
worked fine. the only difference between the two queries is that I named the
query in the new database qry-disbursed verification, while the old was just
called disbursed verification. The other difference is I put the filter for
the query on a tab in the menu as opposed to a separate filter form (could
this be the issue?)

here is the query that works (which is identical that's why I'm confused).
SELECT [Disbursed Billing Charges -Sprint].[Unit Name], [Disbursed Billing
Charges -Sprint].PTN, Sum([Disbursed Billing Charges -Sprint].CurrentBilled)
AS SumOfCurrentBilled, Sum([Disbursed Billing Charges -Sprint].EquipBilled)
AS SumOfEquipBilled, [Disbursed Billing Charges -Sprint].[Account#],
Sum([Disbursed Billing Charges -Sprint].[Hrs Billed]) AS [SumOfHrs Billed],
[Disbursed Billing Charges -Sprint].[Equipment and Retail Purchases],
[Disbursed Billing Charges -Sprint].[Total Charges], [Disbursed Billing
Charges -Sprint].Current
FROM [Disbursed Billing Charges -Sprint]
GROUP BY [Disbursed Billing Charges -Sprint].[Unit Name], [Disbursed Billing
Charges -Sprint].PTN, [Disbursed Billing Charges -Sprint].[Account#],
[Disbursed Billing Charges -Sprint].[Equipment and Retail Purchases],
[Disbursed Billing Charges -Sprint].[Total Charges], [Disbursed Billing
Charges -Sprint].Current
HAVING ((([Disbursed Billing Charges -Sprint].[Account#])=[Forms]![Billing
Filter]![Account #]));



KARL DEWEY said:
If both phone number record 168 hours each then the total WIL BE 336.

It seems to me that the problem is not in the query but in the method of
data collection.

:

This isn't working either. You are correct the PTN is the phone number, the
grouping needs to be by the phone # not the name. I've tried eliminating the
name from the query but it doesn't help. What is happening is that the query
is totaling by name and not by phone number.

I guess I need to clarify. Example, if Charlie works 168 hours in June and
he has 2 phone numbers, #212-555-1234 should show 168 hours and phone #
212-555-4321 should show 168 hours. What I'm getting is both numbers showing
336 hours, which would be the total for the name because its listed twice.

Any other ideas?

:

I assume that the phone number is PTN, so try omiting the phone number --
SELECT Sum([qry-Disbursed Billing Charges]![HOURS]) AS [Hours Billed],
[qry-Disbursed Billing Charges].Current, [qry-Disbursed Billing
Charges].Equipment, [qry-Disbursed Billing Charges].Total, Sum([qry-Disbursed
Billing Charges]!CurrentBilled) AS Billed, Sum([qry-Disbursed Billing
Charges]!EquipBilled) AS Equip, [qry-Disbursed Billing Charges].[Account#],
[qry-Disbursed Billing Charges].[Unit Name]
FROM [qry-Disbursed Billing Charges]
GROUP BY [qry-Disbursed Billing Charges].[Unit Name], [qry-Disbursed Billing
Charges].Current, [qry-Disbursed Billing Charges].Equipment, [qry-Disbursed
Billing Charges].Total, [qry-Disbursed Billing Charges].[Account#];


:

Okay, here's the SQL:

SELECT Sum([qry-Disbursed Billing Charges]![HOURS]) AS [Hours Billed],
[qry-Disbursed Billing Charges].PTN, [qry-Disbursed Billing Charges].Current,
[qry-Disbursed Billing Charges].Equipment, [qry-Disbursed Billing
Charges].Total, Sum([qry-Disbursed Billing Charges]!CurrentBilled) AS Billed,
Sum([qry-Disbursed Billing Charges]!EquipBilled) AS Equip, [qry-Disbursed
Billing Charges].[Account#], [qry-Disbursed Billing Charges].[Unit Name]
FROM [qry-Disbursed Billing Charges]
GROUP BY [qry-Disbursed Billing Charges].PTN, [qry-Disbursed Billing
Charges].Current, [qry-Disbursed Billing Charges].Equipment, [qry-Disbursed
Billing Charges].Total, [qry-Disbursed Billing Charges].[Account#],
[qry-Disbursed Billing Charges].[Unit Name];

strange thing is I have the query in another database and it works
perfectly. I'm trying to separate out the phones from the rest of my data and
when I imported it over from the good database, it doesn't want to work right.

:

You need to post your query.

:

I have a query based on phone numbers. My database distributes billing based
on the number of hours each month an individual bills to a certain project.
The database looks at their timesheets and uses a formula to calculate the
percentage of time billed and then distributes the billing accordingly. I've
created a report "disbursed verification" which totals the amount billed and
the number of hours to verify that the correct number of hours and the
correct dollar value is being billed. The report highlights anyone whose time
doesn't match the correct number of hours for a given month or whose values
don't equal the actual bill.

Now here's the problem. Some users have more than one phone number so
instead of giving me the total hours based on the phone number, its giving me
the total based on the user name. I've tried removing the name from the
query and it doesn't seem to make a difference. I've also tried creating an
expression instead of using the sum feature in a query and that doesn't help
either.

Any Help would be GREATLY appreciated.
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

It appears the earlier query is summing by name and then this one summing by
phone number so the values are doubled.

Base this query on raw data rather than the other query.

Lori said:
I need the query to sum. The data provides information based on the cell
phone bill and the employee timesheets. It distributes the cost based on
where the user bills their time. In order to verify that the right value is
being billed I created this query as a check to make sure that the
distributed hours and the distributed values equal the correct number of
hours for the month and the correct dollar value based on the bill.

KARL DEWEY said:
Are you saying that each of the phone numbers record 168 hours but this query
show 336 for each phone?
Does your query [qry-Disbursed Billing Charges] do any summing? That could
be the problem.


Lori said:
The total is suppose to be by Number not by Name, so if both phone numbers
record 168, then the total for each number should be 168.

As said in the first post, I set this up before in a earlier database and it
worked fine. the only difference between the two queries is that I named the
query in the new database qry-disbursed verification, while the old was just
called disbursed verification. The other difference is I put the filter for
the query on a tab in the menu as opposed to a separate filter form (could
this be the issue?)

here is the query that works (which is identical that's why I'm confused).
SELECT [Disbursed Billing Charges -Sprint].[Unit Name], [Disbursed Billing
Charges -Sprint].PTN, Sum([Disbursed Billing Charges -Sprint].CurrentBilled)
AS SumOfCurrentBilled, Sum([Disbursed Billing Charges -Sprint].EquipBilled)
AS SumOfEquipBilled, [Disbursed Billing Charges -Sprint].[Account#],
Sum([Disbursed Billing Charges -Sprint].[Hrs Billed]) AS [SumOfHrs Billed],
[Disbursed Billing Charges -Sprint].[Equipment and Retail Purchases],
[Disbursed Billing Charges -Sprint].[Total Charges], [Disbursed Billing
Charges -Sprint].Current
FROM [Disbursed Billing Charges -Sprint]
GROUP BY [Disbursed Billing Charges -Sprint].[Unit Name], [Disbursed Billing
Charges -Sprint].PTN, [Disbursed Billing Charges -Sprint].[Account#],
[Disbursed Billing Charges -Sprint].[Equipment and Retail Purchases],
[Disbursed Billing Charges -Sprint].[Total Charges], [Disbursed Billing
Charges -Sprint].Current
HAVING ((([Disbursed Billing Charges -Sprint].[Account#])=[Forms]![Billing
Filter]![Account #]));



:

If both phone number record 168 hours each then the total WIL BE 336.

It seems to me that the problem is not in the query but in the method of
data collection.

:

This isn't working either. You are correct the PTN is the phone number, the
grouping needs to be by the phone # not the name. I've tried eliminating the
name from the query but it doesn't help. What is happening is that the query
is totaling by name and not by phone number.

I guess I need to clarify. Example, if Charlie works 168 hours in June and
he has 2 phone numbers, #212-555-1234 should show 168 hours and phone #
212-555-4321 should show 168 hours. What I'm getting is both numbers showing
336 hours, which would be the total for the name because its listed twice.

Any other ideas?

:

I assume that the phone number is PTN, so try omiting the phone number --
SELECT Sum([qry-Disbursed Billing Charges]![HOURS]) AS [Hours Billed],
[qry-Disbursed Billing Charges].Current, [qry-Disbursed Billing
Charges].Equipment, [qry-Disbursed Billing Charges].Total, Sum([qry-Disbursed
Billing Charges]!CurrentBilled) AS Billed, Sum([qry-Disbursed Billing
Charges]!EquipBilled) AS Equip, [qry-Disbursed Billing Charges].[Account#],
[qry-Disbursed Billing Charges].[Unit Name]
FROM [qry-Disbursed Billing Charges]
GROUP BY [qry-Disbursed Billing Charges].[Unit Name], [qry-Disbursed Billing
Charges].Current, [qry-Disbursed Billing Charges].Equipment, [qry-Disbursed
Billing Charges].Total, [qry-Disbursed Billing Charges].[Account#];


:

Okay, here's the SQL:

SELECT Sum([qry-Disbursed Billing Charges]![HOURS]) AS [Hours Billed],
[qry-Disbursed Billing Charges].PTN, [qry-Disbursed Billing Charges].Current,
[qry-Disbursed Billing Charges].Equipment, [qry-Disbursed Billing
Charges].Total, Sum([qry-Disbursed Billing Charges]!CurrentBilled) AS Billed,
Sum([qry-Disbursed Billing Charges]!EquipBilled) AS Equip, [qry-Disbursed
Billing Charges].[Account#], [qry-Disbursed Billing Charges].[Unit Name]
FROM [qry-Disbursed Billing Charges]
GROUP BY [qry-Disbursed Billing Charges].PTN, [qry-Disbursed Billing
Charges].Current, [qry-Disbursed Billing Charges].Equipment, [qry-Disbursed
Billing Charges].Total, [qry-Disbursed Billing Charges].[Account#],
[qry-Disbursed Billing Charges].[Unit Name];

strange thing is I have the query in another database and it works
perfectly. I'm trying to separate out the phones from the rest of my data and
when I imported it over from the good database, it doesn't want to work right.

:

You need to post your query.

:

I have a query based on phone numbers. My database distributes billing based
on the number of hours each month an individual bills to a certain project.
The database looks at their timesheets and uses a formula to calculate the
percentage of time billed and then distributes the billing accordingly. I've
created a report "disbursed verification" which totals the amount billed and
the number of hours to verify that the correct number of hours and the
correct dollar value is being billed. The report highlights anyone whose time
doesn't match the correct number of hours for a given month or whose values
don't equal the actual bill.

Now here's the problem. Some users have more than one phone number so
instead of giving me the total hours based on the phone number, its giving me
the total based on the user name. I've tried removing the name from the
query and it doesn't seem to make a difference. I've also tried creating an
expression instead of using the sum feature in a query and that doesn't help
either.

Any Help would be GREATLY appreciated.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update Query - ANSWER NEEDED, HELP 4
aggregate sql 1
Access Query for keeping a total hours remaining. 0
extract part of a value 1
two queries from same data in table into one query 2
Comparison Query 4
Query quandary 5
Total Query: Adding a field to a calculation. 3

Top