Queries :: Grouped Query That Returns A List Of All Periods

Nov 21, 2013

I have a grouped query that returns a list of all periods qryPeriod

Code:
SELECT [tblMaster].Year, [tblMaster].Week, ([Year] & " - " & Right(0 & [Week],2)) AS Period
FROM [tblMaster]
GROUP BY [tblMaster].Year, [tblMaster].Week, ([Year] & " - " & Right(0 & [Week],2));

Year | Week | Period
2013 | 48 | 2013 - 48
2013 | 49 | 2013 - 49
2013 | 50 | 2013 - 50
2013 | 51 | 2013 - 51
2013 | 52 | 2013 - 52
2014 | 1 | 2014 - 01
2014 | 2 | 2014 - 02
2014 | 3 | 2014 - 03
2014 | 4 | 2014 - 04

[code]...

these are then used as the start of a between lookup against [tblMaster] along with the last record in the qryPeriod (2014 - 04)...I will need 4 queries so If someone can sort out last period - 8 I can probably do the others.would DLookUp do this say return the last 4 values, Last 6 Values and Last 8 Values

View Replies


ADVERTISEMENT

Queries :: Form List Based On A Query Returns Original ID Value Not Field Text

Aug 14, 2014

I have a table x where the field value is selected via a combo box in a form that is populated from another table z.

When I look in x, it appears to have correctly stored the text from z, not the ID number.

I then built a Query, qX, which looks in x and grabs the fields I want. That query shows the text correctly in each selected field.

Now when I build a form, frmQx and use a List control, it displays some of the fields as ID values from the original table z, not the text values.

How can I get round this? I've searched and searched for an answer, sigh. Maybe I'm just not quite certain how to phrase the search.

View 5 Replies View Related

Queries :: Add Columns To Grouped Max (date) Query

Mar 14, 2013

I have a database that tracks students and their test scores. I am trying to figure out a way to where it will pull the student's latest test score and compare it to see if they fall within standards. So far I have 3 tables.

tblStudents
studentID
studentName
studentEmail

tblResults
resultID
resultStudent
resultDate
resultTest
resultScore

tblTests
testID
testName
testPassScore

The query I have written looks like this: SELECT tblStudents.studentID, Max(tblResults.resultDate) FROM tblStudents LEFT JOIN tblResults ON tblStudents.studentID = tblResults.resultStudent GROUP BY tblStudents.studentID." The data pulls just fine at this point. What I'm trying to figure out is how to then add more columns to this query to start doing comparisons. When I try to add more columns it tries to use them for additional grouping and adds many more records.

View 8 Replies View Related

Queries :: Totals Query Grouped Across Columns

Mar 5, 2015

I have simple table data structured as follows;[Origin], [Code], [Weight]. The Code field is a lookup field that will contain one of 8 choices; 10, 13, 13c, 23, 25, 27, 27a, & other. The other fields are pretty much self explanatory. Sample data would look like this:

Code:
Origin Code Weight
Edison 13 4.25
Edison 13c 2.87
Piscataway 10 5.45
Middlesex 23 1.24
Edison 13 5.21

What I need to create is a totals query where I first group by origin value, then a column for each "code" value which totals the weight for that "code". A sample output would look something like this:

Code:
Origin 10 13 13c 23 25 27 27a other
Edison 9.46 2.87
Piscataway 5.45
Middlesex 1.24

The only way I can think to accomplish this is to restructure the original table to include a field for each code and enter the weight in the appropriate 'code' field. If this is the only solution then Ill have to live with it, but is there any way to create this output using my original structure?

View 1 Replies View Related

Queries :: Query Between Dates But With Count Grouped By Model Number?

Mar 18, 2013

I currently have a query of between dates which the user enters, but when I try to get a total count of model numbers it gives totals for each date. I am trying to get a count of model numbers between these dates with the dates excluded in the grouping.

View 14 Replies View Related

Queries :: Query That Returns All Record For Only Last Date

Apr 30, 2014

i want to write a query that display records for only last date entered in a table and i want to show all the records from last date entries in a table.

View 6 Replies View Related

Queries :: Query That Returns No-matching Records

Jul 15, 2013

Suppose I have two tables with fields as follows:

Assigned Courses (Courses that employees should do)Employee number
Assigned Course Name

Fulfilled Course (Courses that employees have done)Employee number
Fulfilled Course

Now I need to do a query that tells me which employees haven't done. Is there anyway to do that that is not an exclude query?

