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
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.:
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.
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])]' " ...
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?
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?
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.
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.
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 :
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.
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.
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 .......
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?
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);
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];
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.
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.
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?
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.
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.
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).
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.
