BIG newbie problem!!!! please help!!

[Schorsch]

Hi all,

ok, I have a table Review_Object_Main, in this table is a field called Project_ID, Project_Name. AND the field Review_Result with the two entries "released without changes" and "released after changes".

ok now in my FORM I want the following:

documents released without changes: number in %
documents released with changes: number in %

how can I realize that ??

I hope you can help me with this big problem!! And please, I'm a newbie, explain it so even I can understand it Big thanks!!!

regards,
Schorsch

 

[Schorsch]

plz I need help has nobody an idea ?

thanks,
Schorsch

 

[gurdiga]

This query gives you a record with 2 columns, that contains the results you need:

SELECT
(SELECT Count(*) WHERE Review_result='released without changes')/Count(*)*100 & '%' As [Documents released without changes],
(SELECT Count(*) WHERE Review_result='released with changes')/Count(*)*100 & '%' As [Documents released with changes]
FROM Review_Object_Main

good luck

 

[Schorsch]

cool, I'll try it!!

thanks

Schorsch

 

[Schorsch]

I don't get this working so where exactly do I have to paste this little piece of code ?

sorry I'm an access newbie.

regards,
Schorsch

 

[cogent1]

You go into a new query design, select SQL and then paste the staement there.

 

[Schorsch]

plz help me, I get a syntax error!! please take a look at the attachment.

thanks,
Schorsch

 

[cogent1]

I can't read this file, I'm afraid. But what are you trying to do exactly? You say you want this on a form. Why? Can't you just design a query and run that?

You can do what you want with a crosstab or totals query without the hassle of deciphering SQL syntax. Send me the table concerned and I'll see what I can do.

 

[cogent1]

...But now I have managed to open the file, I see there is an opening square bracket missing after one of the As statements

 

[Schorsch]

aahh what is an "opening square bracket " sorry I'm not very familiar with english language please can you post it how the syntax is right ?

thanks,
Schorsch

 

[Rich]

[ is the bracket, it goes here As [released after changes]

 

[Schorsch]

thanks

 

[Schorsch]

I added the bracket, but I still get a syntax error

please look at the attachment, where is the error now ??

thanks,
Schorsch

 

[cogent1]

You are giving yourself problems here. The field you are trying to count has a YES/NO scenario. In other words, you could replace it with a tick box called CHANGES? and check the box if there are any changes. Then it's simple to count the the number of each type in a query. But you can still use an expression to get what you want without using SQL anyway.

But I'm not sure what you want to do. Are the percentages the only thing you want to show on your form or are there other things as well?

If you just want to show these percentages as part of your form, you can use a domain count function in an expression. So. if you can tell me exactly what you want to happen, we can make progress.

 

[Schorsch]

Hi,

I just want to see the % in my form.

thanks for help,
Schorsch

 

[cogent1]

OK. Just base your form on a simple query from your table.

Have two calculated fields. In the first one add this expression:
=Dcount("[Review_Result]", "[Review_Object_Main]","[Review_Result]" &"=
'released without changes'"/Dcount("[Review_Result]", "Review_Object_Main")

Label the field appropriately

Format it as Percentage (in the properties)


Do the same, but have "released with changes" in the second expression.

 

[Schorsch]

where exactly do I have to put the code ?

=Dcount("[Review_Result]", "[Review_Object_Main]","[Review_Result]" &"= 
'released without changes'"/Dcount("[Review_Result]", "Review_Object_Main")

and what is a calculated field ?

thanks!!!

Schorsch

 

[cogent1]

1.Create a new form.

2 Add a text field from the toolbox. This is an UNBOUND FIELD i.e. it isn't stored in a table.

3. Right click in the field (not the label) and select Properties.

4. From the list, select Format and choose Percent from the options offered.

5. Now go to the Control Source property and right click at the right hand edge of that field.

6. Two buttons appear. One has ellipses (...). Right click that button and select Zoom.

7. Type the expression in the window. It becomes the Control source for the field. It is a calculated field because it is the result of an expression.

8. When you close the zoom window, the Control source will show what you typed in.

9. When you switch to form view the result of the calculation will be displayed in the field.

10 Shout "Whoopee!"

 

[Schorsch]

It doesn't work

I paste the code in the zoom window, please look at the attachment:

 

[Schorsch]

BUT when I'm pressing on OK, the following error message appears (look at the attachment).

please help !

thanks,
Schorsch