Queries :: Find The Recent Available Data Based On Dates?

Jul 9, 2014

I have a price table:

Code:
tblPrice
PosNr PriceDate Company Price
1 01.01.2014 Firma A 5
2 02.01.2014 Firma A 7
3 03.01.2014 Firma A 9
4 04.01.2014 Firma A 8
5 06.01.2014 Firma A 6
6 02.01.2014 Firma XY 11
7 03.01.2014 Firma XY 9
8 04.01.2014 Firma XY 7
9 05.01.2014 Firma XY 8
10 06.01.2014 Firma XY 10

And I have a table with the dates, for which I need a price.

Code:
tblDates
PosNr PriceDate Company
1 01.01.2014 Firma A
2 02.01.2014 Firma A
3 03.01.2014 Firma A
4 04.01.2014 Firma A
5 05.01.2014 Firma A (no price available)
6 06.01.2014 Firma A
7 02.01.2014 Firma XY
8 03.01.2014 Firma XY
9 04.01.2014 Firma XY
10 05.01.2014 Firma XY
11 06.01.2014 Firma XY

And now I want to combine this tables, and for the dates which have no price, the last price should be taken.

Code:
tblResult
PosNr PriceDate Company Price
1 01.01.2014 Firma A 5
2 02.01.2014 Firma A 7
3 03.01.2014 Firma A 9
4 04.01.2014 Firma A 8
5 05.01.2014 Firma A 8 (actualy no priceavailable, so take last price)
6 06.01.2014 Firma A 6
7 02.01.2014 Firma XY 11
8 03.01.2014 Firma XY 9
9 04.01.2014 Firma XY 7
10 05.01.2014 Firma XY 8
11 06.01.2014 Firma XY 10

how I can get this?

I have this code, but it need hours.

Code:
SELECT tblDates.PosNr, tblDates.Company, tblDates.PriceDate, (SELECT TOP 1
B.Price
FROM
tblPrices As B
WHERE
B.Company = tblDates.Company
AND
B.PriceDate <= tblDates.PriceDate
ORDER BY
B.PriceDates DESC ) AS Price
FROM tblPrices RIGHT JOIN tblDates ON (tblPrices.PriceDate = tblDates.PriceDates) AND (tblPrices.Company = tblDates.Company);

View Replies


ADVERTISEMENT

Find Most Recent Invoice And Most Recent Funding For Client

Feb 19, 2008

Hello all

I'm sure the answer is on here somewhere but trying to find it and then getting it to work is a bit of a problem, so i resorted to posting.

I have 5 tables
Partnership Details, Invoices, Funding, Communication, Communication Types.

the field Partnership Name in the Partnership Details table has a one to many relationship with the partnership name on tables: Invoices, Funding and Comunication.

What I want to do is create a query that will show the most recent invoice (detirmined by date) and the most recent funding (detirmined by date) for each partnership.

I can do this using max if I only want the feilds Partnership name, invoice date and funding date. however I want to create a report that will show several feilds from partnership details and I want to show the invoice no. and amount as well as the date and also the amount and notes field for funding

Is anyone able to help?

Thanks
Niyx

View 3 Replies View Related

Queries :: Create Query To Find Certain Records Based On Data In CSV File

Aug 14, 2014

This may not even be possible, but I am looking to create a Query that can locate records in an Access Table based on 2 columns of data that I have stored in a CSV file.

My table contains several fields, 2 of which are "Dept" and "SKU" and has over a million records.

My CSV file contains 3 fields: "Dept", "SKU" and "Total" - total being the number of times that particular Dept/SKU combination is used.

I need to be able to parse the dept/sku values from each row in the CSV to the query and locate only the records that contain the same values in the Access table.

The plan being to delete out those that are identified by the query.

View 6 Replies View Related

Queries :: Get Most Recent Record Based On 3 Different Fields

Jun 2, 2014

I have a table similar to the following:

PatientID | LabID | LabDate | Result
001 | 55 | 01jan14 | 9.5
001 | 55 | 01feb14 | 10.0
001 | 55 | 01mar14 | 8.7
001 | 66 | 30jan14 | 11.2
001 | 66 | 30feb14 | 15.4
001 | 66 | 30mar14 | 13.0

