Queries :: Unique / Distinct Result In Time Series

Oct 2, 2014

I have been struggling with understanding Queries related to Time Series. I find it difficult to get the right answer or understanding how to ask the right question Access style.

Table below is a sample data/table example for my question. The intent is to build a query that will return the latest date for each unique/Distinct name. I have Time Series Table that I haven't been able to do this, it doesn't return with Distinct Name. I Also have included a sample database.

TABLE
Name Date_1 Distance
------- --------- -----------
A 1/1/2014 10
B 1/3/2014 5
C 2/1/2014 10
A 1/3/2014 16
A 2/5/2014 3
B 5/1/2014 5
C 6/2/2014 6
C 7/1/2014 7

ANSWER SHOULD BE.
Name Date_1 Distance
------- --------- -----------
A 2/5/2014 3
B 5/1/2014 5
C 7/1/2014 7

View Replies


ADVERTISEMENT

Queries :: Time Series Calculation In Access?

May 23, 2013

Let's say that you have a cookie jar that's to be shared with two children, let's call them Jack and Jill.

You know that to start with (t = 0), there are 100 cookies in the cookie jar

Now the strange thing about Jack and Jill is that they start eating cookies on different days and that their cookie appetite depends on the number of cookies in the jar when they start, Specifically

Jack has an appetite of 10% of the total cookie jar and he starts eating on day one.

Jill eats after Jack and she has an appetite of 5% of the jar.

So what this would look like is:

Day 0 - Cookie Jar has 100 cookies
Day 1 - Jack eats, Cookie Jar has 100 - 100(10%) = 90 cookies
Day 2 - Jill eats, Cookie Jar has 90 - 90(5%) = 85.5

Problem:

1. Given that I have a table containing the starting number of cookies e.g.:

CookieTable
Startingcookies
100

2. Given that I have a table containing a record for Jack and a record for Jill, each with two fields: Eating Start date, Appetite % e.g.:

AppetiteTable
Child, EatingDay, Appetite%
Jack, 1, 10%
Jill, 2, 5%

What will the query look like that:

1. Calculate the number of cookies in the cookie jar over time e.g.

Day 0, Day 1, Day 2
100, 90, 85.5

2. Calculate the total number of cookies eaten by Jack and Jill e.g.

Child, Cookies Eaten
Jack, 10
Jill, 4.5

View 1 Replies View Related

Queries :: Select Distinct For Combobox - Show Only Unique Values

Oct 20, 2014

I am trying to get a combo box to only show unique values, but I can't get it to work properly - it still shows duplicates in the list.

The Row Source is set to

SELECT DISTINCT Areas.ID, Areas.Area FROM Areas ORDER BY Areas.Area;

And the combo is bound to column 2

View 12 Replies View Related

Creating Time Series For Portfolio Values

Sep 3, 2007

Dear All,

once again I need your help, I have no idea how to tackle the following problem. I am taking records of stock market transactions. As a final result I want to have a query which gives me for every end of the day the total value of my portfolio.
Therefore I have two tables. The first one, called tblTransactions with columns - among others - Date, Ticker, Quantity, Price does contain my transactions. The second one, called tblQuotes with columns Date, Ticker, LastPrice contains prices for each security traded for every day.
The following SQL code gives me the portfolio holdings with the respective LastPrices on an arbitrarily chosen date (03/09/07):


SELECT T.Ticker, sum(T.Qty) AS TotQty, Q.qCl AS [Last Price], (T.Qty*Q.qCl) AS [SubTotalValue]
FROM tblTransactions AS T INNER JOIN tblQuotes AS Q ON T.Ticker=Q.qTicker
WHERE T.Date<=#3/9/2007# And Q.qDate=#3/9/2007#
GROUP BY T.Ticker, Q.qCl, (T.Qty*Q.qCl)
HAVING sum(T.Qty) >0
ORDER BY T.Ticker;


This works so far. However, now I struggle with the next step. I want to have a query, which for each date (I could use the date column of tblQuotes) adds all SubTotalValues, i.e. gives me the total value of my portfolio for each day.

Could you give me please some hints on how to proceed from here? I would be very pleased if somebody could help me with that question.

Best regards
JapanFreak

View 4 Replies View Related

Queries :: Calculate Time Difference And Show Result Under Certain Criteria In Query

Jul 9, 2013

ID Up Time Down Time
John 18:00 15:00
Kelvin 08:00 08:05
Melisa 23:00 02:00

This is the Table , i need the Query result show IF [Up Time]-[Down Time] is >=3 hrs