View 4 Replies View Related

Queries :: Query Returns (DELETE) As Field Value

Mar 18, 2015

I have a query, and when I execute it, the correct data set is returned. However one field shows "DELETE" as the value for all records.

when I ran it on the SQL server management studio as a test it ran fine. End user does not have access to the tables, so I created a small MS Access application. When I ran the query in MS Access, then connected table dbo_DocRef > Notes shows a value of DELETED.

What am I missing?

View 5 Replies View Related

Queries :: Query Returns Wrong Values

Aug 30, 2014

My problems is that when I use below setup without the "Notes" tabel, they query works fine, and returns the amount of spares and time used by a single WBS ID. (Customer). But when I add another tabel and link it together. Its returns load of lines with the same data - it looks like its copying its self, over and over again.

View 4 Replies View Related

Queries :: How To Handle Report From A Query That Returns No Records

Jun 30, 2014

A have a report that is based on a query that works fine when the query returns records.

However if the query (legitimately) returns no records then the report fields (numeric) are blank.

How can i get the query or report to show either "0" or "no data" when no records are returned? I have tried the NZ function but that didn't work so i don't think that is the right approach here?

View 3 Replies View Related

Queries :: Query With Instr Returns Unexpected Results

Aug 26, 2014

I have Access 2010. If I run this query

Code:
SELECT Reference,
InStr(1,FunctionThatReturnsLongString(), ResponsiblePerson) As MyField
FROM MyTable
WHERE
(Reference ='ShouldBeOut'
OR
Reference = 'ShouldBeIn1'
OR
Reference = 'ShouldBeIn2')

It returns 3 rows, with values in MyField of 0, 23 and 355.

Now I add 1 more where clause to filter on MyField and the query looks like this:

Code:
SELECT Reference,
InStr(1,FunctionThatReturnsLongString(), ResponsiblePerson) As MyField
FROM MyTable
WHERE
(Reference ='ShouldBeOut'
OR
Reference = 'ShouldBeIn1'
OR
Reference = 'ShouldBeIn2')
AND
(
InStr(1,FunctionThatReturnsLongString(), ResponsiblePerson) > 0
)

So, you would think that it should return 2 values (ShouldBeIn1 and 2), but it doesn't. It only return the value where the Instr returns value of 23. The one with the value of 355 also disappears. Why would that be? Surely Instr does not return a byte?

View 10 Replies View Related

Queries :: Date Formatting Criteria - Query Returns No Data

May 7, 2015

I have a query in my database called "Open Date" which is a date formatted field when imported as DD/MM/YYYY. The criteria is set to:

>=#06/04/2014# And <=#05/04/2015#

