Queries :: Adding Value To Query Output

Dec 12, 2013

I have a field in a query that shows amount of hours a site is covered. This field name is Coverage hours and it changes depending on the number of work days in a month. This part is working correctly but some times additional hours may be requested by the customer.

I want to add a another field to this query that will prompt me if their were any additional covered hours. Then I would have the ability to enter a value and the shown output for this field would be the value entered plus the standard coverage hours in the other field.

I'm not sure how to set up the new field.

View Replies


ADVERTISEMENT

Queries :: Modifying Query Output

Feb 13, 2014

I am trying to modify the output to only show discontinued items. While the query is in datasheet view, the text is read as "YES" or "NO" under discounted items. So, I tried entering "YES" in criteria while in design view, but keep getting an error message stating, "Data type mismatch in criteria expression".

View 5 Replies View Related

Queries :: Multiple Records Appear In Output Of Query

Mar 4, 2015

i have got 2 tables i am trying query against. in table 1 i have a list of records which only appear once in the table under 1 field. in table 2 list of records which may have the same record appear under the same field numerous of times with different data in other fields on the table. i have created the reationship between both tables but i am getting multiple records appear in the output of the query where i only want the record to appear once in the output.

View 2 Replies View Related

Queries :: Get Output Of Query In A Parameter (VBA Variable)

Jul 1, 2015

How could i get output of a Query in a VBA variable ?? I'm trying as below butIi'm getting error:

Dim output As Integer
vari = DoCmd.OpenQuery("qryCompare", acViewNormal, acReadOnly)

Output of a query is a single MAX value.

View 4 Replies View Related

Queries :: Function To Output Line Numbers In A Query - YTD

Apr 18, 2014

Is there a built in function which can be used to create line numbers in a query?

I've written a query to calculate year to date (YTD) points for yachts in a series of races and sorted it in descending order - so yacht 1 is coming first, yacht 2 is coming second etc. I'm looking for a way to add sequential numbering (starting at one and increasing by one for each line) into the query to represent their YTD places. Or this this something that should be left to the report which uses the query?

View 1 Replies View Related

Adding Sum Totals To Output In Excel

Sep 8, 2006

Hello first of all I have learned a lot from this forum so thanks you all who post questions & solutions. Ok I have a table which I use to query info and export to excel. My question is there a way to add sum totals to a few of the columns once it is in excel. I was thinking it would be easier to do a sum statement then just add it as the bottom row while it was exporting. Or would it be easier to do it once it is in excel. Here is my code:

Private Sub Command63_Click()

Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

strSQL = "SELECT tblCONSOLIDATED.ACCOUNT1, tblCONSOLIDATED.COMPANY_NAME, tblCONSOLIDATED.CUSTOMER_TYPE, tblCONSOLIDATED.ADDRESS1, tblCONSOLIDATED.ADDRESS2, tblCONSOLIDATED.CITY, tblCONSOLIDATED.STATE, tblCONSOLIDATED.ZIP, tblCONSOLIDATED.CONTACT_NAME, tblCONSOLIDATED.E_MAIL, tblCONSOLIDATED.TELEPHONE, tblCONSOLIDATED.FAX, tblCONSOLIDATED.REP_NUMBER, tblCONSOLIDATED.PROMOCODE, tblCONSOLIDATED.SALESCODE, tblCONSOLIDATED.CURRENT_YTD, tblCONSOLIDATED.PRIOR_YTD, tblCONSOLIDATED.PRIOR_TOTAL, tblCONSOLIDATED.YEAR2_TOTAL, tblCONSOLIDATED.YEAR3_TOTAL, tblCONSOLIDATED.YEAR4_TOTAL " & _
"FROM tblCONSOLIDATED"


strWhere = "WHERE"

strOrder = "ORDER BY CURRENT_YTD DESC"

If Not IsNull(Me.txtCSONME) Then
strWhere = strWhere & " (tblCONSOLIDATED.COMPANY_NAME) Like '*" & Me.txtCSONME & "*' AND"
End If