002 | 55 | 01jan14 | 12.1
002 | 55 | 01feb14 | 9.9
002 | 55 | 01mar14 | 14.5
002 | 66 | 30jan14 | 16.5
002 | 66 | 30feb14 | 13.0
002 | 66 | 30mar14 | 10.0

Using a single-step Access query, I need to retrieve, for each PatientID, the most recent LabDate and Result *of a given LabID*. Thus, from the example dataset above, the desired output for LabID 55 is:

PatientID | LabID | LabDate | Result
001 | 55 | 01mar14 | 8.7
002 | 55 | 01mar14 | 14.5

I have searched this forum and others, but have not found an answer that I can directly tanslate to my situation. I have successfully written queries (with included subqueries) that retrieve the most recent of all the Labs, but have failed at obtaining a result dataset that contains only the records within a specified LabID.

For example, the query below fails because whenever the most recent of *all* the LabDates is not the same as the most recent of *the LabDates with a LabID=55*, the correct record is not included in the results. In the example dataset above, 0 records are returned.

SELECT a.PatientID, a.LabID, a.LabDate, a.Result
FROM Labs AS a
INNER JOIN (SELECT PatientID, MAX(LabDate) AS MaxLabDate FROM Labs GROUP BY PatientID) AS b
ON (a.PatientID = b.PatientID) AND (a.LabDate = b.MaxLabDate)
WHERE (((a.LabID)=55));

View 3 Replies View Related

Queries :: Most Recent Data Across Two Fields

Aug 23, 2014

I have a table called tbl Meeting Response. It records the data about churches where I have scheduled a representative. Two of the fields in the tbl Meeting Response are 1. Normal Attendance (how many the church normally runs) and 2. Tour Year (the year we were there). Reps have been scheduled at many of these churches more than once over the years, so there are records for different years for the same church in the Meeting Response table.

I want to create a query that will show a field with the most recent normal attendance from the Meeting Response table. This would require comparing the information between two different fields -- Normal Attendance and Tour Year. 1. If a rep was at that church in 2014, 2013 and 2012, and we have a Normal Attendance number for all 3 years, I want the field to show the attendance submitted in 2014 (the most recent). However, if the rep didn't call in a normal attendance in 2014, and the most recent attendance we have is 2012 (i.e., the Normal Attendance is blank for both 2014 and 2013), then I want that field to show the attendance for 2012 (the most recent).

View 1 Replies View Related

Queries :: Most Recent Data From A Date

Jul 30, 2014

I have a problem here that I've been wrestling with for a while.I have a table, ID, Date, and Grade.It is set up that way, and I tried

Code:
SELECT tblGrades.StudentID, tblGrade.Grade, Max(tblGrade.GradeDay)
FROM tblGrades
GROUP BY tbGrades.StudentID, tblGrades.Grade;

I figured since I'm grouping by both StudentID and Grade, it wont return the most recent grade from that Student. Is there any way for me to NOT group by Grade and just display the results of the Max(GradeDay?)

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 :: Find Dates In One Table But Not Another

Mar 14, 2014

I've got two tables, both are indexed by customer ID, with a series of dates against the customer ID. One has a list of all dates a customer was visited, the other is a list of dates where activity happened on the customer account

I want to get a list of the dates when the customer was visited but where no activity happened on the customer account, i.e. where there is a customer visit date on the customer visit table but no record for that date on the activity table.

How do I do that? I can find all dates where was a date was on both tables, but how to find where its on one but not the other

View 2 Replies View Related

Getting Most Recent Dates Query, Help

Feb 17, 2005

HI, I have a table with multiple records of inspections, so there are multiples of inspection_num and each has a different date. I only want to extract the most recent inspections? I have an example of the table below.

Inspection_num Completed_Date

1000 01/01/04
1000 04/01/03
1000 01/01/03
1003 01/01/04
1003 02/01/04
1004 01/01/04

So I would only like to extract the following:
1000 01/01/04
1003 02/01/04
1004 01/01/04

Thanks

Sherriff

View 2 Replies View Related

Queries :: Find Latest Date In A Table Where Dates Are In 2 Separate Columns And Multiple Rows

May 19, 2015

I am trying to find the latest date in a table where the dates are in 2 separate columns and multiple rows. (there are business reasons why there are 2 dates per row they represent different but comparable activities)

I have a table "Assessment tracker" with the following structure

Name Type
Candidate short text
Unit short text
EV1 Date Date
EV2 Date Date

My Data:

