So I have another how do i get a form feild to automatically pickup the largest value. it is for an auto number and i want it to show only the last record that was created so it has to be set to show the largest number.
any help would be great.
Hi, newbie question.
I have an existing select Query, that has a field of dates. I want a new select query based on that source that shows all records except for the one with the largest value date. There will be no duplicate dates. I tried:
SELECT Q_Tasks_Accs.End AS NotLatestEnd
FROM Q_Tasks_Accs
WHERE (((Q_Tasks_Accs.End)<(Max([Q_Tasks_Accs].[End]))));
but when I run the query I get error message:
"Cannot have an aggregate function in WHERE clause(Q_Tasks_Accs.End)<(Max([Q_Tasks_Accs].[End]))"
I clicked on Help but did not understand what it said. So any help from here would be appreciated.
Thanks
I have a table of logged entries. Each record has a date field (ValueDate) and an account identifier field (AccountID)
I also have a table of rates. Each record has the same account identifier field (AccountID), a date field (EffectiveDate) and a rate field (BankRate)
Entries can be logged for any given ValueDate. But there may or may not be a corresponding EffectiveDate in the rates table.
I need to write a query that will return all of my logged entries and the largest EffectiveDate which is on or before the ValueDate (as well as the BankRate corresponding to that EffectiveDate)
This is as far as I've gotten but it returns multiple records for each logged entry. I need one record per logged entry.
Code:
SELECT tblLoggedEntries.EntryID, tblLoggedEntries.AccountID, tmp.BankRate, MAX(tmp.EffectiveDate) AS EffectiveDate
FROM tblLoggedEntries
LEFT JOIN
(SELECT tblRates.AccountID, tblRates.BankRate, tblRates.EffectiveDate
FROM tblRates) AS tmp ON tblLoggedEntries.AccountID = tmp.AccountID
WHERE tmp.EffectiveDate<=tblLoggedEntries.ValueDate
GROUP BY tblLoggedEntries.EntryID, tblLoggedEntries.AccountID, tmp.BankRate
I'm fairly new to access, but I thought this was simple!!! I want to querry a column/field in a database table called Quote # simply to return me the largest number in that column. I then want to use that number and add 1 to it each time I start a new record....
Any help appreciated
Hi, I'm trying to build an expression that will allow me to find the second largest (third largest, fourth largest, etc...) value for a given field. I'm tracking the sales of a company and the database has over 900 customers. I know using the Max fuction will get me the largest value, but how do I find the second largest value?
I've already tried this:
DMax (total_sales, customer_sales, Not Max (total_sales) )
where:
"total_sales" is the name of the field (column)
"customer_sales" is the name of my table
I'd appreciate any help. I know I got the idea right but I'm getting confused with the order of the functions.
Thanks!
Mike
I would like to find the largest value for each row in a query. I have a query with several fields, each field has an expression that produces an integer.
Max and DMax seem to pertain to the values in a single field; I need to evaluate values from multiple fields in a single row.
I keep seeing 'range' mentioned but I have yet to see any examples of evaluating a series of data like 'col1;col2;col3' or anything remotely similar.
I need to find best combination using Loop to count "NumerOfSheets" To achieve smallest possible number from among the largest. Taking into account additional blocks to allocate. My table before running code looks like
Code:
ID Oder Quantity Blocks NumberOfSheets
1 A 350 2
2 B 200 1
3 C 100 1
At the beginning I was using code (I had no additional blocks):
Code:
Dim recIn As Recordset
Dim strSQL As String
strSQL = "SELECT * FROM tbl1;"
Set recIn = CurrentDb.OpenRecordset(strSQL)
While Not recIn.EOF
[Code] ....
It worked! But now I have new field in my main form "Forms!frmGlowny!FreeBlocks" Where I keep number of blocks to allocate (additional blocks which I can allocate in the column "Blocks"). This filed is count by another code. What is important now, this is positive integer (usually no more than 20). I need find best way to allocate my free blocks. What is best way? - The largest number from "NumerOfSheets" should be as small as possible.
Suppose that this example Forms!frmGlowny!FreeBlocks = 1 (so it's very simple example). So Let's find where I should allocate my 1 free block (I need do it by hand, because I don't have a code:/).
Combination 1
Code:
ID Oder Quantity Blocks NumberOfSheets
1 A 350 3 117
2 B 200 1 200
3 C 100 1 100
Combination 2
Code:
ID Oder Quantity Blocks NumberOfSheets
1 A 350 2 175
2 B 200 2 100
3 C 100 1 100
Combination 3
Code:
ID Oder Quantity Blocks NumberOfSheets
1 A 350 2 175
2 B 200 1 200
3 C 100 2 50
The smallest possible number from among the largest is in the combination No. 2 (because the largest = 175 so it is smallest from all largest numbers of combinations), so now I know that my 1 free block should be added to B order to column "Block". It's very simple example because I have only A;B;C oders and 1 block to allocate. But When I will have e.g orders: A;B;C;D;E;F;G;H and 14 blocks to allocate count by hand will be terrible...
I'm trying to create a query that will compare the data in 3 fields in a record, choose the largest (I also have a criteria to order by if more than 1 field has the same entry and it's the largest of the 3), and then group by that.The fields I will need are as follows:
PRODUCT table:
ProductName
Chemical
ChemicalAbstract
PhysicalState
NFPAHealth
NFPAFlammability
NFPAReactivity
qryQuantityOnHand query (which doesn't link directly to the PRODUCT table, it links through associations with other tables):QOH...I will eventually need information from another table for the final reports, but I don't think it has to be included in this query.
The fields NFPAHealth, NFPAFlammability, and NFPAReactivity each may be 0, 1, 2, 3, or 4...I need to ignore blanks; if 1 of the above fields is blank, they will all be blank.For any record, I need to compare the number in those 3 fields to each other, and choose the largest number and group by that rating.
In other words, if the largest of the 3 numbers is a 3 in the NFPAFlammability field, all those products need to be grouped together.If the same number appears in at least 2 of the fields, the order that determines the grouping is: Flammability, then Health, then Reactivity..Ultimately the report will be grouped as follows:
Flammability
Rating 4
Product 1
Product 2
Product 3
Rating 3
Product 1
Product 2
Product 3
Rating 2
Product 1
Product 2
Product 3
Health
Rating 4
Product 1
Product 2
Product 3
[code]....
and each of the groups will be sub-totalled.I'm stumped at trying to create the query in the first place.The added aggravation here is that we are dealing with 23 stores, each with their own mix of products. I have another table that contains the information about which products are in which store.
PLEASE HELP!
Hello:
I have an issue I've run into and have searched around and tried to resolve. The issue is that I have a table that is updated once a week with new data, calculations are performed on new fields, etc...Ok, no problem on that. Oh, to give you a quick overview, the table is for Open and Received orders. Ok, the new field is Actual Received Date. Well, if open, obviously it's not received. If received, it's placed in that field, if Open, that field is blank for that record. I need to create a SELECT QUERY, would save TONS of time here if possible, to do an IF THEN ELSE type aspect that basically, if Actual Receive Date is present, then use this calc, if not, use that calc, etc...ANY IDEAS? Take a look below for additional information. The following are ONLY EXAMPLES to give you a better understanding of the issue.
1 Table, 1 Query, 2 Fields, IF THEN ELSE EXPRESSION within SELECT QUERY under 1 Field.
Field name in query = "DAYS LATE"
ISSUE: (in vb view)
Dim Value1 as String
Dim Value2 as String
Value1 = Date() - [mDueDate]
Value2 = [mActReceiveDate] - [mDueDate]
If IsNull([Tables]![tblOrdersMain]![mActReceiveDate].value) = True Then
[Query]![Query]![DAYS LATE].value = Value1
Else
[Query]![Query]![DAYS LATE].value = Value2
End If
Well, definitely need HELP! Any questions or anything please let me know.
Thanks!
I have joined 3 tables using the query design and I have 3 different fileds. There are fields that have information that I don't need. e.g. I don't want the first 10 characters of field 1. How do I do this in query design (even in SQL view). Or do I need to do this in the table itself before joining the table. Hope my question makes sense. Thanks for any input.
View 1 Replies View RelatedIs it possible to have a specific tab in a tab control selected automatically based on the value of a combo box? Not by what is selected, but by what is already stored in the field.
For example, if have a combo box with a value "shirts," the "shirt types" tab is automatically displayed in the tab control box.
I have a table with 84000 records in I need to fined all the records that have the same First two starting characters and the same last two characters but are different between
the first two and last two characters.
Ie. 30123456757 302356757 301234567
I just want the ones that have different number in between . I have tries ever Select statement I can think of and still coming up with every thing but what I need.
Hi!
I have a table called myTable and it contains two fields Year and Month. Both are type of integer.
Now in my select statement, I like to combine these two fields in to one and name it as period and it will have the format, for example 200501, 200502, ..., 200512. How can I do that in Access query statement?
I know how to do this in SQL Server 2000. That is:
Select Convert(varchar(4), [Year]) + right('00' + cast([Month] as varchar(2)), 2) as Period from myTable
How can I do that in Access Query statement?
Please advise.
Aijun.
I currently have a table showing activity for multiple staff and their availability throughout the day in 30 minutes segments.
I am currently trying to pull the information on who is working by 30 minute timeslice, but as the information is held in a different field for ech period, it is proving difficult.
My thought was to make a query rounding the current time to the nearest hour/half hour and use this to choose the field, but I don't know how to make a query which will allow me to pass a variable (Field name) into the Select query?
can anyone help on this, or have any other ideas?
Thanks
Andrew:
Hi,
I am trying to write a query that looks at a set of parts that are assigned to the same "Asset" number, and takes the sum of all prices for that collection of parts.
I have the query set up to do this without any problem. Where I am stuck is that in the table containing the details of parts, there are three possible prices. What I need it to do is take price c if there is no value in prices b or a, price b if there is no price in price a (otherwise take price a).
I have tried this using the criteria ="price a" = 0 AND "price b" = 0 (for price c)
and ="price a" = 0 (for price b criteria). When I run the query I get a "Data type mismatch in query expression" error.
Can anyone advise?
Thanks,
Ryan
Hello to everyone...
I have a calculated(unbound) text field named(Age) on my main form. The Age is calculated based on patient's DOB(Date of Birth) Field using datediff function, and in the subform, I have a field named (AgeNo).
There is a criteria for AgeNo Field depending on (Age) field for each patient.
Criteria:
If Age is 18-40 = AgeNo should be 0
If Age is 41-60 = AgeNo should be 1
If Age is 61-70 = AgeNo should be 2
If Age is >71 = AgeNo should be 3
MRN (Med. Rec. No) field & Age are in Main form and AgeNo field is in a subform.
I want the AgeNo field to automatically select ( 0, 1, 2 or 3 ) based on the calculated unbound text field(Age).
Many thanks... :)
Hi,
I want to have the entire field in a textbox selected, automatically, everytime the control is entered--- so the user can just begin typing and automatically type over the existing data in the textbox.
How do I do this?
I thought it would be as simple as adding something like the following to the textbox's on_enter event:
Sendkeys "doubleclick"
The textbox will always contain just a single "word", so this idea should work....but I can't find anything like a "doubleclick" that can be used with SendKeys.
THanks for any ideas.
Okay - have hunted the web for the answers to this query on and off for months. Thought I had something but it didn't work.
Problem - basic form with subform of a 'select * from table' dataset. So far I've added one textbox. I'd like this textbox to show the recordnumber selected in the subform or the KeyID of the record selected in the subform. This is so I can populate other textboxes with details of that record for editing and then control/validate data entry.
Have tried: form timer, form load and some version of seltop (not bothered about selheight at this stage) - sort of worked but not properly - at least it gave 'an' answer but not always the correct one and you had to click in and out of the subform to generate an answer.
Private Sub MySubform_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
Text4.Value = MySubform.Form.SelTop
End Sub
Private Sub Form_Load()
Me.TimerInterval = 10000
End Sub
Private Sub Form_Timer()
Text4.Value = MySubform.Form.SelTop
End Sub
I think the _MouseUp sub isn't working as its not a recognised event for a subform.
I started looking at various DisplayRowID, SelRecord functions (in a separate module) run from a command button in the form footer but that's not really the right sort of thing and the referencing to the subform is a bit iffy.
I'd be grateful if anyone could advise on, even complicated, code.
Thanks,
Lez
On an access 2010 form if there are 5 fields (Number,Address,Gender,Age,Date). Is it possible to use a button to export 3 fields (Number,Age,Date) to an excel spreadsheet (Master) and place them in a specific worksheet (Info)?
View 1 Replies View RelatedHi all,
I have a table which contains business details (name, address etc) and also a field for clients. I then have a table which contains client details. Is there anyway I can click a button on a form containing business details which brings up a form containing the clients which would allow me to click the clients I want to be inserted onto the clients field on the business details table?
Does anybody have an example of this.
Thanks for your help!
I've tried the following WHERE clause to return records where the 'costper' field is empty and neither return any results.
WHERE NZ([Transactions].[CostPer])=0
WHERE [Transactions].[CostPer]=0
WHERE IsNull([Transactions].[CostPer])
Hello,
I have a dataset that consists of Stands within a forest inventory. There are two Fields: the first field is StandValue (dollars), the other field is the StandArea( hectares).
I want to select the highest valued stands BUT the sum of the individual areas found in "standArea" cant exceed 10,000 hectares. Can anyone help me figure out an SQL query for this, or any other suggestions on how to proceed would be greatly appreciated, thanks
Greg
Hi
I need to select from a table field of upto 50 characters: the first 15 characters and then the remaining charachters (i.e. after 15...)
I would much apreciate some help...
Thanx
I have a split form, populated with my DVD movies, with the datasheet on the left. On the right I have 3 fields. Title Movie Type and Movie Description. All are enabled. But, today I was unable to select the Description field. I would click in it but the cursor would not enter to field. I needed to select another field and the then I was able to select Description. I was able to select the field programmatically. I needed to find a form from a backup and replace it.
View 12 Replies View RelatedI'm trying to allow my users to do some analysis of existing data. they would like to be able to generate a report which will summarize sales orders against the month they were placed. They would also like to summarize this by between 1 and 4 other fields.
I have created a "filter" form to allow them to choose the values for each of the 4 fields and my plan is that they will leave them blank if they do not want to narrow them down. My problem is that I would like to remove the grouping on the field if they do not select a value for it.
Example data:
Field1, Field2, Field3, Field4, Cost, Month, Year
1,2,3,4,£20,4,2014
1,3,4,5,£10,4,2014
1,2,4,5,£5,4,2014
If they select nothing for any of the fields I would just want to see that my total for April 2014 is £35
If they select 1 in Field1 then I want to see one line with a total of £35 (i.e. it ignores the distinction of the other selectable fields)
If they select 1 in Field1 and 2 in Field2 the total would be £25
If they select 2 in Field2 and 4 in Field3 the total would be £5
etc. etc.
I am struggling because I need the distinct records to allow me to group on them when I need to but if I don't need to group on them I need to ignore the grouping.
I'm trying to use select case on the 1st two digits of a field. I f it equals 1- then the case is meet but its not working. Will this work or no?
[CODE Case (Left(Me.Step42, 2) = "1-")][/CODE]