The query works just fine. My customer has come back and asked for the way the date is displayed to be in the YYYY-MM-DD format (i know it's not ideal but thats what they want). When i've done that using the following

Format([Table].[Open Date],"yyyy-mm-dd")

The query then returns no data.

View 6 Replies View Related

Queries :: Recordset Is Showing As Empty But Query Wizard Returns Records

May 12, 2014

I'm trying to write record set contents to excel. My query runs perfect in access query wizard, but recordset showing as null. My VBA code

Code:

Dim cnn As ADODB.Connection
Dim recordst As ADODB.Recordset
Dim strSQL As String
Dim strPath As String
Dim appXL As Excel.Application
Dim wb As Excel.Workbook

[Code] ....

View 5 Replies View Related

Queries :: Summarize Income And Expenses Over Periods

Apr 1, 2013

I have some issues with summarizing in a query. My intention is to summarize income and expenses over periods

This is how it should look like.

Periods_____Total income___Total expenses
01-2013_____234__________435
02-2013_____533__________132
03-2013_____345__________853
etc

I made the following query to get the result I wanted.

Unfortunately, the result seems to be multiplied instead of summarized.

View 3 Replies View Related

Queries :: Number Of Pay Periods In A Fiscal Year

Jan 23, 2014

If my fiscal year ends 12/31 but the pay period doesn't end until 1/10, how do I get a query to tell me there are 25.3 pay periods remaining (counting from the last pay period 1/11)?

View 3 Replies View Related

Queries :: Combine 4 Tables Data That Falls Within Dates - SQL Query Returns No Records

Oct 2, 2013

I have a complex database app that has a form called from the main form. It requires two inputs: BeginningDate and EndingDate and I use a calendar picker for date selection. Using data assigned to a variable, I build the SQL query in VBA. The result is:

Code:
SELECT [1733_All Print Orders].[Application], Sum([1733_All Print Orders].[TotalImages]) AS SumOfCCPC
FROM [1733_All Print Orders]
WHERE [Application] = 'CCPC' AND [StatementDate] >= #9/3/2013# AND [StatementDate] <= #9/30/2013#
GROUP BY [1733_All Print Orders];[Application]

[1733_All Print Orders] is a defined query that combines 4 tables together and there are data that falls within the dates for CCPC. But the query returns no records.

I pasted the query to the query builder and using different combinations, I isolated that the [StatementDate] >= #9/3/2013# portion is what returns no records

To complicate matters even worse, prior to today, it worked. I made some adds and changes to another area of the application, but did not touch this code.

View 1 Replies View Related

Queries :: Add Comparatives To Grouped Totals Queries

May 23, 2013

I have a totals query of phone call charges by phone number and I want to show a "budget" amount for each number (same amount for all) and a variance against that budget.

How I can do this at the grouped (phone number) level rather than at the individual call level?

Please see current query below:

SELECT Call1CurrentTbl.電話番号(MSN), Sum(Call1CurrentTbl.料金) AS 料金OfSum
FROM Call1CurrentTbl
GROUP BY Call1CurrentTbl.電話番号(MSN), Call1CurrentTbl.[レコード区分], Call1CurrentTbl.表示区分
HAVING (((Call1CurrentTbl.[レコード区分])=2) AND ((Call1CurrentTbl.表示区分)=10));

View 2 Replies View Related

Queries :: Selecting Max From Grouped Data

Nov 26, 2014

I am having trouble writing what I think should be a fairly straight forward query... I am have a table with 2 fields, the first contains a list of subjects, the second contains a list of scores that students got in their tests in that subject.

So the data looks like this:

Maths 54
Maths 69
Maths 41
English 71
English 55
Art 44
Art 43
Art 66

I would like to write a query that selects only the highest score for each subject and presents it like this:

Maths 69
English 71
Art 66

View 4 Replies View Related

Date Periods In Query

Jul 31, 2005

I am tyrying to get my query to analyse the last three months of data but typing the following in the criteria section under Potting Date
Date()-90

An error is occuring.

Can anybody assist with this problem, I think I am writing the wrong thing

Regards Karen

View 6 Replies View Related

Queries :: Count Active Members Grouped By Month

May 5, 2015

I am trying to get active member count by month in MS Access.

Table looks like this:

admin ID firstName sDate eDate 1 Adam 01/01/2015 01/04/2015 2 Ben 01/02/2015 15/04/2015 3 Carl 01/02/2015 13/03/2015 4 Don 13/01/2015

Required output would be like this

2015-Jan: 2
2015-Feb: 4
2015-Mar: 3
2015-Apr: 3
2015-May:1
etc..

I got this far:
SELECT DISTINCT count(a.ID) as mCount, year(dimDate.Date) as [Year], month(dimDate.Date) as [Month]
FROM admin AS a INNER JOIN dimDate ON (dimDate.Date >= a.sDate AND dimDate.Date <= a.eDate) OR (dimDate.Date >= a.sDate AND dimDate.Date = null)
GROUP BY year(dimDate.Date), month(dimDate.Date)
ORDER BY year(dimDate.Date), month(dimDate.Date)

Query4 mCount Year Month 31 2015 1 71 2015 2 44 2015 3 13 2015 4

View 2 Replies View Related

Queries :: Concatenate Multiple Records Grouped By Date?

May 2, 2014

I have a rates table with 3 fields : [RateDate], [CurrencyID], [FXRate]

I also have a currencies table with 2 fields : [CurrencyID], [CurrencyCode]

Where [CurrencyCode] is just the 3-character currency code (i.e. EUR, GBP, USD etc.)

The rates table uses a composite primary key across [RateDate] and [CurrencyID] - i.e., there should only be one unique record for each combination of date and currency.

Not every date will be present in the table and, within each of those dates, not every currency will be present (but at least one, obviously)

I want to write a query which will return two fields. The first field will contain all of the unique dates in the table for which the [FXRate] field (for any currency) is blank / null. Straightforward enough :

Code:
SELECT [tblRates].[RateDate]
FROM [tblRates]
INNER JOIN [tblCurrencies]

[Code]....

But this gives me a separate record for each date / currency combination in the returned dataset. I just want one record per date and all of the currency codes in a single string (separated by some delimiter) as a text field.

View 3 Replies View Related

Queries :: Calculate Percentage On Grouped And Filtered Data

Jul 20, 2015

I'm very new to Access and I'm attempting to write an expression in a query that will calculate the % of the count of "Exchange" field (Exchange is a text field and is grouped and the count based on each unique name) where the total count is based on the filter where "Group" = 'FS' or 'S'.

The below seems to work, but there is a better way of going about this (especially if I have to add more filter criteria). I added a pic of the query I'm trying to build.

PercentofTotal: (Count([Exchange])/DCount(Count([Exchange]),"[Holdings]","[Asset Group] = 'FS' OR [Asset Group]='S'"))

View 11 Replies View Related

Queries :: Grouped Sum With Proportion Of Total (Access 2007)

Feb 20, 2015

I have a table of transactions (close to 1m records) from which I want to query totals by currency and direction (ins and outs) and then also show the proportion of the overall total for each currency. All amounts are absolute (i.e. ins and outs both represented by positive numbers)

To throw some added complexity into the mix, the transaction table records only refer to account (AccountID), which links to a separate account and currency tables.

I can query the sum totals by currency and direction (although I'm not totally happy with the SQL syntax .

Code:
SELECT tblCurrencies.CurrencyCode,
tmp1.SumPay,
tmp1.SumPayUSD,
tmp2.SumRec,
tmp2.SumRecUSD

[Code] .....

I'm struggling to get the proportions per currency though? I tried adding :

Code:
((SumPayUSD+SumRecUSD)/Sum(tblTransactions.USDAmount)) AS Proportion

But it tells me that the specified field 'tblTransactions.USDAmount' could refer to more than one table listed in the FROM clause of the SQL statement.

View 3 Replies View Related

Queries :: Using Variable In A List Field Query - Getting Complete List On Initial View

Mar 28, 2014

In my access form I provide the user a list of locations from various countries in a listbox . But the list is too long so I provide him a combobox for selecting a country. Selecting the country should update the listbox showing only the locations in that specific country.

So my SELECT from the listbox must cover the unselected state and show all entries and when a country is selected it must narrow the selection.

I tried to get this happen with the following SELECT statement containing a variable. Choosing a country in the Combobox results in a change of the variable and in a requery. This works after the first country is selected and for each country change, but the initial list is empty.

VBA in the loadform
'Application.TempVars.Add "varcountryselect", "*"
SELECT in the listbox "lstlocationsperproject"
SELECT tbllocations.locationID, tbllocations.country, tbllocations.localstreet, tbllocations.localcity FROM tbllocations WHERE ((tbllocations.country) Like [TempVar]![varcountryselect]);

VBA in the combobox
Application.TempVars("varcountryselect") = [Form]![kombcountryselect].Column(0)
Me.lstlocationsperproject.Requery

The values in [kombcountryselect].Column(0) are texts like "SPAIN", "MEXICO", etc.

Any hints, how I have to use the * for getting the complete list on the initial view ?

View 5 Replies View Related

Forms :: When Using Query With Grouped By Option / Can't Edit Record In Table

Aug 19, 2015

I have a table called "Workorders" that I have created a split form for and have customised it so that it had combo boxes etc to enable the user to change the record data.I then needed to add some existing queries to the form so that I could calculate some totals from records in a few other tables. This all works fine EXCEPT that I can no longer change any of the fields in the records.

I am sure that it is something to do with the fact that the 3 queries I added to the form use the "Grouped by" option and based upon my Googling, this is probably the issue.Here is one of the queries:

SELECT DISTINCTROW [Workorder Labor].WorkorderID, Sum([BillableHours]*[BillingRate]) AS [Labor Total]
FROM [Workorder Labor]
GROUP BY [Workorder Labor].WorkorderID;

I need to have the totals and I also need to have the ability to change the data..

View 8 Replies View Related

Queries :: SQL Like Returns False Records

Jul 9, 2015

SELECT tbl_Visits.vst_VisitDate, tbl_Visits.vst_Complaint, tbl_Visits.vst_Diagnosis
FROM tbl_Visits
WHERE (((tbl_Visits.vst_Complaint) Like "*asthma*")) OR (((tbl_Visits.vst_Diagnosis) Like "*asthma*"));

returns records with no occurrences of asthma. about 4 of 638 hits.

View 14 Replies View Related







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