Is this possible

[PaulC]

Hello
Being new to access (1 week) can I output a calculation in a report textbox or should I have worked it out before hand somewhere else?
I have a text box with this formula (expression?) "=[EPD Records Query]![Cal Value]*[EPD Records Query]![Position 6]" in the control source box in properties but it returns #Error instead of 5160 (Cal Value=2580 and Position 6=2)
I'm using Access 2003 on an XP Operating system and used the expression builder to create the formula
Thanks

 

[kusumerumaal]

Hi,
Yes you can use formula expression in the report.
Try
=[EPD Records Query].[Cal Value]*[EPD Records Query].[Position 6]
Or
=([EPD Records Query].[Cal Value])*([EPD Records Query].[Position 6])
Or even this if the whole report is looking up from one query
=([Cal Value]*[Position 6])

 

[boblarson]

Hi,
Yes you can use formula expression in the report.
Try
=[EPD Records Query].[Cal Value]*[EPD Records Query].[Position 6]
Or
=([EPD Records Query].[Cal Value])*([EPD Records Query].[Position 6])
Or even this if the whole report is looking up from one query
=([Cal Value]*[Position 6])

You can't just assign a control source to a control and base it on tables or queries that aren't in the form's (or report's) recordsource. If you want to do that you will need to use a DLookup instead.

 

[PaulC]

Thank you both for your replies
Kusumerumaal. If I try your first 2 suggestions a message box pops up with the title 'Enter Parameter Value' with 'EPD Records Query' between the title and an empty text box when I switch from design view to normal (?) view. The third suggestion returns '#Error' in the textbox that I am changing
boblarson. The EPD Query is a duplicate of the EPD records table, i.e. when creating the query I copied all of the fields from 'Table: EPD Records' in the wizard. Should I be using the table rather than the query for the calculation parameters or did I miss the point?
Paul

 

[PaulC]

boblarson. Further to above I have changed my query fields. I deleted the Position '*' fields and created new calculated fields using the expression 'EPD6:[Cal Value]*[Position 6]'.
In the report I now use EPD6 as a control source instead of =[EPD Records Query].[Cal Value]*[EPD Records Query].[Position 6]. Is this the way to continue (I have another 69 calculated fields to add!)
Paul

 

[boblarson]

Sounds to me (given a field name like Position6) that your database is not normalized and therefore very difficult to get data out unlike you should be able to do.

Can you elaborate on your structure a bit?

Also, if you haven't read this, perhaps you should:
http://www.accessmvp.com/strive4peace/Access_Basics_Crystal_080113_Chapter_03.pdf

 

[PaulC]

I'll read the link tonight, thanks for supplying it.
To elaborate:
I am trying to create a database to store records in a single table that contain up to 77 variables. There are 5 variables that define the unique identity and 69 positions that are a record of defects counted. The 3 other variables define how the positions were counted. I enter data to the table via a userform and it just adds another row of data every time a record is entered.
The Query uses the data in the table and manipulates it (cal Value * Position etc ) and the report shows the data in a specific layout using data from the query and table (well that's the plan)
It would be easier to show you but I haven't figured out a way to upload to the forum without setting alarm bells ringing! and the file is large (3.8Mb). Is there a way to upload just the table?
To give you a taster, I have also been working on this in Excel the file (if allowed) is here: http://www.fileden.com/files/2009/3/12/2360379/EPD.xls.
I'll try to zip the file and add it later
Hope this isn't too confusing!
Paul
5 days into his access career

 

[boblarson]

So, yes you have a bit of a problem as you appear to be creating a spreadsheet application in a database. You really do have some learning to do because a relational database is not a spreadsheet, nor is a spreadsheet a relational database. They both have WAY DIFFERENT ways of working and you need to get to know those before going any further.

You need to think Thin and Tall when thinking of relational database tables where spreadsheets are a lot of time Short and Wide.

I highly suggest reading up on normalization as I said and then some redesign is highly in order. If you continue down the path you've set in motion it will only get harder and harder as you want to try to pull more data and also it will take more to get it back to a good design if you wait. I know that isn't what you wanted to hear, but we do pride ourselves here at AWF on not just solving the "immediate need" but also trying to teach and to help get things on the right path so it will make your job easier and the users will be happier in the long run.

 

[PaulC]

Always open to suggestion. Better to be led than stumble blindly on.
Thank you for your input, it is very much appreciated. As you suspect I'm used to Excel (the short and wide analogy is superb), so have much to learn
Any pointers on normalization reading material or reference books that will help a novice?
I promise to turn around and walk back up the path
Paul

 

[boblarson]

Any pointers on normalization reading material or reference books that will help a novice?

As mentioned in the other post:
http://www.accessmvp.com/strive4peace/Access_Basics_Crystal_080113_Chapter_03.pdf
You might also look at the other stuff there:
http://www.accessmvp.com/strive4peace/

And, another normalization link:
http://support.microsoft.com/default.aspx/kb/283878

Some "quick tutorials" that might help some and some samples (on my website):
http://www.btabdevelopment.com/main/

and some really good Access Tutorials, as well as others:
http://www.functionx.com

 

[PaulC]

Thanks very much. I'm off for some haoppy reading
boblarson you're a superstar
I thank you
Paul