Why can't I use Expression Builder in tables?

[BillieH]

I feel like a fool.... I have finally got my head around using expression builder....
So I built an expression from a form. Now that works, but I cannot get it to work from tables, so it is automatic....

In my invoice table I have allowed for 10 items
Each has a quantity and price, therefore * the two to get the item amount.... but I did this in a form....
why can't i do it in the table in the first place?
At the end I want to add all ten item amounts....


works a treat in forms, but when I try to generate reports from this all the expressions disappear....
As I said I am a beginner, only been doing this for three yrs and NEVER with expressions....

Ideally I would like to build the expressions before I build forms or queries or reports....
I feel embarrassed that I am missing something fundamental in my understanding....
Please Help...
I dont' even know if I have expressed my issue clearly enough...

[Ruegen]

I feel like a fool.... I have finally got my head around using expression builder....
So I built an expression from a form. Now that works, but I cannot get it to work from tables, so it is automatic....

In my invoice table I have allowed for 10 items
Each has a quantity and price, therefore * the two to get the item amount.... but I did this in a form....
why can't i do it in the table in the first place?
At the end I want to add all ten item amounts....
works a treat in forms, but when I try to generate reports from this all the expressions disappear....
As I said I am a beginner, only been doing this for three yrs and NEVER with expressions....

Ideally I would like to build the expressions before I build forms or queries or reports....
I feel embarrassed that I am missing something fundamental in my understanding....
Please Help...
I dont' even know if I have expressed my issue clearly enough...

Tables generally are for holding/storing values. You can make a query that has an expression that either concatenates values, calculates values or copies values (and then you can put those into a table field in turn)

update
in a query you can make a field expression (expr1: or whatever name you like then price * qty )

I do my tables with standard values. Then I make a query with everything I want to see on a form (to as much as I can) then make that the record source for the form.

[Ruegen]

Also I would be doing invoices for records> so a table for invoices. This way any invoice that has a field value holding the same value as the customer ID only shows in the customers list of invoices

This way you can add many invoices to the customer and show it in a listbox.

[June7]

You have 10 fields for product items? This is not a normalized data structure and will probably cause you issues down the road.

I haven't used calculated fields in table so just did some testing. The expression builder opens when the calculated field is first created. It can later be invoked by clicking the ellipsis (...). The calculated fields are available to the report. All works for me.

[BillieH]

Thank you... that is about the only part I got right! Same with suppliers and expenses

[BillieH]

Okay, that makes a lot of sense.... wasn't such a silly problem afterall!

So should I remove the current expressions in the forms and re-start with queries.... do I do a query for each item or do all 10 and the total in one query?

I am in Clunes, Vic
Thank you Ruegen

[BillieH]

Dear Ruegen,
Do I do one expression per query or do I all expressions in one query

[Ruegen]

The idea is the raw values like say price, name, address, quantity are stored in the table. You wouldn't do calculations in a table because you don't want to store calculations in case those calculations happen to be wrong. You don't want to store a value if it is wrong (and that can happen form time to time).

When you make a query you are "selecting" something from the table, like a column. You can choose what you want to select. Say you select the name of the product, the quantity sold (which might be in another table and you like them together in the query) and you the price you sold them for. In the query builder you create a query, add a new field then right click in that blank field. It's like adding another column but it won't store any value in the table. Now with this new field you can "concatenate" (put things together into one long thing)

Concatenate can be something simple Total: price * quantity

or something complicated

or just some words (strings i.e. whatever is in " ")

say you want to concatenate the persons first name (which is a string) and surname (another string) into one thing - FullName: firstName & " " & Surname

I added a little & " " to give a space between the two

you have to add an & to add the next string

When you now go to look at the query after - you will see price, quantity and the total and the sums below it for every record/row or the name as one full name.

Now when you make a form you have totals and can display that on your form. You can also use it for reports if you use the same query.

Make sense?