I have a query that is working, but I am looking to make it faster. We import 13 csv files daily into 13 Access tables. The files have an ItemID and price. We need to get the minimum of the raw data across the 13 tables & then manipulate the prices some (in the query) and get the lowest of the manipulated prices again for the 13 tables for each ItemID - some might be blank. We are using a MinOfList function in the query to get the minimum of the 13 numbers. But this is very time consuming since we have to run the Min function twice (with 13 input parameters) for each ItemID - about 15,000 records. It takes about 1 minute to run the query.
I am trying to set up a calculated field in one of my form querries, using expression builder.
In one of my source tables I have 4 date fields called Inspection date 1 to 4
I need the lowest date among those inspectinos which are in the future (next inspection) If all 4 dates are in the past or Nulls, the function should return the current date. The logic of the expression could be:
The built-in functions DMin and Dmax work with single field arrays, witch would be perfect if the database were properly designed, but now I dont have the power to change this.
The database I'm working on is used for personnel budget projections. Because some employees are hired mid-year, I need to be able to use various dates in my projection calculations.
I have 3 different tables - one with the employee start date, the other with the fiscal year start date, and the last with the start date of certain special pay tables. In order for my projections to work correctly, I'll need to return in a query the minimum of these 3 dates. I know how to do a minimum value in a single field within a table, but don't know how to select a minimum from multiple values in multiple tables. Is this possible.:rolleyes:
I have a table which has repeated IDs, I want to find the start of the ID then mark it as Y in the next field, the remaining repeated IDs should have blanks in the next field, how do I do this, thanks for any help
I have a counter on my form which will increase or decrease depending on weather a number of tick boxes report true or false. Is it possible to set a minimum aloud value on a box regardless of other criteria. At present I can depending on certail criteria, finish up with a - number which for other reasons, I dont want.
I have a main form and 5 tabs within that mainform that are subforms. They are linked by an EventID.
The subforms are continuous forms, but only one record is "required". What kind of code do I need to make sure that the data entry person enters at least one record in each subform.
Also, is there a way that when I am tabbing through my subform and get to the second record and leave it blank, that when I press tab it will take me to the first record of the next tab subform.
I have a field in a report that is calculated to generate a dollar amount. Well the people who need this have some strange rules about how they get their dollar amounts. Mostly they just round up to the nearest dollar, which is no problem. But they also want a minimum charge of $2.00. So my problem is if the calculation works out to be 0.01 to 1.00, it will say 1.00 and it is going to be wrong because they will want it to be 2.00. So is there a way to set a minimum value for this field, or where should I go to try this?
How do I create a query to return just one record, a minimum value for one of the fields, per group within a group that contains an ID field?
This is what my query is returning: ID, UPC, MinOfAverage Unit Cost 19161, 026851003663, $1.15 15927, 026851003663, $1.11 4459, 026851003663, $1.18 19598, 029695215513, $39.73 15993, 029695215513, $39.29 11694, 029695215513, $48.81
This is what I need: ID, UPC, MinOfAverage Unit Cost 15927, 026851003663, $1.11 15993, 029695215513, $39.29
I would be *extremely* grateful for an answer. I am new to this list, but I have plenty it MS Access help karma to my name, so I'm hoping to cash some of that that in.
What I am doing is trying to have access identify and label various lanes of transit.
So, for example. I have the below...
Origin Destination Via Transit Time Hong Kong New York East Coast 38 Hong Kong New York West Coast 32 China New York East Coast 34 China New York West Coast 29
Is there a way in Access to have it identify that Hong Kong to NY via West Coast is the "DEFAULT" where HKG to NY via East Coast would be "ALTERNATE" ?? And then do the same for the next origin? The table I have now has over 71 different origins but I need it look at each origin independently.
Does anyone have a good technique for enforcing a minimum number of records in a table?
I am attempting to ensure that in a table of Roles, there be a minimum of two Roles defined as Technicians. The field which identifies a Role as Technician is a Boolean.
I wrote a Function which does a domain lookup to count the number of Roles defined as Technician and am calling it from the BeforeUpdate event.
However, if there are 2 Technician Roles, and I mark a third Role as Technician, change my mind and try to unmark it...of course the Function can still see just the 2 existing.
I realize I can Undo the edit, but a user may not.
I´ve had this problem for a while but it only just recently became very annoying.
I have a chart on a form displaying data over time. I´m using two comboboxes to select start date and end date. There are A LOT of data in the underlying query. However the problem does not seem to be related to that.
When I select the starting date to be before a certain date, the chart suddenly sets its own minimum to 0 so that the line in the chart becomes almost flat (since the data reaches from maybe 20 to mayb 25).
This has to be a common problem, but is there a good solution?
I´ve checked the data so there´s no problem there.
I'm trying to get my query to display only the minimum value within my query results. I have the following fields, CustomerID, OrderID, ProductID, WarehouseID, Freight Cost.
My current query will return results that show me the locations and freight cost of each product to my customers. My intention is for the query to only return records that has the lowest freight cost as there may be several warehouses with identical products but with varying freight costs due to location to customer.
I've tried to apply the MIN function on Freight Cost but it will only sort the records in ascending order instead of only displaying the record with lowest frieght cost.
Hi. I have one table with dates (1) and object properties. Another table has dates (2) and other info. What I need is a query that selects the dates (1) closest to date (2) and lists the respective properties.
I figured all I needed to do is a WHERE MIN (dates(2)-dates(1)) query. The problem is that Access (2000) rejects this query b/c aggregate functions are not allowed in WHERE statements.
Does anyone have an idea of how to work around this? The structure of the data is such that there will only be one minimum date > 0.
I have a normalised table containing the following fields:
<Unique ID> <Fund Name> <Date> <Return>
The data are time series denoting the monthly performance of investment funds. Funds can have any number of observations (e.g. March 1997 to June 2005). In addition, some funds can have performance gaps.
I would like to extract those funds that have a continuous performance history of 120 consecutive months between a specific start and end date. As an exmaple, the start date is July 1990 and end date June 2010:
- Fund A: performance history July 1995 to June 2005 (= 120 consecutive observations) INCLUDE - Fund B: performance history July 1995 to June 2007 (> 120 consecutive observations) INCLUDE - Fund C: performance history July 1995 to June 2000 (< 120 consecutive observations) EXCLUDE - Fund D: performance history July 1995 to and June 2000 August 2000 to June 2007 (> 120 observations but gaps) EXCLUDE - Fund E: performance history July 1985 to June 1995 (= 120 consecutive observations but only 60 after start date) EXCLUDE
how to build a query around this? Perhaps this needs implementation in VBA. Ideally, I would like to be able to select the start and end date dynamically and then run the query accordingly.
How to find the Minimum value of a column. This is my SQL:
SELECT * FROM tblFinalMaster WHERE (((tblFinalMaster.OperatingDay)=[forms]![frmEnterParameters]![Combo13]) AND ((tblFinalMaster.[Checkout Order])=([Forms]![frm01ATFTieLineCheckout]![Checkout Order])+2) AND ((tblFinalMaster.[From BA_REPORT])=[forms]![frmEnterParameters]![Combo5]) AND ((tblFinalMaster.[TO BA_REPORT])=[Forms]![frmEnterParameters]![Combo3]));
The part in blue, instead of pulling a +2, I want the Minimum value. Seems simple enough.
I have six textboxes in my form. First 5 textboxes have dates ( MMDDYYYYY). In the sixth textbox I need to display the minimum date among these five dates.
I made a database that in one of the forms, I like by clicking on a button the user be able to select 5 excel files with different file names (in the same directory) and then based on the imported file's names, it be stored in 5 different tables.
At the moment by using the bellow code, I can import multiple files (with the same formats) only into one table . My vba code comes as follow:
Function GetAllFiles() Dim fd As Object Dim strFilter As String Dim lngItems As Long
Const msoFileDialogOpen As Long = 3 Const msoFileDialogViewDetails As Long = 2
I need to count records based on multiple criteria from two different tables. I have two tables (i.e. "tblTasks" and "tblTaskHistory"). The tables have a one-to-many relationship based on the "TaskID" field. "tblTasks" has a field called "AssignedTo" and "tblTaskHistory" has a field called "TaskStatus". I need to know how many tasks have been "reopened", the "reopened" status is located in the "TaskStatus" field in "tblTaskHistory". I need this count against a unique listing of employees which can be found in the "AssignedTo" field in "tblTasks".
I would like to know which way is the best way to import excel data from multiple sheets in to multiple tables in access.
For example data from Sheet1 -> Table1, Sheet2->Table2, Sheet3->Table3 etc...
I have tried using this: Cmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel5, "Table1", "C:Importedfile.xlsx", True, "Sheet1!"
Ironically, data from Sheet2 and Sheet3 seem to be properly imported in to table2 and table3, but some of the data from Sheet1 seems to be missing in Table1 after import.
A small issue I was wondering of for a few day . Is it possible in SQL query to SELECT multiple fields from multiple tables ? Example for the question is
Code:
dim my_var as String my_var = "SELECT Emp_FName , Emp_LName , Emp_Adress " _ & " FROM Table1 " _ & " AND Emp_Date_Of_Payment , Emp_Sum_Of_Payment " _ & "FROM Table2 " _ & " WHERE Emp_ID = 3 "
Is this code actually valid in SQL gramatics , and is it usable if passed to a Recordset variable ( rs = CurrentDB.OpenRecordset(my_var) ) ? Just FYI - The two tables are not related and I want to keep them that way (If possible relate their records just via SQL/Vba )
I have 10 tables, 30+ fields on each table (every table has the same 'account number' field). I only need from 5 - 20 fields from each table. How do I get the certain fields from each table and put them in a table, query or report?