Like Below :

ID Up Time Down Time
John 18:00 15:00
Melisa 23:00 02:00

IDUp TimeDown TimeJohn18:0015:00Melisa23:0002:00

I tried DateDiff("h",[Up Time],[Down Time]) in Field but POP out with error "Syntax error (comma) in query expression 'Table1.[DateDiff("h",[Up Time],[Down Time])]' " ...

View 9 Replies View Related

General :: Identify Cycling / Fluctuations In A Time Series Data?

Mar 12, 2013

I am trying to find an algorithm to identify patterns in my data.

My task is to accomplish whether the data shows a very sharp decline and whether or not it follows previous fluctuation.

If it declines sharply and doesn't follow previous fluctuations it will indicate a production problem.

My time series data is as follows.Also sharp decline according to the below data is highlighted.

Data
-0.027663709
-0.057051957
-0.077941988
-0.070009989
-0.033860193

[code]....

View 2 Replies View Related

Unique Query Result

Nov 15, 2011

I have a query of 11 employees and their pay for jobs done. Only problem is there are 15 completed jobs with some of the employees doing more than one. When I run my query its only displaying unique name results so I'm only getting 11 results of the 15 jobs instead of all 15.How would I change the query to that the results are only unique employee names?

View 1 Replies View Related

Forms :: Set Up A Series Of Controls Then Bind To SQL Queries?

Jul 10, 2014

I have about 12 tables in a 2010 database and I want to create a single summary form showing key details of the database. Most of these will be count fields using various subset of the tables e,g. total members, count of new members this year, count of those not attending a meeting etc. More than one table will be represented on the form.How would I set up a series of controls that I then bind to SQL queries? Or is this not the way to do it?

View 8 Replies View Related

Modules & VBA :: Way To Check If Series Of Action Queries Can Be Run

Mar 4, 2014

Is there a way to check if a series of action queries can be run (without any error) before actually running the queries?

View 1 Replies View Related

Queries :: Create Series Of Records Automatically

Jul 5, 2013

I have a table in access which captures a couple of bits of information. The database is for tenant management and payment records. This is what i am trying to achieve:

In one table I record payment information, when they paid, and how much they paid.

In the lease table I capture information such as first pay date, and the payment schedule, whether this be weekly, monthly, fortnightly etc.

I would like to create a table of sorts which has the next twelve months worth of payments dates. Then when i add a payment to my payment table it will match it up with the correct payment date. This will allow me to track arrears and missed payments.

View 3 Replies View Related

Queries :: Conditional Query To Post Result In Field And Filter Result Records?

Mar 5, 2014

I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg

In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.

View 1 Replies View Related

Queries :: If There Is No Result In Query Need To Have Default Result Zero

Oct 12, 2013

I there is no result in query, I need the default result zero in my form field. I only use query wizard to create queries.

View 5 Replies View Related

Queries :: Converting CSV File Into Text Format - Export Records With Specific Series And Date

Jun 7, 2015

I have a CSV file and want to convert it in a text format with some filtered data and with some formatting. This is an everyday task for me. So I made a table and imported the data in to it by the command :

DoCmd.TransferText acImportDelim, "fo Import Specification", "fo", FileName:="C:UserswelcomeDesktopfo.csv", HasFieldNames:=True

Actually I have a column "SERIES", contains various series like "EQ", "BE", "DR", "BZ", "D1" and so on. And one more column with the dates having 4 / 5 current months dates and one next months date and one next to next month's date. And every date has got several thousand records.

now the issue is that : After importing these several thousand records, I want to export it but with a specific date and with a specific series.

The other thing is that, these dates change every month so if hard coded, the problem will occur the next month.

I use this code for export :

DoCmd.TransferText acExportDelim, "NewFnoSpec", "fnoquery", "C:UserswelcomeDesktopFO Output.txt", True

this code is working fine but when the month will change, the code won't work.

Can we have a date & series picker attached to this query, so it can export the records with the specified SERIES & DATE.

I tried putting a textbox on the form named TxtDate and in a Query ( Design mode ) under the date column, in criteria I have put [Forms]![Futures]![TxtDate] and after putting this line, the query becomes empty and no data is there.

View 14 Replies View Related

Need Distinct Records Of Whle Table But Distinct On One Field

Sep 15, 2006

Hi, Wish if some one could help me ASAP.
I have a table which contains name, tel, email
i need to import only records which have distinct email.
I do need need to import data of all three fields but only which has distinct email.
As there are number of record which are duplicate.
They have different names but same email.
So i need to condition only for distinct email but dump the data in a new table with all three records.
so same names can have different email.
but same email can't have duplicate email.
So need only records which have distinct email.
Please help .......

