adding fields in query

all, using access 2003. have table contains number fields. I am using a query in a subform. I want to add the number fields to place them on a field called total charges. please help. this is what I am placing in the query

TOTALCHARGES: DSum([NUM_TRANSCHG],[NUM_FUELSURCHG],[NUM_ACCESCHG],[NUM_OTHERCHG])

its returning a error about wrong # of arguments

Sponsored Links:

Related Topics

Format fields in Make Table Query
Field lenth, etc inside a make table query. I have a query that creates a table based on customer data, but at the same time, I am adding new fields that do not exist on any table and I want to define the data type, length etc.

inside the make table query so that the query creates the table as I need it. The table that is made is used in queries later down the road.

For all fields that I am adding, I am setting the value of the field to Null.


Adding 2 Fields together..returns error if no value
This is Driving me crazy, I am trying to add the value of 2 Fields together in a seperate field on a form, everything works fine and I get a total no problem.until one of the two fields I am adding has no value in it, then I get #Error!
Sometimes there will be no value in one or both of the fields I cant avoid that.


Queries : All about the Query
This tutorial covers: Types of queries, Creating a query, The Design view, Adding fields, Defining calculated fields, Forming expressions, Column headings, Changing the order of the fields, Saving the query, Running the query, Modifying the query design, Ordering rows, Selecting rows, Conditions ,Multitable queries, Joining tables and The external join


Adding more than 16 fields to a query
This is a pretty simple question: how can you add more than 16 fields to a query? I know the maximum allowed is over 50 but in design view there are only 16 standard but I have to add more.


Counting specific fields within a table/query
I have 32 fields and each has the ability for 3 different types of input (S,H,EL)
I want to count each field based on the table for the type, so if field1=S and field 2=S then TotalS would be 2 just for that table.

I need to do this to all 31 fields with all 3 selections for each.

I can do this with IF logic using a counter for adding but I really want to do this the proper way


The Structured Query Language
So far, to create a query we have learned to use either the Query Wizard or the Design View of a query. In both cases, we were selecting fields and adding them to the query. This concept of building a query is based on the Structured Query Language abbreviated SQL. In fact, all queries are based on SQL and this language is used by most database development environments, including Microsoft Access.


using table as a look up field and adding to existing table
I want to use a 2nd table as a lookup field on my main table(permits) and have the chosen fields added to the permit table fields using a form. If I change the field to a lookup using the2nd table (contractors) how do I pull the additional fields into the form/1st table?

I also tried creating a new form w/fields from both tables after adding a relationship but the form will not allow me to add any new records. .


The Structured Query Language (SQL)
So far, to create a query we have learned to use either the Query Wizard or the Design View of a query. In both cases, we were selecting fields and adding them to the query. This concept of building a query is based on the Structured Query Language abbreviated SQL. In fact, all queries are based on SQL and this language is used by most database development environments, including Microsoft Access.


Microsoft can't append all the records
I have two tables, table 1 has 19 fields. Table 2 has 18 fields. Table 2 has one field table 1 does not have. Table 1 has two fields table 2 does not have. I changed field names so that they both coincide and all fields of text type have a Yes in AllowZeroLength and a No in Required. I tried to run an append query adding only the fields that both tables have in common, to transfer the data from table 2 to table 1. I then get the usual message: can't append all the records in the append query. Microsoft Access set 0 fields to Null blah blah. Of the 109 records I wanted to append it only appended 17 and I can't understand what is different about these records or all the others!


Best practice - Adding vs Editing
Is the best way:

* create one form and then open it differently for adding new vs editing existing records
* create one form to insert and one form to edit, where the edit form is bound to a query, and the insert form saves data following a button's click event.
* some other method?

I guess I need some Access 'best practice' info. Having a bit of experience with databases, but none with Access, I initially started Access development by dumping some controls on a form and then writing an Insert query to save the data once the form had been fully completed and the Save button clicked. I'm starting to think this is not the best way to use Access.

I have a large form that contains about 200 fields. These are a mix of fields keyed in directly from source documents and fields that are calculated (to check the correctness of provided data or add extra information).


Using a Yes/No field to trigger a calculation in another field
I have a billing form that was created from a query based on the table that all these fields are from. In this billing query is where all these calculations are happening so they can be displayed on the billing form.