If Not IsNull(Me.txtCSOSLD) Then
strWhere = strWhere & " (tblCONSOLIDATED.ACCOUNT1) Like '*" & Me.txtCSOSLD & "*' AND"
End If

If Not IsNull(Me.txtCSOARN) Then
strWhere = strWhere & " (tblCONSOLIDATED.CONTACT_NAME) Like '*" & Me.txtCSOARN & "*' AND"
End If

If Not IsNull(Me.txtCSOCTY) Then
strWhere = strWhere & " (tblCONSOLIDATED.CITY) Like '*" & Me.txtCSOCTY & "*' AND"
End If

If Not IsNull(Me.txtCSOST) Then
strWhere = strWhere & " (tblCONSOLIDATED.STATE) Like '*" & Me.txtCSOST & "*' AND"
End If

If Not IsNull(Me.txtCSOZIP) Then
strWhere = strWhere & " (tblCONSOLIDATED.ZIP) Like '*" & Me.txtCSOZIP & "*' AND"
End If

If Not IsNull(Me.txtCSOSSM) Then
strWhere = strWhere & " (tblCONSOLIDATED.REP_NUMBER) Like '*" & Me.txtCSOSSM & "*' AND"
End If

If Not IsNull(Me.txtCSOM1) Then
strWhere = strWhere & " (tblCONSOLIDATED.PROMOCODE) Like '*" & Me.txtCSOM1 & "*' AND"
End If

If Not IsNull(Me.txtSLCYYD1) Then
strWhere = strWhere & " (tblCONSOLIDATED.CURRENT_YTD) BETWEEN " & Me.txtSLCYYD1 & " And " & Me.txtSLCYYD2 & " AND"
End If

If Not IsNull(Me.txtSLLYYD1) Then
strWhere = strWhere & " (tblCONSOLIDATED.PRIOR_YTD) BETWEEN " & Me.txtSLLYYD1 & " And " & Me.txtSLLYYD2 & " AND"
End If

If Not IsNull(Me.txtSLPYR11) Then
strWhere = strWhere & " (tblCONSOLIDATED.PRIOR_TOTAL) BETWEEN " & Me.txtSLPYR11 & " And " & Me.txtSLPYR12 & " AND"
End If

If Not IsNull(Me.txtSLPYR21) Then
strWhere = strWhere & " (tblCONSOLIDATED.YEAR2_TOTAL) BETWEEN " & Me.txtSLPYR21 & " And " & Me.txtSLPYR22 & " AND"
End If

If Not IsNull(Me.txtSLPYR31) Then
strWhere = strWhere & " (tblCONSOLIDATED.YEAR3_TOTAL) BETWEEN " & Me.txtSLPYR31 & " And " & Me.txtSLPYR32 & " AND"
End If

If Not IsNull(Me.txtSLPYR41) Then
strWhere = strWhere & " (tblCONSOLIDATED.YEAR4_TOTAL) BETWEEN " & Me.txtSLPYR41 & " And " & Me.txtSLPYR42 & " AND"
End If

If (Me.PROSPECTBX) = True Then
strWhere = strWhere & " (tblCONSOLIDATED.CUSTOMER_TYPE) Like 'P' AND"
End If

If Not IsNull(Me.txtSLCLS) Then
strWhere = strWhere & " (tblCONSOLIDATED.SALESCODE) Like '*" & Me.txtSLCLS & "*' AND"
End If


If strWhere = "WHERE" Then
strWhere = ""
Else
strWhere = Trim(Left(strWhere, Len(strWhere) - Len("AND")))
End If

Set qryDef = dbNm.QueryDefs("qrySALESDATA")
qryDef.SQL = strSQL & " " & strWhere & "" & strOrder

DoCmd.OutputTo acQuery, "qrysalesdata", "MicrosoftExcel(*.xls)", "QUERY RESULTS.xls", True, ""