View 1 Replies View Related

Queries :: Find A Way For Access To Find Unique Dates And Unique Names?

Aug 1, 2014

I have been working on a simple data base for some time now (beginner level) and am still trying to improve it. I would like to do something but before that I would like to have your opinion to know if it is even possible?I have a query QryMainReport:

Start Date/Time
End Date/Time
Employee

At the moment this is what the format of my report looks like (I removed other unnecessary fields):

StartTime----------EndTime---------------Employee
12/06/2014 01:00--12/06/2014 03:00------John Smith
12/06/2014 04:00--12/06/2014 06:00------Jane Doe
13/06/2014 02:00--13/06/2014 05:00------John Smith
13/06/2014 08:00--13/06/2014 08:00------Jane Doe

I would like to do as a report. (Dates would always be from Sunday to Saturday). I am not sure it is possible to do that. I suppose first it would mean:I would have to do a query to separate the times from the dates?I would have to find a way for Access to find the unique dates and unique names?Does it mean I have to use cross tab queries?

View 2 Replies View Related

Queries :: Either Or Select Distinct Row

Aug 12, 2014

Is it possible to change this query to search for either a Name or by an employee ID number? The query runs a report on after update. Currently I can only enter the name not the employee ID

Code:
SELECT DISTINCTROW First(Inventory.Employee) AS FirstOfEmployee, Inventory.[Employee ID]
FROM Inventory GROUP BY Inventory.[Employee ID] ORDER BY First(Inventory.Employee);

View 6 Replies View Related

Queries :: Distinct Count In Subquery

Jun 3, 2015

Extended Cost] and salestran.[Extended Price] for each salestran.[SKU Code/Number], Count the number of unique customers, salestran.[Customer Code/Number], per SKU for any transactions equal to or after salestran.[Transaction Date] 11/1/2014.

Problem is, doing a regular count on the Customer field returns an incorrect value. It counts the number of times the sku appears within the given date range, ie 6 transactions with 2 customers, my count says 6. I need a distinct count on the customer, for the above example I want to see 2. Here is the code I have so far which yields an error of "At most one record can be returned.." distinct count in my Select statement along with the other fields I want to see, ie Summary data and SKU.

SELECT Salestran.[SKU Code/Number], (SELECT COUNT(cd.[Customer Code/Number]) AS Count FROM (SELECT Distinct [SKU Code/Number], [Customer Code/Number] FROM Salestran) as cd GROUP BY cd.[SKU Code/Number]) AS [Number of Customers]
FROM Salestran
WHERE (((Salestran.[Transaction Date])>=#11/1/2014#))
GROUP BY Salestran.[SKU Code/Number];

View 4 Replies View Related

Queries :: Distinct Values In Combo Box

Jun 13, 2014

I have a combo box that I want to display titles of discussion that fall within a date range. I set up the query as such;

SELECT DISTINCT Discussions.Discussion_Title, Discussions.Discussion_Date
FROM Discussions
WHERE (((Discussions.Discussion_Date) Between ([forms]![DiscussionsForm2]![txtStartDate]) And ([forms]![DiscussionsForm2]![txtEndDate])))
ORDER BY Discussions.Discussion_Title;

It works but not quite the way I want it too. I would like the combobox to display only unique titles but right now it shows the same title if it was used on another date. I guess its showing distinct titles and dates, while I want it to display just distinct titles.

View 2 Replies View Related

Calculating Military Time And Putting Result To Nearest .25

Mar 14, 2013

I have to fields in military time. I need to calculate and have the result in decimal format to the nearest .25 minutes.

View 1 Replies View Related

Distinct Rows From Non-distinct Data?

Oct 10, 2005

I have data which consists of:

xxxxx123 A.Nother 123456
xxxxx123 B.Jones 123457
xxxxx456 D.Smith 123458
xxxxx456 Z.Zephir 123489

How would I ensure that the query returned only unique rows (where column 1 is unique) based on the first alphabetical record of column 2?

Any ideas?

View 1 Replies View Related

Queries :: How To Pull All Records If Distinct Count Is Less Than 1

Mar 18, 2014

I have a table with the following values.

EMPLLOYEE_ID
JOB
PAY_DATE
LOCATION

Most employees have 10 to 15 rows. I only want to pull ALL rows for employees ONLY if there is a distinct count from DATE_PAID greater than 1. There are cases where an employee might have two pay checks generarted for the same PAY_DATE.

View 3 Replies View Related

Queries :: COUNT With Select Distinct On Expression

Dec 9, 2014

I have the following SQL which returns rows of distinct numbers that are calculated from a field.

SELECT Distinct (Left([ProjectID],4)) AS NumberOfCalls
FROM tblProject;

ProjectID looks something like this : 1307-IND-001 and NumberOfCalls looks like : 1307

I need to count the number of unique "NumberOfCalls" entries that there are in a list of about 50 rows. Currently the SQL returns a list of numbers like this:

1307
1311
1401
etc...

I just want NumberOfCalls to show "3". Is there an easy way to do this?

View 5 Replies View Related

Queries :: Distinct Count In Access 2010?

Jul 15, 2015

I'm trying to write a query to get a count of Volunteers under a certain Job Code for a given year - problem comes in that a single Volunteer may record hours multiple times under a Job Code in a given Fiscal Year. I can't seem to get "Unique Values" to work. From my research it looks like I need a two-part query but (as a newbie) I'm not quite sure how to write that. I have two queries, one that works and one that doesn't.

This one counts total amount of hours volunteered under a given Job Code, it works:

Code:
SELECT tblHoursWorked.FiscalYear, tblHoursWorked.JobCodeLookup, Sum(tblHoursWorked.HoursWorked) AS [Sum Of HoursWorked]
FROM tblHoursWorked
GROUP BY tblHoursWorked.FiscalYear, tblHoursWorked.JobCodeLookup;
HAVING (((tblHoursWorked.FiscalYear)=[Enter Year:]));

This one attempts to count number of Volunteers that worked under each Job Code in a given year - it instead counts number of entries under that job code. What I think I need to do is count unique instances of the NamesIDFK, but I can't seem to get that to work.

Code:
SELECT DISTINCT tblHoursWorked.[JobCodeLookup]
FROM tblHoursWorked
GROUP BY tblHoursWorked.[JobCodeLookup];

1) correcting my second query and 2) putting them into one query so I can use them in a report.