Candidate Unit EV1Date EV2 Date
TH1 10 07/05/2015 25/05/15
TH1 10 07/05/2015 07/06/15

I have a query "Candidate AC Dates" that compares the 2 dates EV1 and EV2 and outputs a 3rd column with the latest date.

Query:
PARAMETERS [Candidate Name] Value;
SELECT [Assessment Tracker].Candidate, [Assessment Tracker].Unit, [Assessment Tracker].[EV1 Date], [Assessment Tracker].[EV2 Date], Max(MaxDate([Assessment Tracker]![EV1 Date],[Assessment Tracker]![EV2 Date])) AS Achdate
FROM UnitData INNER JOIN [Assessment Tracker] ON UnitData.Unit = [Assessment Tracker].Unit

[Code]....

Output:

CandidateUnitEV1 DateEV2 DateAchdate
TH11007/05/2015 25/05/201525/05/2015
TH11007/05/2015 07/06/201507/06/2015

It does this by using a function shamelessly copied from the web somewhere...

Function Maxdate(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Date' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)
' Cycle through each value from the row to find the largest.

[Code]....

This is working well (I think)

I then want to find the latest date for the 2 records i.e. the Max value for the Achdate.

Query:
SELECT [Candidate AC Dates].Candidate AS Expr1, [Candidate AC Dates].Unit AS Expr2, Max([Candidate AC Dates].Achdate) AS MaxOfAchdate
FROM [Candidate AC Dates]
GROUP BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit
ORDER BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit, Max([Candidate AC Dates].Achdate) DESC;

But this is returning

Candidate Unit MaxOfAchdate
TH1 1025/05/2015

I expect it to return

Candidate UnitMaxOfAchdate
TH1 10 07/06/2015

It looks to me like MAX is considering only the day value rather than the whole date. I suspect this is because it is considering the results of the function in the first query as a short text rather than a date field. (I've tried to force this through declaring the variables as dates but don't know where else to force this. (I am UK based hence the DD/MM/YYYY format)

View 14 Replies View Related

Need Query For Max Date To Find Most Recent Letter Sent Using Distinct Addresses

Feb 21, 2014

I'm trying to create a report for how many "nasty grams" (rejection notices) my company has sent to people who keep sending in paper forms when they are supposed to file electronically.Every letter that goes out has information recorded based on whatever they sent to us - so the only remotely reliable way to count how many each person received is by the address on the envelope (people use different names, different business names, use different telephone numbers on the forms, etc).

I just built several queries that feed into a report that gets sent to my boss on a monthly basis to show the people who've sent in more than one paper form and have received our rejection notices more than once.I'm not the greatest at SQL, but I've been trying to find a way to use DISTINCT Addresses, leave all other fields the same (not DISTINCT), to:

1. Only return people who have received 2 or more letters

2. If at least one of the letters was sent more than 90 days ago
AND If at least one of the letters was within the last 90 days
-If at least one was within the last 90 days, only display the most recent send date of the letter (lots of people get back-to-back letters).

3. Display their names, addresses, telephone numbers, the date of the most recent letter sent, count of the total letters ever sent to that person. (the report will already do this, just need Max date)

My first query counts the number of times each address appears in the main table and simply only has [Address] and [CountofAddress]

My second query has the [Name], the [LetterDated] >=Now()-90, and the qryCountofAddress is linked to the main table by [Address], using [Countof Address] >=2...I have tried Selecting Unique Values in the Properties tab. Yes, I have tried INNER JOIN (but can't get the rest of my fields to display once I make addresses distinct).

View 1 Replies View Related

How To Find Record On Form Based On Multiple Queries

Jun 23, 2006

I currently have a form to manage inventory. On the left side of the form, I have a list box that shows all of the items in the database and finds the record on the form based on the selection.

I would like to have toggle boxes or something underneath the list box that when clicked will sort the box by vendor and item and another that when clicked will sort the list by part number and then select the record on the form based on the selection at that time.

I have no idea how to accomplish this...can anyone help?

View 1 Replies View Related

Queries :: Dropdown Box - Find Records Based On A Form

Jul 16, 2014

I have a form with a dropdown box that shows the 'Group by' records in a table.

For example the form shows "Art_6". My table has many records (could be 1000's) that start "Art_6".

I want a macro/query to find all records that start "Art_6" and delete them.

How to find only those that start as described.

View 13 Replies View Related

Queries :: Averages Of Values Based On Dates

Aug 20, 2013

I'm not sure if this is the right forum. If not let me know and I'll move the thread.

Table 1
Date
Measurement A
Measurement B
Measurement C

Table 2
Table 1 ID Link
Data 1

Tables are linked 1-to-many from table 1 to table 2 by ID.

I would like to average the Data1 data per Table1 ID and report it with the Table 1 Measurements.

View 1 Replies View Related

Queries :: Assigning A Number Based On A Range Of Dates?

Mar 19, 2013

I have a table called StockTable with the following fields Location, Status, Serial, Make, Model, LastDate, DotNumber

I also have a table called FCDateRange with three fields

DateStart DateEnd and DotNumber

For example

3/7/13 - 3/13/13 - 1
3/14/13 - 3/20/13 - 2

Im trying to figure how to write a query that if the lastdate from the StockTable falls during the DateStart and DateEnd fields it will assign it the number in the DotNumber field

View 2 Replies View Related

Queries :: Return Records Between Dates Based On 2 Date Fields In A Table

Apr 24, 2013

I have a table which includes a start date field and completion date field for housebuilding.

I am trying to extract all records that have either a started date or a completed date between 2 dates supplied by the user. I have tried to use Between on both fields but that doesn't return results between the fields.

It workd if I just do it on EITHER the start date field OR the completion date field so that implies to me that I need to break it into 2 queries, one returning start date recrods and the other returning completion date records but then I would need to have somthing that removes records that appear in both the start date and the completion date results.

View 7 Replies View Related

Queries :: Query Filter Based On Dates - Excludes Null Values

Apr 17, 2014

how to return all values in a query when a form critieria is left blank. I have made some progress, the combo box criteria queries were fairly simple, but i'm getting stuck with my date criteria. My query doesn't return null values when I want it to.

I want it to return all records (including null values) if the form OpenFrom and OpenTo dates are blank, and just the values between the selected dates (excluding null values) if the form is completed.

Code:
SELECT qryReportSelector2_Authority.*, qryReportSelector2_Authority.ApplicationDate AS ApplicationDateFilter
FROM qryReportSelector2_Authority
WHERE (((qryReportSelector2_Authority.ApplicationDate) Between Nz([Forms]![frmReportSelector]![OpenFromDate],DMin("[ApplicationDate]","[qryAllCases]")) And Nz([Forms]![frmReportSelector]![OpenToDate],DMax("[ApplicationDate]","[qryAllCases]"))));

View 8 Replies View Related

Selecting Based On A Text Field And Most Recent Date

Jul 16, 2007

Hello, I'm working with SQL and databases in general for the first time, and was wondering: how would I select just the most recent entry for each device? my data looks [roughly] like this:

device1 data_1a 15.2.2000
device1 data_1b 15.2.2001
device1 data_1c 15.2.2002
device2 data_2a 15.2.2000
device3 data_3a 15.2.2001
device3 data_3b 15.2.2002

So what I'm looking for is:

device1 data_1c 15.2.2002
device2 data_2a 15.2.2000
device3 data_3b 15.2.2002

Thanks for any help you can offer!
-Eric

View 2 Replies View Related

Queries :: Find Data Only For 3 Products

Apr 16, 2015

I have been trying to get this to work for days and I cant get it to work correctly. I have a query that I need to search only for the records that have 12,22 and 24 so in the fields criteria I have been trying

Total: Where
Criteria: In (12,22,24)

and it works BUT it only works for the first record it finds then it seems to skip and continue to search for all records after that.

View 3 Replies View Related

Find Matching Data Between 2 Queries

Aug 23, 2012

I am managing a tool room and one of my tasks is to update a chit board where people sign out their tools. I have managed to make a query to find New Employees and one to find Terminated Employees. I have also made a query to find the matching employees between these two queries.

My problem is that we employ both contractors and direct employees. When a contractor is hired on directly, their name shows up in both the New list and the Terminated list.What I want to do is have a separate query that shows contractors who went direct and not have these employees show up in the other 2 queries.

Both queries are unmatched query that compare the "Current Chit Board" table to the "Weekly Roster Check" table. The first query called "New Employees" displays the data from the "Weekly Roster Check" table where Employee Number from "Current Chit Board" Is Null. The second query called "Terminated Employees" is the exact oposite. The third query I have is called "Contract to Direct" compares the "New Employees" query to the "Terminated Employees" Query with a "fields from both tables are equal" join type.The problem is when I try to do an unmatched between either the New or Terminated Emplyees queries and the Contract to Direct query, I get a Circular Reference warning and it doesn't execute.

View 3 Replies View Related

Queries :: Find A Way Of Overwriting Records In Table With New Data

Aug 6, 2014

I have a table where csv files get imported to on a daily basis. The key fields I am working with are a supply number and date. The problem I have is that sometimes the csv file will contain information that is correcting/updating information held in the table. This is creating duplicated records.I need to either create a query to find records that have the same supply number and date or find a way of overwriting the records in the table with the new data.

View 2 Replies View Related

Queries :: Find Specific Data In A Table To Produce A Report

May 14, 2013

I have a table that is linked into access 2003. This table is updated by personnel in another location and I have to run a weekly report on engines that are below a certain performance level.

The column heading is MGT Margin and I have to list all of the engines that are below 20 degrees.

Can I run a query that looks at this table and produces a report of all the engines that are below 20 degrees?

I currently have to cut and paste each engine from the updated spread sheet every week onto a separate spread sheet and import that into access. If a query can be used to do what I am after I can use similar principles in other reports I have to run.

View 5 Replies View Related

Queries :: Filtering Data Between Two Dates On Listbox

Feb 4, 2014

I've filtered a listbox query between two dates selected by a user through an unbound text, however the listbox only changes after you click it. I've requeried it after someone updates the 2 unbound date textboxes, yet it still doesn't work.

View 1 Replies View Related

Queries :: Totals Query - Return Data Set Between Two Dates

Jun 9, 2015

I am trying to create a Totals Query which returns a data set between two dates. So far I have managed to select the data I want (Please see attached screenshot). However, I only want to select records between a date range working on my field [DueDate]. If I add the due date field to the current query then it removes the grouping and all records are displayed.

View 7 Replies View Related

Queries :: How To Get Latest Dates And 3 Other Data For Records From A Table

May 29, 2015

Giving up after a zillion tries. I have a table (tblLOADS) containing: BROKER, PUDATE, MATERIAL & DRIVER. I am able to create this query:

SELECT tblLOADS.L_ID, tblLOADS.BROKER, Max(tblLOADS.Pudate) AS MaxOfPudate, tblLOADS.Material, tblLOADS.Driver
FROM tblLOADS
GROUP BY tblLOADS.L_ID, tblLOADS.BROKER, tblLOADS.Material, tblLOADS.Driver
HAVING (((Max(tblLOADS.Pudate))>0));

Problem: It's datasheet view displays all of the records for BROKERS, PUDATE, MATERIAL & DRIVER, instead of ONLY the records for the last PUDATE of each of the BROKERS, with their corresponding MATERIAL & DRIVER fields.

View 12 Replies View Related

Queries :: Query Filter Out Data From A Table Between Times On 2 Dates

Jul 24, 2014

Running Access 2010 and developed this query to filter out data from a table between times on 2 dates (day before report run and day of report). Covers data from a shift that carries over to the next day.Trouble is, the PC i developed on still operates the query as expected. However, on the PC the database resides (not networked just stored) and operates, the query brings up no data at all unless I remove the Time filtering.

This PC used to operate correctly up until early this year (about 18 months operated correctly) when the PC was replaced due to failure. Access version is the same and I am at wits end to what the cause is. Here is what my query looks like:

Quote:

SELECT Breakdowns.BreakdownDate, Breakdowns.Time, Breakdowns.Shift, Breakdowns.Downtime, Breakdowns.Equipment, Breakdowns.Conveyor, Breakdowns.Fault, Breakdowns.Stopper, Breakdowns.Gate, Breakdowns.Dolly, Breakdowns.Carrier, Breakdowns.FaultType, Breakdowns.Comments, Breakdowns.Tradesman
FROM Breakdowns
WHERE (((Breakdowns.BreakdownDate)=Date()) AND ((Breakdowns.Time) Between #00:00:00# And #6:29:00#) AND ((Breakdowns.Shift)="Night")) OR (((Breakdowns.BreakdownDate)=Date()-1) AND ((Breakdowns.Time) Between #22:30:00# And #23:59:00#) AND ((Breakdowns.Shift)="Night"));

View 14 Replies View Related







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