How do I use a query to "add" two text fields together?
For example, if the fieldalready has "ABC" and I want to update the field of multiple records to have "DEF", then the field will be "ABCDEF" after the update is run.
I looked at the crosstab query, but that looks like it is for numbers and not text. The append query does whole records not just certain fields. butI thought I would ask before I wrote off the possibility.
Thank you for taking the time to report an issue.
What's wrong... Please write below.
IIf([Field 1]"" Or [Field 2]"","Answer B") and this works fine.
When I created a new query where I am joining one Query (Query 1) to another Query that has the expression above (Query 2) I used the relationship Include records from Query 1 and only those records from Query 2 where the joined fields are equal (the joined fields are numeric and the are no null values in the joined fields).
When I use the field that has the expression, it applies that expression to all of the records in Query 1 as well as Query 2.
The field name is txtQ1Outcomes, txtQ2outcomes etc. and all data is stored in a table tblDataStore.
Once all of this data is collated I then want to run a report that shows the audit results by counting the number of times a particular handler for example, has had an outcome of Yes/No/NA for Q1, Q2, etc.
If I create a query and use Q1 as an example then I have two columns, one to group by and one to count and this works really well. However as soon as I add Q2 to the query I get a "summary grid" that shows how many times Q1 has been answered "Yes" and then how many times Q2 has been answered "yes", then how many times Q1 has been answered"Yes" and Q2 has been answered "no" and so on.
As I add more and more fields so the matrix becomes more complex and does not give me what I want.
All I want is "Q1 has these outcomes", "Q2 has these outcomes", and I am ending up with a response with numerous permutations on it.
This what I am getting when I add other columns to the query and this is only for 2 of the 54 fields.
How can I simply get the totals for each individuals question without creating a query for each question and then probably creating a sub report in the overall report that goes back to the team leaders for review?
Do I need to re-arrange the data? I thought about assigning numerical values to each outcome, but really need a count of Yes, No, N/A. For example as an insurance company if there is noinjury to a claimant then several questions would be N/A so I can't ignore them as we need to know if a handler has missed the injury out entirely if it should have been applicable.
So likewise as I add on queries. and in a later stage when I try to add more fields into a one of these queries, I get an error message sayin "QUERY TOO COMPLEX" I searched for online help and found MS ACCESS help.
Followed what they said. but stil it appears. I have to add more fields to this query and aslo some more queries.bt they wont allow it.
the speciality is, when I add a single field additionaly to the existing fields in the query only this appears and once I delete that field no error is given and in all above, I can keep onadding more queries without this prob, but with only lesser (2 or 3 ) fields.
First, my tables are Clients (ClientID, ClientName, Address), LabourRates (LabourID, Type, Rate) and Jobs (JobID, CustomerID, date). Do I create the "Jobs" table first and then create a "Jobs" form off of it?
Next: for the subform, I assume my best course of action is to create a "Job Details" query for my subform. What fields should I need? I'm thinking JobDetailsID, ClientID, JobID, LabourID, Rate, Hours (entered by user).
Basing the subform record source on this query is the best option, right? Do I need JobID in this query? I assume I do, even if for the future.
Ideally, I'd like to select the "Type" of Labour (eg. Overtime: $40.00) in a combo box on the subform and have the "Rate" field in the subform automatically populate with the corresponding rate.
Some people seem to say to add some code to the OnUpdate or OnDirty options (Me.TextBox = Me.ComboBox.Column(3)). Others seem to think that my query will allow me to do that if I set the record source of the subform to the query and the source for the "Rate" text box to.? I'm not sure.
but it only involved a form and not a subform. Both the "LabourID" combo box and the "Rate" text box are in a subform. Does LabourID come from the "Job details" query or from the "Labour" table? What about "Rate".
I assume it's best to avoid lookup fields in tables and use queries instead; Base forms on those queries when possible, especially when you're looking to bind fields.
If I exclude anything (one or all the unwanted values) the query stops returning the blank fields. I've tried some NZ and "" and "*" constructions, but the exact formula eludes me.
With the odd values, like "It's a Cat" I am using "*Cat*" to avoid any issues with characters/symbols. This query looks at values in two columns. The other is a simple requirement that "Purina" be in the field.
"can't add record due to key violations"
I designed this table to be able to hold multiple records of the same person by having the "person id" and the "date" in which they came in as primary key fields. Thatway, a person can be in the table multiple times if the dates are different, so why is the query complaining and why is it not working as intended?
strAttending = "Attended"
SetResultAttending strAttending (this passes my information to a global variable)
And I then get a query result returned based on this criteria, no problem, with a GetResultAttending method in the query. Success.
But when I try to add more than one criteria, The query return is blank.
strAttending = """" + "Attended" + """" + " Or " + """" + "Confirmed" + """"
"Attended" Or "Confirmed"
in the query criteria box, in the query and this does not work. I have also tried passing it without the quotation marks without any success.
I have also tried the query criteria in another query that is identical, without passing variables, and the criteria returns the result I am looking for when I do that.
I have followed the access "Simple Query Wizard" but when I try to actually view the query results, it says "Type mismatch in expression".
If I just use one table, it's fine. As soon as I even add the table (in design mode), it starts coming up with the error when I try to run the query: even though I haven't actually added any of the fields into it.
I have three tables. Each heading is a criteria. Each row is a customer account number. On each row I can assign one letter to each criteria, which are "B", "W" or "S".
What I need is a form that shows this data and allows it to be modified. Usually I would use "Add existing fields" and design my form around those fields. Why can't I now? Because the user can add a new field in using an "Add field" button I made.
So, how do I make the form automatically show any new fields that users have added?