Find Minimum Of Dates From Different Tables?
May 1, 2006
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:
View Replies
ADVERTISEMENT
Jan 16, 2014
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.
View 5 Replies
View Related
Nov 2, 2006
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.
Looking forward to your ideas. THANKS !!
View 2 Replies
View Related
Apr 30, 2012
I have a table tROE with a field listing all dates starting 1/1/10 to date (populated), and three fields for currency exchange rates [USD], [RSD] & [EUR] which are empty and need to be populated. I have another table tROEPartial that has the exchange rates for some of the dates starting 1/1/10 but not all. Their structures are identical. I want to add the exchange rates from tROEPartial to tROE where the dates match, leaving the unmatched fields in tROE blank.
View 3 Replies
View Related
May 17, 2013
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.
View 2 Replies
View Related
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
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
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 14 Replies
View Related
Jul 8, 2014
I have two tables with dates. Between (!) every two following dates in table1, I want to know the number of dates in table2. How do I write an SQL query for this? The tables I have are up to a few hundred records in table 1 and a few thousand records in table2. So to prevent that this takes hours I need a fast query.
To explain the query I need, for example:
table1
01/01/2014
15/01/2014
17/01/2014
30/01/2014
table2
01/01/2014
02/01/2014
05/01/2014
17/01/2014
18/01/2014
20/01/2014
21/01/2014
25/01/2014
So the answer of the query would be 2,0,4.
Explanation:
Between 01/01/2014 and 15/01/2014 in table 1 there are 2 dates in table2 (01/01/2014 is not included between the dates)
Between 15/01/2014 and 17/01/2014 in table 1 there are 0 dates in table 2
Between 17/01/2014 and 30/01/2014 in table 1 there are 4 dates in table 2
View 2 Replies
View Related
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
Nov 8, 2012
I have just started using Access 2010. I need to calculate student's ages from their date of birth to today's date. I have a column set up for the date of birth but can't figure out how to get this figure.
View 2 Replies
View Related
Feb 12, 2008
Hi this is my first post... so hi all :)
ok what i have is a table with contact details 900k plus
there are about 90k of which are duplicates.
this is the basic feilds that are important in this case.
Id, data_source, data_recived, data_code,
what i want is to have a table with unique records (no dups in data_code)
this table will look like this...
Id, data_code, Num_dups, dup1_source, dup1_date, daysbtw_Dup1_dup2, dup2_source, dup2_date, daysbtw_Dup2_dup3 ,dup3_source, dup3_date, daysbtw_Dup3_dup4 ,dup4_source, dup4_date,
I know there is no more than 4 dups of each record.
what i want from this is a table that will give me a record of how many dups for each record then all the dates that they were added and the date between each record entry.
if anyone can help it would be great .
thanks in advance.
View 6 Replies
View Related
Jan 28, 2005
Hello,
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
View 3 Replies
View Related
Aug 9, 2007
hi
i try to build an access form .
i have "big" table that fill in the name of the product , the quantity , and a minimum quantity.
the field : min quantity give us the quantity that is the minimum of the product.
i have a "minimum" table that has the list of the product and the minimum quantity.
i want to see the minimum quantity of a product , when i type the product (in the form if it possiable , if not - in a report)
eran
View 2 Replies
View Related
Jul 8, 2005
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.
View 14 Replies
View Related
Mar 6, 2006
Hi All,
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 appreciate your responses.
View 2 Replies
View Related
Nov 19, 2012
I am building a database and am trying to automate month names and years to appear in my table upon adding a new record. I have the code built to input the data I am calling from other tables, and have functioned it to work on a button click. Now I want the record to capture the current date and year everytime a new record is added and input that information into the respective fields.
View 3 Replies
View Related
Jul 31, 2007
How would one go about comparing 2 tables and finding those records that are not common to both tables? For instance, I have a table that I suspect some records have been accidently deleted by the user. I would like to compare that table to an older version of the table, and list only those records that exist only in the older version of the table.
Thanks.
View 2 Replies
View Related
May 10, 2005
Ok this might be a really stupid question, but in the quest to always make my life a little bit easier thought i would ask !
I have been asked to find the relationship between a number of tables and queries, so that this company can find out which tables and fields in tables are no longer really used, in other words how many queries and which ones is table A used in if you understand what I mean !
doing it manually means checking every query (and there are a lot !!) and seeing which table they use.. what I want to know is what is the quickest way to to this? can I do it from the table end? in others find out how many queries Table A is used in ? or do I have to go through each query individually? if this is the case my thoughts were just on copying and pasting the sql view from every query into a a word or txt doc and then doing a search /find for each table name? but if anyone has a more efficient idea on how to do this if u cld let me know !! :confused:
View 2 Replies
View Related
Jul 3, 2006
i want to find out all the tables that contain a particular field. eg.employee_ID exists in employee table and other tables, how i can find out what are the other tables? can i run a search query to do this?
View 2 Replies
View Related
Mar 7, 2007
This may have already been addressed, but I can't even think of how to word it to do a good search.
I have one table, tblInvoices. I've done a query to search for "Micro*" in my [supplier] field, along with [Item].
I then need to pull from the same tblInvoices, any other records that contain the same [Item] as one that was queried in qryMicro.
I've tried to make a relationship between tblInvoices and qryMicro with [Item] but am getting too many records. I don't want to view records from qryMicro as I'm going to do a Union query to merge the two queries. I've tried
Field: Item Criteria tblInvoices.Item = qryMicro.Item w/o a relationship between the two but it's no better.
Any ideas as to how I can accomplish this?
Thanks,
Toni
View 1 Replies
View Related
Dec 18, 2006
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?
View 4 Replies
View Related
Aug 18, 2005
Hi
I have a table (tbl1) with three fields/columns (Primary key, NameID, Number). E.g.:
NameID ... Number
Mike ........ 2
Mike ........ 3
Mike ........ 19
Ronald ...... 1
Ronald ...... 2
Greg ........ 7
...
I would like to make a query that lists each NameID only ONCE, with corresponding MINIMUN Number. So the result from the upper example would be:
NameID ... Number
Mike ......... 2
Ronald ....... 1
Greg ........ 7
How do I do it???
Thank you in advance
m.
View 8 Replies
View Related
Oct 26, 2005
I wondered if anyone new how to return the following
Table of Data
ID|ID2|Date
----------------
1 | 1 | 20/01/05
2 | 1 | 21/01/05
3 | 1 | 19/01/05
4 | 2 | 20/01/05
5 | 2 | 25/01/05
I want to return the minimum date grouped by ID2, but with the corresponding ID i.e.
ID|ID2|Date
----------------
3 | 1 | 19/01/05
4 | 2 | 20/01/05
cheers for your help...
View 4 Replies
View Related
Sep 7, 2007
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.
Fingers crossed.
-Tom
View 3 Replies
View Related
Oct 29, 2012
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.
View 2 Replies
View Related
