Removing Rows From A Query Based On Date?
May 11, 2015
I have a query that produces a set of rows that contain product IDs and dates (sent). What I want to do is filter out rows that share IDs with another row such that I keep only one row which has the most recent date. As an example my query might contain the following two rows:
DATE ID
01/01/2015 1
02/01/2015 1
What I want to do is filter my table and under the condition that ID #1 appears more than once only keep the row with the most recent date.
I'm unsure what the easiest way to do this would be (or how to do it at all).
View Replies
ADVERTISEMENT
May 23, 2005
Hi everyone, I am hoping to learn something new about access today. What I need is to be able to run a query that gives me rows of data based on paramenters that I specify. For example, I will query this all just on one table and I want to come up with an output that is in a nice format as follows:
Parameter..........................Member Count
01/01/04 thru 01/20/04...............298
02/01/04 thru 02/20/04...............287
03/01/04 thru 03/20/04...............301
04/01/04 thru 04/20/04...............254
And so on, usually for a calendar year.
Basically each member has an effective date, like member A might have 01/15/04 effective date and member B might have 01/02/04 effective date. I need to capture the count of members by month based on their effective date range. I would like to do it all in one query like above instead of just running a query for January, then February, then March, so on.
Is there a way to do this? Right now I am just running a query and getting one member count at a time and copying that number to an excel spreadsheet. it works okay, but is time consuming. I would rather semi-automate things for me. Any ideas?
Oh and I am new to access, by no means an expert, so be gentle with me.
Thanks,
Hanna
View 2 Replies
View Related
Jun 13, 2007
Hi All
Can anyone help me with this problem?
I have an 11 million row table that lists relationships between pairs of people. There are several columns, but the relevant ones for my problem are
personA, personB, a-b_strength, b-a_strength.
because of the size of the table i'm having all sorts of problems, and i could actually cut out half of the rows, because for every row listing eg
dave, steve, 4, 5
there's another row listing the same information, but flipped round, ie
steve, dave, 5, 4
I'm sure there must be an efficient way to delete, using a query or otherwise, one of each of these pairs (it doesn't matter which). I have another column (id) that simply has a unique number for every row, ie 1,2,3->11 million, so i was thinking of something like
delete
from table t1
where exists
(select *
from table t2
where t1.personA = t2.personB
and t1.personB = t2.personA
and t1.id < t2.id)
So, this should delete every row that has a mirror version earlier in the list.(i think)
What i'm worried about though, is performance: is this going to make a new temporary table for the nested select for each new row in the outer statement? I already created a table that listed the count of friends for each unique person with a make table query, and that took 12 hours to complete. I don't mind another 12 hour query, but not a 144 hour query!
Any advice welcome - thanks! (Sorry for the longwinded post)
View 4 Replies
View Related
Sep 19, 2013
I have an excel sheet that I want to import, it has 3 rows at the top which are titles that I don't want. Can I miss these rows out using the standard import function or do I need to write a custom VBscript ?
View 3 Replies
View Related
Apr 22, 2014
I have a query based on 2 tables, joined on Memberid, the result showing :
Table1 Table1 Table1 Table2 Table 2 Table2
Category Association Memberid CustomerName E-mailAddress MemberID
Board Member(Lookup,integer) SAMGA(Lookup,integer) 44 Smith smith@abc.co.za 44
Board Member ADHTY 44 Smith smith@abc.co.za 44
Grower SAMGA 44 Smith smith@abc.co.za 44
I only want to show 1 row, based on the duplication of E-mail address. I know i should be using the row_number function, but cannot get to the result I want.
View 4 Replies
View Related
May 20, 2005
I have a front end that is connected to three back end files. The front end is on my local computer while the back end files are on a network drive.
There are a lot of calculations that go into the queries and intermediate queries. For a report, I have based it on a UNION query.
But when trying to design the report it takes about 45 seconds just to do any one thing, e.g.;
- Add Groupings
- Add Grouping Headers//Footer, sorting option
- Add bound textbox
:eek:
Needless to say this is very annoying.
:mad:
The union query itself runs fine (takes about 15 seconds to run) and returns about 12,000 Rows. The union query looks like this (I changed the field names to make it read easier, hopefully);
SELECT a1, a2, a3, a4, a5
FROM qry_A;
UNION SELECT ALL a1, b2 AS a2, b3 AS a3, a4, a5
FROM qry_B;
UNION SELECT ALL a1, c2 AS a2, c3 AS a3, a4, a5
FROM qry_C;
UNION SELECT ALL a1, d2 AS a2, a3, a4, a5
FROM qry_D;
UNION SELECT ALL a1, e2 AS a2, a3, a4, a5
FROM qry_E;
UNION SELECT ALL a1, f2AS a2, tblG.f3 AS a3, tblG.f4 AS a4, a5
FROM qry_F;
One solution I came across when searching the forums was to use an Append Query to append the query results to a table and base my report on that. This does indeed fix the problem.
But what I was wondering if it was is my query design that is causing it to be slow or is it just the fact that I am returning 12,000 rows?
:confused:
In case it matters, I wanted to mention that I can’t use the report wizard to create the report. When I select the union query, the fields will be showed for awhile then they just disappear. That in and of itself doesn’t cause any trouble since I am creating the report using the design view and not the wizard.
View 7 Replies
View Related
Apr 3, 2013
I have a dataset of about 70 columns but would like to remove any two rows that sum to zero (based on two columns) if the column "Units" are the same for the same "Bar code" thus to say "for the same 'Units' (for any two lines), check to see if their respective 'Bar Codes’ are the same and if so remove if their amounts sums to zero".
I have the following code which seems to be working but for relatively large data ( about 5k), it does not select all the data that meets the criteria:
Code:
SELECT *
FROM datatable
WHERE ((([datatable].[Unit] & [datatable].[Bar Code] & Abs([datatable].[Amount]))
In (SELECT datatable.Unit&datatable.[Bar Code]&abs(datatable.Amount)
FROM datatable
GROUP BY datatable.Unit&datatable.[Bar Code]&abs(datatable.Amount)
HAVING count(*)>= 2 and sum(datatable.Amount)=0)))
ORDER BY [datatable].Unit & [datatable].[Bar Code] & Abs([datatable].Amount);
identifying the bug in this code that makes it not pull all the data meeting the criteria.
See sample data below:
# Unit bar Code Amount
1. AAB Mac1 2.75
2. AAB Mac1 -2.75
3. AAB Mac1 1.24
4. AAC Mac3 35.00
5. AAC Mac3 -20.00
6. AAC Mac3 20.00
7. AAD Mac3 16.11
8. AAC Mac2 11.00
9. AAC Mac2 -11.00
10 AAC Mac3 12.05
11 AAF Mac3 -12.05
12 AAD Mac3 -16.11
13 AAC Mac4 35.00
I want to select records # 1, 2, 5, 6,7,8,9 and 12 since they are same units having the same bar code and their sums total zero.
View 6 Replies
View Related
Jul 16, 2014
On my report, I want rows with expired dates to stand out by having their borders thicker. Something like:
Code:
If Me.txtDate < Date() Then
Me.txtDate.BorderWidth = 6
End If
When I do that, it ends up formatting every txtDate field in the report if one of them passes the If criteria instead of only ones that are expired.
View 7 Replies
View Related
May 8, 2007
Hi all,
Very very new to Access, moved department a week ago and inherited a couple of databases that need tweaking. Although I'm learning I am stuck by something very simple and can't find it by searching this forum or Google :)
I have a query that originates from 2 tables, there is one custom record made from 2 of the fields -
Free Stock: [stk_stkqty]-[stk_allstk]
Now I want a record that shows -
Product where the free stock = backorder stock
so theroetically my guess as a beginner is -
SELECT * FROM [Product] WHERE [Free Stock] <> [Back Order]
But it fails miserably on syntax. Can someone point me in the right direction, and I apologise for my lack of understanding in advance.
Boofuls :)
View 5 Replies
View Related
Mar 30, 2006
:confused: i am trying to develop a system which requires a date order to be used .. i want to use a filter query to filter out certain information from my tables into a form .. i know how to accomplish this but i am having a small problem getting there
i wish to set sunday to an order of one (1) and monday to two (2) and so on so that when i create my query i will be able to select the thursday for example ( by setting the criteria to a "5" )
i have a table consisting of the dates but i am unsure how to create a field that displays the order .. automatically calculating whether the date is a sunday or a monday and assigning the correct number as stated above, i have tried something (not sure what) which assigned a 1 to monday through 7 to sunday but this is not the order i wish to use and it failed to work with my query anyway
would this create this using my computers clock or something and if so what do i need to do to get this small part of my system up and running therefore enabling me to create my filter query
please help
thankyou :)
View 3 Replies
View Related
Apr 3, 2007
I am very new to access so thank you in advance for your help and patience.
I have creadted a database for tracking students.
I would like to give them progress reports every three month based on thier start date (I have a 'short date' field for the start date).
If I run the query in January I would like results for all student who started in Jan, April, July & Aug.
Thank you again!
View 6 Replies
View Related
Nov 23, 2005
I have an address database where people have been allowed to type in any characters they want in place of just leaving a missing field blank. For instance the Zip code was not a required field (don't as me why because I don't know. It just wasn't) so over the years when the person entering the data didn't know what the zip code was they would put a ?, a 0, a 000 or a period or what ever else they could think of at the time.
I would like to add a filter in my query where I could eliminate any thing with less then a certain amount of characters.
I thought something like this would do the trick
Zip: IIf(Len([V_ZIP]<5),[V_ZIP],"0")
But I am getting a data type mismatch in criteria expression. Any ideas of what I am doing wrong?
View 4 Replies
View Related
Sep 1, 2006
Apologies if this is a basic question. Here's my situation:
I'm trying to update rows in a table based on a count of items in the same table. The table in question contains order line items. On each line item, I'd like to store the total number of items attached to that order (because it affects how the individual line items are processed).
I have a query that seems like it should do the trick, but Access doesn't like it:
UPDATE sales AS S1 SET S1.EXPC = (select count(*) from sales S2 where S2.order_id = S1.order_id AND S2.product_code = "EXPC");
Here's the error I get:
Operation must use an updateable query.
Thoughts?
View 5 Replies
View Related
Aug 21, 2013
I have generated a report with my entire list of personnel, and I have created a column that generates thier age based of another column using this:
=DateDiff("yyyy",[Birthday],Now())+Int(Format(Now(),"mmdd")<Format([Birthday],"mmdd"))
Now my question, im trying to hide the rows of those who age is older than 26. If thats not possible maybe highlight the entire rows of those under 26, i used conditional formatting to highlight the age, but not the entire row.
View 1 Replies
View Related
Nov 27, 2012
i have been trying to combine data from 2 different sources, to make unique rows: here it is my situation Data in DGV already in dgv, Unbound:
column1 | Qty
"Sugar", 100
"Salt", 100
"Color", 200
"Malto", 150
Data in datatable:
column1 | Qty
"Sugar", 80
"Salt", 60
"Apio", 25
"Lemon", 60
"Color", 60
So i want a control that matches the column1 in datatable against DGV.Column1, and if value matches only add second column value and if not, then make a new row in DGV.
Final decided outcome:
column1 | Qty|Qty2
"Sugar"| 100 | 80
"Salt" | 100 | 60
"Color"| 200 | 60
"Malto"| 150
"Apio" | | 25
View 1 Replies
View Related
Oct 2, 2006
I have a database with the following information:
FTA # Date Attended Last name First name assigned person......
Each week I need to generate a report that shows the people that attended for that week, grouped by the assigned person. I only want to show the data for a specific "date attended" (i.e. that days date). I figured I first need to generate a query that only returns the data for that specific date in the "date attended" column. I did that but it is manuel by using the criteria field.
Is there an easy way to type in the date that I want the query to use as the criteria?
Thanks
:o
View 14 Replies
View Related
Apr 23, 2007
I have the following tables: Rates and SelectedDates, which are structured as follows.
Rates: RateID, EffectiveDate, Rate
SelectedDates: Date. Table is bound to a combobox in which user selects one or more dates, mm/dd/yyyy, which is the same format as Rates.EffectiveDate.
I need a query to return the first Rates.Rate for the following conditions. (there will be a minimum of one record in the Rates table)
IF there is only one Rates.EffectiveDate for the SelectedDates.Date
return the associated Rates.Rate
ELSE IF count(Rates.EffectiveDate <= SelectedDates.Date)>=1
return the first Rates.Rate where Rates.EffectiveDate <= SelectedDates.Date
ELSE
return Rates.Rate associated to the Rates.EffectiveDate closest to the SelectedDates.Date.
I hope this is clear, as it is late and I am tired...:confused:
Thanks in advance,
Rod
View 1 Replies
View Related
Apr 21, 2008
Below is the SQL I have on a Union Query. Each Query is
based on a date range. The first Query date is Planned
Immplementation Date. The second Query date is Revised
Planned Implementation Date. If the Date in the Revised
Planned Implementation Date is higher than the EndDateTxt
Date Range I do not want the results to appear for that
ECN. Any suggestions on how to accomplish this?
SELECT ECNBCNVIPtbl.[ECN Analyst], ECNBCNVIPtbl.[ECN Number], ECNDetailtbl.[ECN Description], ECNDetailtbl.[Planned Implementation Date], ECNDetailtbl.[Revised Planned Implementation Date], ECNBCNVIPtbl.[Serial Number Break Required?], ECNBCNVIPtbl.[Implementation Reporting Required?], ECNBCNVIPtbl.[Do Not Process]
FROM ECNBCNVIPtbl INNER JOIN ECNDetailtbl ON ECNBCNVIPtbl.[ECNBCNVIP ID] = ECNDetailtbl.[ECNBCNVIP ID]
WHERE (((ECNBCNVIPtbl.[ECN Number])<>"sample") AND ((ECNDetailtbl.[Planned Implementation Date]) Between [Forms]![EcnVisualStatusFRM]![StartDateTxt] And [Forms]![EcnVisualStatusFRM]![EndDateTxt]) AND ((ECNBCNVIPtbl.[Do Not Process])="yes"))
ORDER BY ECNBCNVIPtbl.[ECN Analyst], ECNBCNVIPtbl.[ECN Number]
UNION SELECT ECNBCNVIPtbl.[ECN Analyst], ECNBCNVIPtbl.[ECN Number], ECNDetailtbl.[ECN Description], ECNDetailtbl.[Planned Implementation Date], ECNDetailtbl.[Revised Planned Implementation Date], ECNBCNVIPtbl.[Serial Number Break Required?], ECNBCNVIPtbl.[Implementation Reporting Required?], ECNBCNVIPtbl.[Do Not Process]
FROM ECNBCNVIPtbl INNER JOIN ECNDetailtbl ON ECNBCNVIPtbl.[ECNBCNVIP ID] = ECNDetailtbl.[ECNBCNVIP ID]
WHERE (((ECNBCNVIPtbl.[ECN Number])<>"sample") AND ((ECNDetailtbl.[Revised Planned Implementation Date]) Between [Forms]![EcnVisualStatusFRM]![StartDateTxt] And [Forms]![EcnVisualStatusFRM]![EndDateTxt]) AND ((ECNBCNVIPtbl.[Do Not Process])="yes"))
ORDER BY ECNBCNVIPtbl.[ECN Analyst], ECNBCNVIPtbl.[ECN Number];
View 5 Replies
View Related
Jul 24, 2012
I have an infopath form, which people in my company can fill out to order parts. The form is linked to an Access database. The primary keys for the database are the Order Number and the Line Number (where the specific part info is in the system). The form also takes info about who entered the order, when they entered it, etc.
My problem is that while the form will be used for only one order, it may be used to order multiple parts. So the infopath form has a repeating section with a table where they can enter multiple line numbers and part names, etc.If I use the form to enter just one part, everything works great. But when I use the repeating section to add multiple parts, the information specific to the repeating section is added to the database but the rest of the information (who entered, when the entered, etc) isn't.
My intial idea was that since the first part is entered correctly and the subsequent parts are missing some information which is already in another line in the database. Maybe if there was a way to pull the information from another line into the lines with missing info within access would solve my problem. So basically automatically populating the rows with matching Order Numbers with the information that is missing.
View 7 Replies
View Related
Jul 30, 2013
I have a big Excel file with payroll information about employees. Per employee per date, there are a couple of rows with mostly identical data (such as the employee's home address) but 2 differences: one will have paycode A with amount B, another will have paycode C with amount D, etc. I want to simplify the file to have 1 row per employee per date: in the example given that row would have paycode A with amount B as well as paycode C with amount D, in 4 separate columns.
I think I can technically solve this by creating Excel files per pay code/amount, and then linking them together by person and date, 1 by 1, through Access. This is feasible because there aren't that many different pay codes. But still, I was wondering if there was anyway to solve my problem in Access itself.
View 1 Replies
View Related
Feb 4, 2008
I have form with two date text boxes. One is start date and the other is end date. I want to query a table and only pull records that the date field is between the start date and end date.
my code I am working on is:
Dim startdate2 As String
Dim enddate2 As String
Me.start_date = startdate2
Me.end_date = enddate2
Dim SQL As String
SQL = SELECT FROM 'project' WHERE Date between 'startdate2' and 'enddate2'
DoCmd.RunSQL SQL
I am getting a error on the sql = statement, I can't seem to get the statement right.
View 4 Replies
View Related
Dec 18, 2014
I am trying to run query on a months worth of dates, have it count based on each day and then display the date and the number?
Table:
field1 - field2 - field 3 - Date
I can run a query one day at a time but would like to run it for the month and get this
12/01/2014 - 15
12/02/2014 - 32
12/03/2014 - 0
12/04/2014 - 12
View 6 Replies
View Related
May 16, 2013
Within a query, I'd like to reference another query field based on a date specified as a parameter.
In my query, there are fields for each month: [January],[February], etc.
I have a field titled [Current Month], based on the parameter [As Of Date]. So if when running the query, the parameter pops up and I type 5/6/2013, it knows that the month is May. I know how to return May in the current month field (format([As Of Date],"MMMM"). But how to I return the value that is in the May column?
View 3 Replies
View Related
Aug 24, 2015
i want to write a ms access query for date difference.i have the excel formula for that.in attachment i have shown wot i really want as output.i want to make a IIf query for these conditions.
in excel i did this like:
=IF(J13="",IF(INT(B13)=INT(AE13),C13-DAY(I13)+1,C13),IF(INT(AA13)=INT(B13),IF(MONTH(J13 )=MONTH(I13),J13-I13+1,DAY(J13)),IF(INT(AA13)=INT(D13),DAY(J13)-E13,0)))
how to do it in access query.
View 4 Replies
View Related
Jul 10, 2013
I have two tables. One table is a list of classes with the number of enrolled students:
Class............StEnrolled
English 1A........6
English 1B........12
English 1C........20
English 1D........25
Reading 1.........4
Reading 2.........15
And the other table is a list of "combined" classes (meaning they are, essentially, the same class and should be counted as such):
Class...........CombinedWith
English 1A.......English 1B
Reading 1........Reading 2
Using the first table, I currently generate a query (and build a report) that displays class enrollment levels. What I need is for this query to identify combined sections from the second table and display them as such:
Class........................StEnrolled
English 1A/English 1B........18
English 1C.......................20
English 1D.......................25
Reading 1/Reading 2..........19
View 7 Replies
View Related
Dec 19, 2011
I have an access DB.I have an XLSM Data Sheet.In the XLSM I have A few Rows with Fields.In the Fields I have a few matching fields as in the Access DB.I also have a few extra fields that i want to add to the Access DB
I want the new fields of the ADB to Populate from the XMLS in the proper rows based on the ID number in the ADB.
View 5 Replies
View Related