End Sub

Sorry for adding the whole thing but I'm not sure it should be done after "Query Results.xls" is written or during the select statement.

Thanks in advance for your help.

Bryan

View 1 Replies View Related

Queries :: Adding Calculations To Queries Based On Columns In Query

Feb 18, 2014

I am trying to add calculations to queries based on columns in the query... it seems to randomly expect 'Expression' or 'Group by' as column types, and Im having to create 3 sets of queries following on from each other to de-dupe data and allow filters on calculated values.

Also I've got a function which turns a date into a quarterly cohort, e.g. Oct 2013 -> 20134. I use ot on a lot of dates. I created a VBA function, CohortQ used as follows in queries:

Cohort: IIf Year([InputDate]) < 1990 or Year([InputDate]) > 2020, 0 CohortQ([InputDate]))

In the VBA, InputDate is defined as a date

Code:
Function CohortQ(InputDate As Date) As Integer
If InputDate = 0 Then
CohortQ = 0
Exit Function
End If

[Code] .....

But when I run it on a date field, it gives me a data mismatch error. I can't step through as it's working on 600K rows.
If I put the function into the query,

Cohort: IIf Year([InputDate]) < 1990 or Year([InputDate]) > 2020, 0 Year([InputDate])*10+DatePart("q",[InputDate]))

it works.

View 3 Replies View Related

Forms :: Adding Fields In A Table And Having Output Fill Fields On A Form

Jan 29, 2014

I have a totalquery that runs fine and give me the sum for both fields I'm looking for but I can't get the outputs to fill the fields on the form. I have tried the Dcount query in the control source but that just returns an error and locks up access.

Code:
SELECT [Tble-wcDelays].Causedby, Sum([Tble-wcDelays].HoursDelay) AS SumOfHoursDelay
FROM [Tble-wcDelays]
GROUP BY [Tble-wcDelays].Causedby, [Tble-wcDelays].LinkingID
HAVING ((([Tble-wcDelays].LinkingID)=[Forms]![Frm-ePlusCent]![cleanID]));

That is the query.

View 14 Replies View Related

Queries :: Adding Query Criteria Using VB?

Jul 10, 2015

i have a query name "Query01". I want to add a criteria ">=#1/5/2015#" in the field of "PaymentDate" using VBA.

View 1 Replies View Related

Queries :: Adding Criteria To A Query

Mar 24, 2014

I have one datatable and i have to list the duplicates from it based on different criterias. It contains invoices, we would like to avoid duplicated payments.I'm struggling with a query. Basically i have a working one, i have to add one more criteria to filter out a couple of rows where the text column is "freight", but anyhow i have tried, it's not working. Without that one line where the not equal is, it's working as it should, when i'm adding that line it ruturns the same as before, but it should give me 108 less lines. The below one is based on an other query result called Duplicates_Rule1.Here is the query:

Code:
SELECT * FROM Duplicates_Rule1
WHERE (((Duplicates_Rule1.BSEG_DocumentNo) In (SELECT [BSEG_DocumentNo] FROM [Duplicates_Rule1] As Tmp
GROUP BY [BSEG_DocumentNo]
HAVING Count(*)=1 ))
AND (Duplicates_Rule1.Check) Is Null)
and (Duplicates_Rule1.BSAK_Text <> "FREIGHT")
ORDER BY Duplicates_Rule1.BSEG_DocumentNo;

View 9 Replies View Related

Queries :: Adding Several Criteria To Query Using SQL

Jul 29, 2015

Currently, I am using the following code to add criterias to a Query before I run it:

Code:
qdf.SQL = "SELECT * from [Offshore_Projects] WHERE [Business Field].Value = " & Tall

Unfortunately, I struggle with adding additional criterias for example something like this:

Code:
qdf.SQL = "SELECT * from [Offshore_Projects] WHERE [Business Field].Value = 3" AND [Offshore_Projects] WHERE [Location] = ""Longyearbyen"""

View 2 Replies View Related

Queries :: Adding Percentage Field Into Query

Apr 25, 2014

I have a database which tracks the performance of my team and how long it took them to send something out to the customer. We have a target of 5 minutes.

So I quite a few queries to drill down this information. I have a summary query that takes that information and tells me the total amount of things sent, the amount of things sent in time and the amount of things sent late.

My team have to get at least 95% out on time. So how would I go about adding this bit into the query. The calculation is:

The amount sent on time / the total amount * 100

But is there anyway to add this into a 4th column displayed next to these figures?

Looking around a few people have talked about SQL but I no nothing about this and it seems quite daunting, is there a way to do this as a calculated field?

View 1 Replies View Related

Queries :: Using Append Query But Not Adding Duplicates?

Jul 30, 2013

I would like to use an append query that appends data from a union query into a table, but doesn't add duplicates based on a certain field.

I have been trying to use this code, but it doesnt seem to work.

INSERT INTO [Cotton13/14]
SELECT DISTINCT [Contracted Farmers].*
FROM [Contracted Farmers]
WHERE NOT (SELECT * FROM [Cotton13/14] FROM [Cotton13/14] AS [Cotton13/14] WHERE [Cotton13/14].[OF Codec] = [Contracted Farmers].[OF Codec])

Cotton13/14 is the table I want to append to and Contracted Farmers is the union query. However the code is giving me an error.

View 7 Replies View Related

Queries :: Adding Complex Criteria To A Query

Nov 29, 2013

I've got two tables in my Access 2010 database - both are identical in structure, the difference being one (Dove) contains old data and the other (Dove Data File) contains updated data. The primary key for these tables is "TowerBase".

I want to use an update query to update only the changes from the Dove Data File table into the Dove table - but for certain records only.

I've managed so far to get a query which displays only records that have changed in a particular field (Bells). Up to here I think is all good...

SELECT [Dove Data File].*
FROM [Dove Data File] INNER JOIN Dove ON [Dove Data File].TowerBase = Dove.TowerBase
WHERE (((Dove.Inactive)<>True) AND ((Exists (SELECT NULL
FROM Dove
WHERE [Dove Data File].TowerBase = Dove.TowerBase
AND [Dove Data File].Bells <> Dove.Bells))=True));

But now I want to add in criteria and this is the bit I'm struggling with. I need this query to now display only records where there is no associated record in my Visit Dates table. In other words, the Visit Dates table has the "TowerBase" ID along with a visit date. These records I do not want to see in the query, as I don't want to update any changes for these from the Dove Data File table to the Dove table.

View 1 Replies View Related

Queries :: Adding Fields To Crosstab Query?

Oct 9, 2014

I have created a crosstab query that I will use in a subform and a subreport. However, Access won't let me add the fields to the query that I need to link the subform to the form. Is it possible to add fields to a crosstab query?

View 6 Replies View Related

Queries :: Adding Pull Down Menu To Query

Jul 28, 2014

I have a query built that gives me info per employee. I built a question into the criteria that ask for the employees name. When a person types the name results are given. If the person mispells the name, results are not given.

I have everyones name on a seperate table. How do i create a pull down menu on the query to choose the names?

View 7 Replies View Related

Queries :: Adding Parameters To Count Query

Sep 16, 2013

iwhen i add a field to allow me to add the parameters for a search between to dates. the query will only count the ethnicity of people who complained on the same day and give the others as a single number. i need to allow the user to search between dates as to generate the data for a report.

View 3 Replies View Related

Queries :: Change Output From Check Box To Y / N

Jan 30, 2015

I have created a query and one of the columns of the output displays a check box because this is the data type of the underlying table.

I would like the query to output either Y or N rather than displaying the checkbox.

How would I do this?

View 3 Replies View Related

Queries :: Expression To Output Value As A Number?

Apr 17, 2014

I've created several expressions in a query to test for values in a field (one column for each value for use in a report).

The expressions output the value 1 where the test is true. I planned to sum them to establish how many times the value is true but this isn't working. I am only offered a Count and this returns the number of records. Other fields (numeric) are offering me a sum at the total line and are working as expected.

I assume the expressions are outputting the value one as text rather than as a number but I can't work out how to change this.

View 5 Replies View Related

Queries :: Sum 2 Expressions - Output With 3rd Expression

Apr 26, 2013

I'm trying to sum up 2 expressions.

1st. UsedAF: nz(DSum("AF_lengte+AF_zaagbreedte";"VRMUTSTAF";"VR ID = " & [VR].[VRID]);0)
2nd. UsedRES: nz(DSum("RES_lengte+RES_zaagbreedte";"VRRESSTAF";" VRID = " & [VR].[VRID]);0)

Now with a third expression I like to sum those 2 up.

3rd. Used: [UsedRES]+[UsedAF]

The thing I run into is when 1st has a value of 15 and 2nd a value of 0 the outcome of 3rd is 150 while it should be 15 ofcourse.

When I change the first 2 expressions to Sum (at Totals) the 3rd works properly. Yet the other 2 comes with wrong values.

View 5 Replies View Related

Queries :: Adding Currency Conversion Option Into A Query?

Aug 19, 2014

We have a query that calculates the cost total of three different types of costs between three different parameters. I now need to run a query that prompts the user for the days currency conversion rate to create a new report. I don't want to permanently convert the original fields, it is purely a monthly report where when the report is run is shows both British £ and USD.

View 3 Replies View Related

Queries :: Lookup Records From Table - Adding A Value To A Query

Feb 12, 2014

I have a query which looks up records from a table, now what I would like to do is append the word All Projects to this query - is it possible, and if so how?

View 7 Replies View Related

Multiple Queries Output To Be Displayed In The Form

Dec 15, 2004

Hi,

I am trying view the query output's in different text on the form and I do not want to see them again in query window.How do I hide this.. comments
???

View 2 Replies View Related

Queries :: Incorporate A Date In Output To Statement

Jun 11, 2013

I have a database that enables the banking details for a small club to be generated as an Excel spreadsheet that is then emailed as an attachment to the treasurer. The event procedure below works fine but I have been unable to incorporate the banking date from a control called BankDate to end up with the Excel file being in the form DGC Banking dd-mm-yy - is this possible?

DoCmd.OutputTo acOutputQuery, "Export Banking", "Excel97-Excel2003Workbook(*.xls)", "DGC Banking.xls", True, "", 0, acExportQualityPrint

View 5 Replies View Related

Queries :: Wildcard Like Not Returning Desired Output

Jul 8, 2014

I'm building a query using wildcard "like". See the attached file. My question is that why doesn't the query "result" return "aaaaa" for "aaa" is contained within "aaaaa"?I'm basically limiting my records to those found in tbl_site based on a wildcard "like".

View 12 Replies View Related

Queries :: Adding Test Field To Different Table And Try Different Query Using Like Parameter

Mar 20, 2013

I'm using Access 2003...I have a query that searches a parts table by description:

Like "*" & [Enter in Part Description to search] & "*"

I need to add another search to this query, I added another field to the parts table call manufacturer. I add this field to the above query and added this parameter to it:

Like [Enter in Manufacturer] & "*"

I then added a manufacturer to one of the fields for test purposes. For some odd reason this doesn't work. If I take out the manufacturer parameter and don't enter in anything into the part description the query returns all the records. When i type a description into the part description field the query returns the proper records. When I add back the manufacturer parameter and enter through both parameters only the one record returns showing the test record instead of all of them. If I put a part description in and enter through the manufacturer parameter no records show when they should.

I've tried adding a test field to a different table and tried a different query using the like parameter. The parameter works in a field already in the table but won't work with the new field I added. I've done compact and repair.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved