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.
