Solved I have 2 questions regarding query limits and form additions

[donkey9972]

Hi, I have 2 questions and instead of taking up 2 forum slots I thought maybe a general question post might be best, but I wasn't sure.

So my first question is this. Lets say I have a table with 10 fields in it. Then when I make a query and I want all entries to be in the query, so I drag the Asterisk from the table down into the query, does that count as 1 for the query or 10?

Now my 2nd question is regarding how to add things in a form. I have an unbound control and 3 input fields in the detail section of my form. The unbound field will be where the total of the values will appear. I have been able to get it to add, but only when the form is closed and reopened using this on the unbound field.

=Sum(([Field1])+([Field2])+([Field3]))

Is there anyway to do this directly on the form without moving the unbound field to the footer portion?

 

[GaP42]

Count: count of what - 1 query, 10 fields irrespective of using * to get them in the query or individually

Unbound control: your sum is based on fields - which for the current record, if it is a new record the new field values are not committed to the table until saved. Also when edited, the changes are not committed till after saving. You could use simple After Update event code to calculate the sum. if you stay on the same record the total should display or move off the record and move back to it the sum will be updated.

EDIT: You may be better off by referencing the value of each control in which the fields are displayed:
field1 in txtField1 control etc. then the unbound control formula = txtField1 + txtFeld2 + txtField3
Updates immediately

 

[donkey9972]

Well for the first part I am referring to the 255 field limit placed on tables and queries.

 

[MajP]

The limit is 255 fields in the resulting query. So the star does nothing to get around it. It is the result that counts.

Access specifications - Microsoft Support

Find limits and specifications for Access 2016 database files and objects, such as the maximum file size or the maximum number of fields in a table.

support.microsoft.com

In the afterupdate of your controls you could add a recalc

Recalc Method [Access 2003 VBA Language Reference]

learn.microsoft.com

Then when you enter the first and second value it will immediately recalculate when leaving those control. The problem will still be with the third field. The afterupdate will not fire until leaving the control. If you really want to see immediate results you can put the recalc in all the controls on change.

 

[donkey9972]

Thank you, that helps and clarifies the things I needed.

 

[MajP]

Out of curiosity what kind of useable query has more than 255 fields? Not like you could look at it and read it and make sense. My first guess when people ask this about tables and queries with huge amount of fields is that the tables are not normalized correctly. Even in huge databases I rarely have tables with more than 20 fields.

 

[DHookom]

I question your table structure when you add values across fields. This suggests an un-normalized table.

 

[The_Doc_Man]

=Sum(([Field1])+([Field2])+([Field3]))

Got to ask: Did you really mean to take the sum of the sum? (Particularly on a form.)

The expression = A + B + C is a sum - of the three fields in that record that is currently on-screen, but of no records that are NOT on screen.

Using = SUM( expression ) is also a sum, of a type known as an SQL aggregate, in which you evaluate the expression for EVERY RECORD in your form's .RecordSource - IF that .RecordSource is actually set up to be an aggregate query... which is less commonly done for forms.

Using both of those may represent a bit of overkill.

 

[Pat Hartman]

I agree with the others. Maj posted links to the limits and Duane pointed out that you may have a bigger problem. I would also like to clarify your Sum((([Field1])+([Field2])+([Field3])) Function.

Access is NOT Excel where you can specify a range that goes across or down. Functions in Access operate on the domain so always down. The domain in this case is the bound RecordSource for the form. So, it sums the three fields but for the ENTIRE Recordset, not just the current record. The "correct" expression IS:

[Field1] + [Field2] + [Field3]

However, in arithmetic functions, nulls will propagate so if any of the three fields is null, the result will be null. To solve that problem, the expression needs to handle nulls. So, we have:

IsNull([Field1],0) + IsNull([Field2],0) + IsNull([Field3], 0)

So, as Duane has pointed out, this reeks of being a repeating group but that may be simply because you have not given us actual column names but just used something with a numeric suffix. If your actual column names contain suffixes or prefixes, the data almost certainly belongs in a separate table as one row per value rather than one column per value. The other hint that points to this issue is your concern regarding the number of columns in a query. I've created thousands of tables over the years for hundreds of applications and I can probably count on two hands the number of them that exceeded 50 columns. The average is normally much fewer. The tables that ended up with more than a small number of columns were mostly for data warehouses and so those tables were frequently the result of queries that joined several tables to make reporting easy for the users and reduce the number of joins they needed in their queries.

Oops, Looks like Doc beat me to it

 

[Mark_]

=Sum(([Field1])+([Field2])+([Field3]))

Are you trying to have a computed field for things like "Product Price + Product Tax + Shipping and Handling"? And are any of these looked up from another table where you save the ID instead of the value?

More important, are you also trying to save a computed value?