Unsure how to do this.....

[cubbyamber]

I am making a database for a somebody who sends some of his work to subcontractors. He needs to be able to pull a monthly report on the work that his subcontractors have done along with the amount of money owed to them and I don't know how to do this. I know I should make a query but how do I make it so it figures the amount owed for each individual subcontractor?

Thanks in advance

Tracy

 

[pbaldy]

A "totals" query, grouping by subcontractor and summing the money.

 

[cubbyamber]

For some reason my help in Access isn't working for the group by option I tried re-installing it but it still isn't working so I need some more input.

1. How do I do the group by option? Do I just right click right click on the sort option in the query and click totals for that field or is their another way?

2. Here is what I am trying to do. My client subcontracts some work out. I have a field called status codes

Here are the status codes:

FIND-Means they found what they were looking for
WOR-Working

There is also a field called service price. So here is what I need to do. I need to calculate how many FIND status codes there were multiplied by the service price sorted by contractor.

I think I need to use the IIF code but I don't know the required code to do this.

Any help would be greatly appreciated.

Tracy

 

[pbaldy]

1) There are several ways to expose the totals fields, and that's one of them.

2) I'm not really clear on what you're trying to achieve. Can you post a sample db along with an example of what the results should be?

 

[cubbyamber]

Okay, I got the IIF statment to work:

Here is the code I used:

Total: IIf([Status Code]="FIND",1*[Service Price])

Now I have two different issues:

1. How do I make it so that blank fields are not showing up As you can see in my Order Details query?

2. How do I make it so that the totals for the contractor only show up in one column instead of being split like they are on my sample database in my order details query? Is that the group by criteria?

Thanks

Tracy

 

[pbaldy]

I'm guessing a bit at what you want, as you didn't give an example of what the results should look like, but does this do it?

SELECT [Order Details].[Contractor ID], Sum([Order Details].[Service Price]) AS [SumOfService Price]
FROM [Order Details]
WHERE [Order Details].[Status code]="Find"
GROUP BY [Order Details].[Contractor ID];

 

[cubbyamber]

I have redone the database a little bit so that you can hopefully see what I want to do. If you look at the table test table, it tells you what I am looking for and how I want it to look.

I will give you a little bit more input as to what my client wants.

My client is a PI, he wants to keep track of the people his subcontractors have found hence the code "FIND".

His subcontractors charge a rate per "FIND" which is the service price in the table (such as $25.00 each). He wants to pull up a report monthly for each subcontractor showing how much he owes them (total) based on the "FIND" status code.

He wants to pull the report for each individual contractor


So for instance Contractor 1(C1) has found two people, he enters that status code "FIND" by each person he Finds and he charges $25.00 per find.

So the report or query should look something like this

Column 1: Contractor ID: C1
Column 2: Service Price: $25.00
Column 3: Total: $50.00

This needs to be done for each subcontractor.

I hope this and the database helps shed some light on what I need.

Tracy

 

[pbaldy]

How about

SELECT [Order Details].[Contractor ID], [Order Details].[Service Price], Sum([Order Details].[Service Price]) AS [ContractorTotal]
FROM [Order Details]
WHERE [Order Details].[Status code]="Find"
GROUP BY [Order Details].[Contractor ID], [Order Details].[Service Price];

 

[cubbyamber]

That code isn't working for me.

I open my query in design view and in the column named Total I right click on the criteria and click build then I copy and paste your code and it is coming up with the error

"The syntax in the subquery in this expression is incorrect. Check the subquery's syntax and enclose the subquery in parentheses."

Obviously I need parentheses in the expression but I don't know where. Can you please place the code up again with the parentheses?

Thanks

Tracy

 

[pbaldy]

That's the whole query, not a subquery. Start a new query, get into SQL view, and paste that in.

 

[cubbyamber]

THANK YOU THANK YOU THANK YOU!!!!!

I found one glitch though: If a contractor charges $25.00 for one find but $50.00 for another find, it doesn't group them together. Is there anyway that I can fix this?

That is what I wanted but can I ask just one more question. Is there a way that I can enter the contractor ID and it will pull up the report for that particular Contractor? Or do I have to run queries for each contractor.

Meaning: Can I pull up a report for contractor ID of C1 only and then pull a different report for Contractor ID of C2?

I'm not trying to make this difficult but I want to make it as user friendly as I possibly can as he knows less about access than I do. All he knows is how to make a table.

Thanks in advance

Tracy

 

[pbaldy]

First is easy, I just guessed at what you'd want. Change the Group By on that field to Min or Max and it will pick whichever, and you'll only get one line per contractor.

You can easily restrict it to a selected contractor. The simplest way is to put:

[Enter contractor]

in the criteria line under the contractor field. That will prompt you when the query runs and restrict the results to the entered contractor. Personally, I would create a form with a control to enter/choose a contractor, and have the query look there for the criteria.

 

[cubbyamber]

Paul,

They both worked like a charm. I did go with the form option you mentioned. I really want to thank you for dealing with all my newbie questions and helping me with this. Thanks to the different people on this forum including you I have learned a lot about Access and I thank you for that.

Thanks again,

Tracy

 

[cubbyamber]

Paul,

I sent you a PM please give me your thoughts on it.

thanks,

Tracy

 

[raskew]

Total: IIf([Status Code]="FIND",1*[Service Price])

That can't possibly work. With an Iif() statement, you require both a True and False condition. You've got a True condition, e.g. '1*[Service Price]', but no False condition. That's not going to fly.

Bob

 

[pbaldy]

Sorry, I've been out all day. Questions about Access should be addressed in the forum, not PM's. Part of the value of a site like this is the ability to search for answers to questions that have already been asked. If the exchange occurs in PM's, then this benefit is lost.

 

[cubbyamber]

Sorry, I sent the PM to you because I was giving you the option of helping or not because I didn't want to annoy you or anybody else with my questions.

I will address it publicly now then.

My client wants to pull a Success Rate Report per Contractor based on the status code that is placed in the status code field and it has to be by percentage.

So this is what I would like:

If C1(contractor ID) has a status code of FIND two times and C2 (contractor ID) has a status code of FIND two times then my query/report should say

Contractor ID-C1
Status Code-FIND
Success Rate-50%

Contractor ID-C2
Status Code-Find
Success Rate-50%

OR

If Contractor ID-C1 has two DEC status codes and Contractor ID-C2 has two DEC status Codes they this query/report would be as follows:

Contractor ID C1
Status Code DEC
Success Rate-50%

Contractor ID C2
Status Code DEC
Success Rate-50%

I need to use

DEC
DIS
FIND
RET
UEMP
UTL
WOR

as the different status codes. So how would I do this?

Tracy

 

[pbaldy]

Questions are why we're all here. If I or anyone else gets annoyed by them, we're free to not answer. Is the report "rptPaul" what you're looking for?

 

[cubbyamber]

Yes, exactly. Now how did you do it? And will it automatically update if you run the report?

Tracy

 

[pbaldy]

Studying what I did will help you learn how to do it next time. If you have questions about how anything works, just ask. As to whether it will update, try it. Put in more data and see if it continues to return data as you expect. That's quick and dirty, so you'll need to clean it up.