View 6 Replies View Related

General :: Creating Unique Sequential / Reference Number Every Time Report Is Printed

Feb 15, 2013

I have a rental database and I print several contacts for leases etc. what I want to do is have a unique reference number or something inserted to the report every time that it's printed. What I am trying to achieve is to keep track of which tenant corresponds to the report (Lease) printed by using reference number.

There is a seperate form which holds the tenants details and I would like to have a field on that form which would show the same reference number as the report so I can track which report was printed for who.

Whats the best/easiest way to accomplish this?

View 1 Replies View Related

Queries :: Pulling TOP DISTINCT Rows In Final Query

Jun 7, 2013

I've got two nested queries. One finds the oldest inspections from an import table, the second compares that query to the main table again and pulls all 'expired' inspections for each Service Order in the first.

These queries are trimmed down for the essential elements of my question. I can post the full SQL if necessary.

queryDoTheseFirst:

Code:
SELECT TOP 18 ImportTemp.[SO ID], ImportTemp.[Inspection Activity],
[Activity Created]+[AddDays] AS [Due Date]
FROM [Priority List] INNER JOIN (ImportTemp INNER JOIN
queryNeededFirst ON (ImportTemp.[SO ID] = queryNeededFirst.[SO ID])
ON [Priority List].Activity = ImportTemp.[Inspection Activity]

[Code] ....

The refinement I would like to make is, rather than having to pull TOP 18 activities in the final query, just pull TOP 12 [SO ID]s and however many activities come along with them (usually 1 or 2, averages out to about 1.5 so 18 is my compromise). In theory an inspector could have two inspections due on every single property, and would only get 9 unique addresses/[SO ID]s. But I can't figure out how to do that when [SO ID] is no longer unique in the second query.

I suppose I could 'number' the rows in the subquery and add a <=12 criteria on that calculated field, but I'm leery of the processing required (that table contains ~14,000 rows, and most methods of numbering seem to want to use DCount).

View 2 Replies View Related

Queries :: Append Distinct Records Only If Field Have Changed

Jun 5, 2013

Using a database 2010. I have a monthly import from a spreadsheet to one of my tables. Sometimes it has the same previous data from prior month or months that has not changed. I don't want to keep appending this data to my table if none of the fields have changed.

Is there a way to append data to table only if fields have changed in an append query? Ex. (Name, Address, PhoneNum, Rate) If all fields are the same; don't append. If one or more fields are changed; append.

View 14 Replies View Related







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