TotalTuitions is a field that exists in my main table and it is calculated in the billing query by adding (4) other tuition type fields.

MultiClassDisc is a field in my main table and is in the billing query.

MultiClassTrigger is a field in my main table that works from a Yes/No combo box. This will also be in the billing query.


Adding fields from different tables onto the same form without error
I have a database with 4 tables that have a one to many relationship. These tables all have a primary key and foreign keys. I would like to create synchronized combo boxes on a form so I created two more tables containing data for those combo boxes and linked them to the parent table to create a relationship. When I add fields from these two new tables onto a form with fields from other tables and look at the form in form view, the fields disappear. Previously when adding fields from other tables to a form, a wizard box pops up asking how these tables are related.

Can anyone either tell me how to manually get to this wizard box or how to fix the problem of the missing fields in form view


Query not showing all records or showing duplictae records
Here are a few items to show how my main table is set up:

TimesheetDataID (PK)
DateID (FK)
StaffID (FK)
TeamID (FK)
ProjectsID (FK)
ModuleID (FK)
SequenceID (FK)
OperationID (FK)
Order
SequenceNo
OperationNo
Hours
Comments

For each record, the blue fields will always have an entry, but then only entries in either the red or green green fields; black is optional.

I then create a query of the main table, adding in extra fields from the FK tables, but then get only the records from the main table where the red fields were populated.

I can get all records if I exclude the red fields, but I need the red fields data. Other options I have tried give me multiple entries. I have tried to create a join that includes all records from one table and only records form another if items match, but this doesn't work or gives me a message of

Help please. I am a novice here


having to deal with massive query's
I've got a query of almost 2 million records and I want to perform a market analysis by adding a form which let me filter by Product Group (99 entries) and Discount Group (89 entries).
The problem I am encountering here is that, plain and simple, it takes ages to filter these fields. takes a lot even to switch from table view to design view or whatever you call it.
what I do is: building a form from the Product Group query; adding a subform from the 2-million-entries table.
I didn't do anything professional though. I think I am missing something here.
is there a well known way to make forms run faster when you have lots of records


error 3190 too many fields.
I've been adding fields using vba (DAO) and have been deleting them in access under design view of table. It looks like the column gets deleted, however, the info about how many fields exist aren't updated.

I am now unable to add any more fields (says I already have 255). How can I fix this?


query function question
I have four fields containing values returned by a query. I want to add a field to the query that will calculated the average which I can then include in a form and report. When I build I get an error. I'm only learning VBA and SQL and I have exhausted all the possible simple solutions.also, I have been reading some posts that suggest adding to the SQL. How does one know where to add to the SQl statement(s


query function question
I have four fields containing values returned by a query. I want to add a field to the query that will calculated the average which I can then include in a form and report. When I build I get an error.

I'm only learning VBA and SQL and I have exhausted the possible simple solutions.also, I have been reading some posts that suggest adding to the SQL. How does one knowwhere to add to the SQl statement(s)?


can you combine & format a field in one query?
I have two queires. one where I format two fields to be ten digits long & the 2nd query just combines both fields. Is there a way this can be done in one query? I tried adding the mergefield into my 1st query but I get a pop-up window asking to enter [OBG] & [OBL].

Format (1st query)
OBG: Format([OBG_NO],"0000000000")
OBL: Format([OBL_NO],"0000000000")

Combination (2nd query)
Account Number: [OBG] & "" & [OBL]


Too many fields in a report
I am have a report that has too many fields. The fields I need to display in the report are all required and they take about 277 fields to create.

I can achieve the desired result by calling subform(s) if I am not mistaken. (correct me if I will still be limited on my fields) However, I need each of the subforms to pull data in regards to my paramater query on my main report.

I can achieve this by adding the paramater field in each subform but then it will ask the parameter for every subform on my report. Is itpossible to make it so I only ask the parameter once?

The report is serving as a complete listing of an entry in my database. (separate but 1to1 linked tables linked by the main table AutoNumber)


Append Query to append fields to other fields
I have a Append Query to append fields to other fields. One of the fields is call Baptism (h1). When I go to run the query it tells me that Baptism(h1) does not exist. When I add a space again between Baptism (h1) the query will work. HoweverI have a command button for the user, and they will not know to adjust the query each time.