I'm trying to create a query that will return the closest matches to what is input. However the closest i have gotten to this working correctly is by using the following criteria.
Like Left([Forms]![TESTTESTTEST]![Text2],1) & "*"
Now obviously this isn't a great solution as it just produces results that match the first number but i need it to display results closest to the number entered into text2.
Basically i am doing this as a designer can search for a gap on a machine where there new project can fit on causing the least amount of disruption. So by entering in the size of there project the query will display jobs of a similar size that are already planned on the machine.
I need to do a report that takes data from many different tables (like 10).
To do this I have a big query with many LEFT JOIN to link all the tables together. I have made this query last year and it worked well and efficiently until now.
But I have to add something there, and the problem comes with it :
One of the tables is a currency exchange rate table, with the exchange rate of USD to 50 different other currencies and for each day since Jan 1st 2011.
But in this table there are some dates when a currency does not have an exchange rate (example : it was a holiday in that country, so there is no official exchange rate with this currency on that day).
However, I need to have rates even if they are not present in the table.
I need to get the nearest rate, for example if the rate of EUR on 31st December is not available, I can take the one of the 30th, or 29th or January 1st...
Ideally I would like to do the following :
Add a new LEFT JOIN to my already big query that would look like :
LEFT JOIN CurrencyExchangeRate ON ABS(RateDate - Instrument.Date) < 3
But to take only the closest rate.
I tried with a subquery :
LEFT JOIN (SELECT TOP 1 * FROM CurrencyExchangeRate WHERE ForeignCurrency = Instrument.Currency1 AND ABS(RateDate - Instrument.Date) < 3) AS MRate ON MRate.ForeignCurrency = Instrument.Currency1)
But the subquery does not accept Instrument.Currency1 because it is not part of the table CurrencyExchangeRate
I have a solution that should work, it is to search for this rate in a subquery in the select clause instead of doing a left join. But the tables are quite big (hundreds of thousands of entries) and my work machine is not really 'fast' so it would take hours and hours (when I tried I stopped after 3 hours).
I know there is the solution to make a macro to "fill" the missing rates with the rate of the previous day... But I would prefer not to go to this extent.
I am increasing prices and after increasing I am left with 4 decimal places and am trying to figure out how to run an UPDATE query to round down prices to the nearest 5 cents, examples below:
42.4516 round to $42.45 42.4659 round to $42.45 42.4489 round to $42.40 2.49 round to $2.45 2.46 round to $2.45 2.44 round to $2.40 2.04 round to $2
I would like to add fields from one table to another based on matching datetime codes.
Most data are in the table 'metadata' (information on sound recordings), and i want to add lat long fields from a large table of GPS points 'gpsData'. Each record in the metadata table has start and end datetime fields - i want to a add lat and long that matches the start time and a lat and long that matches the end time (so four fields to metadata).
My approach has been to try this with two separate select queries (i.e. one for adding the start locations and one for adding the end locations), with the intention of then combining these two outputs in a single table.
When i try a simple select query though I can see that:
a) there are no matching time codes for some records, because although the hh:mm:ss look the same there are differences in the lesser decimal places of the datetime serial numbers. I have tried using the round function on the datetime fields of both tables but this doesn't fix it (I don't understand why not).
b) there are some duplicates because there are sometimes more than one GPS point for each second, and the metadata times are given to second precision (so some metadata records get two entries in the query result).
How can I have the query select the gpsData record with the nearest datetime stamp to that in the metadata table's start time field? The respective field names are UTC and StartUTC. Can i do the same for the end times simultaneously or should i do this separately?
I have a table of standard Circuit Breaker (CB) sizes. I then calculate a minimum CB size in a query field. I want to use that calculated minimum value to look up the next largest CB size from the table and fill a field in the query with it.
For now, I am going to add a field to the CB size table with the smallest size CB which would be assigned that standard value. I will then use a Dlookup with conditions of greater than "smallest size" and less than "standard size" fields from the CB size table.
I think this will work fine, but there must be a better way.
I am trying to round off times to the nearest half hour. To be clear, I don't want to only round down or only round up. I need the rounding to be to the nearest half hour. I want to do this in the query, not vba. I've attached a picture of the query.
I have a timesheet database, which has the exact start time and exact end time. I want to ignore this if it is five minutes either side of the half hour, and display the exact half hour, but otherwise want to round it UP to the nearest fifteen minutes.
I have a table that archives hour meter readings for my equipment. You sharp folks here helped me set it up and it works great. Thanks! I am at the point now where I want to do some studies and calculations on this data. The first project Im working on is equipment utilization over a period of time. I have all my calculations figured out, all I need is to pull the proper data out and run it through. And thats where Im a little stuck.
My table is just three fields: tblHourMeterArchive = eqnum, readingdate, meterreading. I want to be able to input a StartDate and EndDate and have the query choose the archive date that is nearest to the input date and the meter reading on those dates. For example, I have meterreading dates of 1/9/05, 1/30/05, 6/5/05, and 6/19/05. I input 1/18/05 as the StartDate and 6/16/05 for the EndDate, the query should return the archive dates of 1/9/05 and 6/19/05. How would I go about doing this?
Ive done some research on nearest date, but most of the information has been about doing it in VB and SQL. Can this be done without going into coding? Im still very much a novice when it comes to VB code. Ultimately, I would like to have this work with a form and calendar controls. Ive been studying some of the examples and suggestions for the calendars, but Im not really sure how I would go about setting up a query in code and linking it all together.
As always, any help and/or links to information you could provide would be greatly appreciated.
I have been searching the forum for the last hour or so. I'm new to access, vba and macros. So I asking for some help, please be gentle with me. I'm am trying to round up the the nearest twenty.
example. I have a table that contains an estimate take-off f and I want to round the pipe footage to the nearest 20 for purchase amounts and update the table.
I need to find the nearest dates between two tables.
Table1 contains dates 11/1/2006 to 11/30/2006
Table2 contains only two dates: 11/5/2006 11/19/2006
I want Query1 to produce the nearest date in Table2 beside each of the dates in Table 1. Example: Date Nearest Date 11/1/2006 11/5/2006 11/13/2006 11/19/2006 etc.
I have 3 queries named Mech Final Equipment 3 Mth, Mech Final Equipment 6 Mth, and Mech Historical Final Equipment.They all have two fields-Final equipment and Sum of Sum of Down (calculating the number of minutes each piece of equipment was down in the time period selected).
My ultimate goal is to join the three queries to display a pivot chart that uses the Final Equipment as the category field and 3 Mth, 6Mth, and Historical as seperate data fields.What I have is a join query (Which I have named Mech Final Equipment H63 Joined)
Using this SQL:
Code:
SELECT DISTINCTROW [Mech Final Equipment 3 Mth].[Final Equipment], Sum([Mech Final Equipment 3 Mth].[Sum Of Down]) AS Duration FROM [Mech Final Equipment 3 Mth] GROUP BY [Mech Final Equipment 3 Mth].[Final Equipment] UNION
[code]...
Which returns a table that looks like this:
Final Equipment, Duration
Ancillary Equipment, 225 Ancillary Equipment, 401 Ancillary Equipment, 1787 Brush Unit , 1252 Brush Unit , 2519 Brush Unit , 8004
And so on.What I need the table to look like is this
Final Equipment, 3 Mth, 6 Mth, Historical
Ancillary Equipment, 225, 401, 1787 Brush Unit, 1252, 2519, 8004
And so on, like a cross tab.I tried to do a crosstab query but I don't have enough fields.
Not sure what happened to my original post but here it goes again......I have a calculation that I need to perform in a query. Before I do the calculation however I need to round down numbers to the nearest hundred so a number like 1485.45 would be 1400, something like 543 would be 500 and something like 68 would be 0.In excel this is easy for me to do using the following formula/function:=ROUNDDOWN(C2,1-LEN(INT(C2)))Where cell C2 might contain something like 588 the net result is 500.How can I acheive this in Access (query)? There doesn't appear to be a rounddown function nor a len function in access.Any help is appreciated!M Opine
I am working on a report that has some special characteristics.
Let's say I have a list of groups of Vendors in a table, complete with VendorID. I have 3 other tables that use the VendorID: Complaints, Complements, and Terminations.
Each of these tables has a date that the Complaint, Complement, and Termination notice was received.
Every Fiscal Quarter, a report is pulled that looks back over the 4 preceding quarters to determine if a 5% threshold has been crossed by any of the vendor-groups in regards to the amount of Complaints they received.
The equation used for that is : (complaints/vendors_in_group)*100
It is imperative that the information has the current fiscal year and fiscal month (which I am tracking with functions from MS website), and I need to be able to store the information attached to the fiscal year and month.So when a user goes to the form and inputs the desired Fiscal Year and Fiscal Month, the database can display the 4 previous quarters of information...split into Q1, Q2, Q3, and Q4.
What I would like to have happen is to be able to have one table where the information is stored, quarterly, so that it can be retrieved for the report.
Questions: 1. Is it possible to have one line, per VendorID, that has the total number of Complements, Complaints, and Terminations, as well as the threshold percent stored in a table? Right now, I am getting LOTS of duplicates and blank lines when I try to put them all together. It has the right data, but takes about 10 rows per VendorID.
2. It is very important that the total number of Vendors in a group be captured on that quarterly report, so maintaining that number, in the same table, is essential and must be tied to the VendorID.
3. I have looked at Union Queries and Crosstabs, but I just dont know enough about them to make it work.
I am a beginner Access user. I’m trying to export the results from two queries "TestData" and "ReportDate" to a saved .xlsm file. These queries are generated from tables linked to a SQL server.
The first query “TestData” should be copied to a sheet named ”TestData” and the second query “ReportDate” should be copied to a sheet named “ReportDate” in the same Workbook ”Test.xlsm”.
Both queries have two parametesr passed from Form1 [Start Date] and [End Date].
Code: Public Sub ExportToExcel() 'Step 1: Declare your variables Dim XL As Excel.Application Dim wbTarget As Workbook Dim ReportDate As QueryDef Dim TestData As QueryDef
I have a field that sums pounds. I need to convert this to tons (rounded to the nearest 3 decimals), then multiplied by $67.50. This will calculate a fee payment.
This is what I have now:
Code: =Round(Sum([Hazardous_Waste]/2000*67.5),3)
The total pounds is 2675.
After dividing by 2000, Access generates a number of 1.3375. It rounds 1.3375 to 1.337 which generates an incorrect final total. Not sure how to alter this to round properly.
I have main form call frmemployee and subform call frmlcourse subform the main form hold's employee information e.g. first name addresss the sub form hold's all the training courses that the employee has been on
I have a text box called txtnextcourse due to display when the next course due date is
The query below is supposed to accept some characters from the user and bring up a list of records satisfying the criteria. TelephoneAAA is a table with many columns. The query does not give any error message receives input from user runs quietly and does not give any result. I know that the table contains sufficient data. Probably I am missing something obvious but what.
Code: RowSourceQy = " SELECT * from [TelephoneAAA] " & _ "WHERE (([TelephoneAAA].[SOYADI]) Like ((' * ')+ [Word] +(' * '))) OR " & _ "(([TelephoneAAA].[ADI]) Like ((' * ')+[Word]+(' * '))) OR " & _ "(([TelephoneAAA].[ADRES]) Like ((' * ')+[Word]+(' * ')))" & _ "ORDER BY [TelephoneAAA].[SOYADI], [TelephoneAAA].[ADI], [TelephoneAAA].[TEL];"
I am trying to use a combobox to select a value which then activates a query to return results.
Here is some details
I have to tables, one called "Customers" and the other called "Calls". These two tables are linked.
The customers is literally a list of customers with their contact details, but all I am in interested in is the "Company" Field.
The Calls table has a field called "End User" which looks up the company from the Customers Table.
What I am trying to do is create a search by Customer query, furthermore, I would like to do is to create a form with a drop down that looks up from Customers table, select the company and it returns all the records with that company...
What I have done
I have created a query that has the customer and calls tables included, I have dragged down the [Company] from customer table and then all the fields from the Calls table.
Then i created a blank form, inserted a combobox - Combo7, linked the box to the Customers table.
Back to the query, under the [Company] I have put into the criteria the following
Forms!sc!Combo7
Back to the form, selected the combo box, built a macro in the AfterUpdate, to run the query.
Tested this and it does not bring anything back, however if I put into the criteria Like [Please Enter Company Name], then typed the company name, it brings back all the records for that customer.
Am I missing something?, do I need to set the form control to the query, or even the combobox....
Every time I run a query that I have created it asks me to input Expr1 and Expr2 in an enter perameter value pop up box. I don't enter anything, just click okay and the query runs as expected.
Is there a way to get rid of these? (and maybe more importantly, why do they appear?)
Having built a database that tracks projects across their lives, I've now been asked it I can identify the latest status of the project in a "quick view"
The table "All Projects" has columns that list the Project Number; Project Title; Project Manager and further colunms that list dates that the various stages of the project were achieved. i.e. Start Date; Date Milestone 1 Completed; Date Milestone 2 completed etc.
The projects are such that Milestone 5 may be completed before Milestone 2 and so on, so I need to be able to identify which Milestone was the last to be completed.
I thought of trying an IIF but the progress across the Milestones isn't logical so it won't work. I found something on the Internet about "Greatest" being used on some other program (not Access), which Access didn't like at all!
If the requirement had been given before starting building the db, I would have done everything differently, but I have no real desire to go back to stage 1 and start again, so how or if it's possible to find the highest / latest date relevant to the Project Number across the row of data ?
