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
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...
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
I am trying to replace the numeric values of a field with a text in querry using Choose function, something like this aa5:
Choose([a5]-1="one";[a5]-2="two";[a5]-3="three";[a5]-4="four")
But it is not giving me no results :(( I am missing something but I do not what. Maybe I should use some other function?
I hope someone will help me!
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.
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
791335.12pack. This is the object that is in one of my access fields. I need to extract the 12 and place that in another column called qty.
View 1 Replies View RelatedI 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 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.
Date of Birth (DOB) field etc. in one program are text - how do I make another file with the same data into number fields for Date of Birth field etc? When I copy data to file that has number fields the 09252004 is changed to 9252004. Can I get reports with the correct Date of Birth in them by moving data from text file to number file?
There is data entered monthly in file and formula has been set up for January, February etc as ---quarter: Int(([month]-1)/3)+1. I would like formula for the fiscal year for April to be counted as month 1, May - month 2, June as month 3, July as month 4, August as month 5, Sept as month 6, October as month 7, Nov as month 8, Dec as month 9, Jan as month 10, Feb as month 11 and March as month 12.
Thank you
I have a form where we fill in information for supply of equipment to employees.
Each item must be signed for on a printed report.
I am encountering problems trying to create enough rows in my report detail for each signature of the items supplied.
For example, on the form I will select the "equipment" - 4 hats supplied and 3 boots. On the report I want the equipment set as the group and the detail to be a number or rows which equals the number of selected items. therefore under the Hats group heading I want 4 blank rows which are made up of 3 text boxes - Print Name, Signature & Date and another group heading for boots but with 3 lines.
I have a access table with 32 columns and 42,000 rows of numbers. I need to find the MIN number in the row and if the MIN number has duplicates then I need them all placed into another column by column name.
Example:
Starting file
DEST,ORIGIN1,ORIGIN2,ORIGIN3,ORIGIN4, ETC
05512,3,2,4,2
ENDING table needed
DEST,ORIGIN1,ORIGIN2,ORIGIN3,ORIGIN4, NEWCOLUMNname
05512,3,2,4,2,2 ORIGIN2 ORIGIN4
Where the new column name contains the MIN number in the row and all of the associated duplicates column names.
I'm trying to get an invoice number field to auto generate the next number, keeping the format as "00000"...this is what I have, which gets the next number but drops the leading 0
Code:
Private Sub Customer_AfterUpdate()
If Len(Me.[InvoiceNumber] & vbNullString) = 0 Then
Me.[InvoiceNumber] = (DMax("[InvoiceNumber]", "[tblInvoiceNumber]") + 1)
DoCmd.RunCommand acCmdSaveRecord
End If
End Sub
invoice numbers are 04024, 04025 etc...how I keep the formatiing?
Guys,
Could you please help me to make a query with the below conditions from the attached test.mdb file?
1. Enq Received Date 10 days before the search date AND/OR
2. Technical Sub Date 4 weeks in advance of the search date AND/OR
3. Potential Date 4 weeks in advance of the search date
The querry will display all the records which satisfies the above conditions.
Thanks in Advance.
Prajith
I have a table, [temp_indicateur] which has fields [id,libelle_court,description,calcule,resitute,orig ine,seuil_min,seuil_max]
[number,text,text,boolean,boolean,boolean,number,nu mber]
My querry is this: insert into temp_indicateur (id,libelle_court,description,origine) values (" & rs!id & ",'" & rs!libelle_court & "','" & rs!description & "'," & rs!origine & ")"
But it gives me an error: Too little parameters
The table has no primary keys and no foreign keys. rs!id is the value of an earlier querry which I am running. Independently, I get the values of rs correctly but I can't get them to execute in this querry.
My current table
ClassName PropertA
ClassA 1
CassA 2
ClassB 2
ClassA 3
ClassA 2
ClassB 2
THis is my sample table with two columns. Property A can have 3 values 1,2 and 3. I want TO SELECT 4 columns such as classname, CountOf1, CountOf2, CountOf3. I just want ClassA in one row and at the same time get the number of 1's, 2's and 3's for that classA in seperate columns in the same row.
I appreciate your time. Please Let me know how to
write this querry.
Hello,
This may sound too common a question, but I really need help on it. Prior querries in the same do not seem to solve it.
I have two tables, table1 and table2 with the following fields
table1 ( ACID, name, address, Salary, MStatus,Age)
table2 ( ACID,salary, Age)
ACID is the common field in the two tables ( primary key in table1)
Required
I need to update table1 with the colums in table2 for all the similar ACID in in table1.
Basically, after teh update, table1 will have all the information it had before the update, but a few fields will have changed coz of teh new information coming from table2.
Please help with the code. I'm using access, and the table structures are all the same.
Thanks in advance
I have these three tables A, B, C and D. IN table A, SSN is the primary key. Tables B, C, and D have other columns, but the also have SSN as one of the columns. Also SSN is NOT required in other tables.
What I need to do is to be able to run an update querry that will update the value of the SSN in table A, and in any other table that will have that SSN.
I use Access 2000
I Have A Querry That Adds The Amount Of A Certain Light Bulbs I Have Order Since 2003. I Then Subtract The Amount Of That Light Bulb I Have On Hand Now...it Gives Me The Amount I Have Used Since 2003. Now I Want The Querry To Take That Number And Divide It By The Numberber Of Months Its Been When The Querry Was Open.
Example...i Open The Querry 10-23-06
It Tells Me The 60 Watt Bulb
Has Been Ordered 900 Times Since 12-2003
I Have 300 In Stock On 10-23-06
I Have Used 600 Since 12-2003
The Querry Needs To Give Me This Answer
You Use 17 60 Watt Bulbs A Month
hello all,
i would very much like anyones help with this,, i'm hoping its not too much to ask,, the story is a sfollows,,, i have two tables both conatining the same fields,, table 1 has approx 1k entries, where table 2 has approx 400 entries,, the data in table 2 is the newer data. what i would like to do is run a querry that would enable me to 'post' the data from table2 into table1,, for example table 1 has activity jc111 at 10% where table2 has jc111 at 20%,, if i could simply run this querry that would generate a new table or just up-date table1 my life would be very much better,, i really appreciate anyones help
- please remember that i'm quite new to this
thank you very very much in advance
best regards
Hi,
I am just starting with access and dbs and already have a problem
i am working on a db for managings goods being retured
my db has particular tables:
GOOD
GUpc (PK)- unique product code
GDescription
PERSON
PReference (PK)
PName
PSurname
PTel
PMail
COMPANY
CReference (PK)
CName
CAddress
CTown
CPostcode
CCountry
RGA
RGAReference (PK)
CReference (FK)
PReference(FK)
GUpc(FK)
RGADate
RGASatus
RGACostOfRepair
I managed to get a query working so that i can search the PERSON table searched by surname
PARAMETERS [PSurname] Text ( 255 );
SELECT PERSON.PReference, PERSON.PName, PERSON.PSurname, PERSON.PTel, PERSON.PMail
FROM PERSON
WHERE (((PERSON.PSurname)=[PSurname]));
but i cant get it to work i wanted to search it by PReference
PARAMETERS [PReference] Text ( 255 );
SELECT PERSON.[PReference], PERSON.[PName], PERSON.[PSurname], PERSON.[PTel], PERSON.[PMail]
FROM PERSON INNER JOIN RGA ON PERSON.PReference=RGA.PReference
WHERE (((PERSON.PReference)=[PReference]));
I get a msg that The expression is typed incorectly or is to complex to be evaluated. For example a numeric expression my contain too many complecated elements. Try symplyfing the expression by assigning parts if the expression as variables.
AND THIS IS NO HELP FOR ME... francly
The same situation is with all the other tables- its possible (query- search) for everything apart for the primary keys.
I prepared a querry gathering data from all those tables. I managed to get it work with parameter like the one below with surname
PARAMETERS [PSurname] Text ( 255 );
SELECT COMPANY.*, RGA.*, PERSON.PName, PERSON.PSurname, COMPANY.CName, GOOD.GDescription
FROM PERSON INNER JOIN (GOOD INNER JOIN (COMPANY INNER JOIN RGA ON COMPANY.CReference = RGA.CReference) ON GOOD.GUpc = RGA.GUpc) ON PERSON.PReference = RGA.PReference
WHERE (((PERSON.PSurname)=[PSurname]));
but the same situation again, i cant get it to work if i want to get it for RGAReference, CReference, PReference,GUpc
I don't know what i am doing wrong?
have I designed it all wrong or is it just a small thing...
Thank you for all your help
My problems started when I tried to run a querry that will return the GDescription (this tables store data about 3000 products)
I have created a search form made up of a number of different boxes. In my querry for the search I am using the following statement:
Like "*" & [Forms]![Search]![ExerciseName] & "*"
I a have a number of theses all saying similar things. At present I have to put something in each of the search boxes. Is there a way that I can leave a box blank and still search by other criteria. If I leave a box blank at present it returns no results.
Hello All
This might be an easy problem but I am not quite sure how to go about solving it. Presently I have a querry that is linked to a form so that when a person selects a role from the drop down list and you click search it uses the elment selected in the drop down list to show the coreesponding users. My probelme now is that I want to be able to select two different roles and use this querry to give me the users corresponding to these 2 roles. I would really appreciate your help what I have tired to do is create a new combo box and named it role 1 (the first one was named role) and then in the query in the criteria i tired to put the following
[Forms]![Users To Role Search Engine]![Role, Role1]but that give me nothing I am sure there is a flaw in my logic but I am not sure how to solve this. Thank you for your help
Hi,
I have this querry but when I execute on a button click it dosen't give me any error....but no data is saved in the table, why????
strSQL = " UPDATE DISTINCTROW projet set id='" & txt_id.Value & "',code_NOP='" & txt_code_NOP.Value & "',client='" & txt_client.Value & "',libelle_projet='" & txt_libelle_projet.Value & "',id_clarity='" & txt_id_clarity & "',technologie_projet='" & txt_technologie_projet.Value & "',em='" & txt_em.Value & "',date_debut=#" & txt_date_debut.Value & "#,date_fin=#" & txt_date_fin.Value & "#,site_mandataire='" & txt_site_mandataire.Value & "',charge_totale=" & txt_charge_totale & ",ca_total_vendu=" & txt_ca_total_vendu.Value & " WHERE id='" & Form_projet.id.Value & "';"
I have a string which I want to use as a querry, but its giving me an error saying "object required" even before executing the querry..
strSQL = "INSERT INTO projet (id,code_NOP,client,libelle_projet,id_clarity,tech nologie_projet,em,date_debut,date_fin,site_mandata ire,charge_totale,ca_total_vendu,no_sous_fon,no_ec rans_s,no_ecrans_m,no_ecrans_c,no_traite_s,no_trai te_m,no_traite_c,no_impression_s,no_impression_m,n o_impression_c,prov_risque,prov_garantie)" & _
" values('" & txt_id.Value & "','" & txt_code_NOP.Value & "','" & txt_client.Value & "','" & txt_libelle_projet.Value & "','" & txt_id_clarity.Value & "','" & txt_technologie_projet.Value & "','" & txt_em.Value & "',#" & Format(txt_date_debut.Value, "mm/dd/yyyy") & "#,#" & Format(txt_date_fin.Value, "mm/dd/yyyy") & "#,'" & txt_site_mandataire.Value & "'," & txt_charge_totale.Value & "," & txt_ca_total_vendu.Value & "," & txt_sous_fon.Value & "," & txt_ecrans_s.Value & "," & txt_ecrans_m.Value & "," & txt_ecrans_c.Value & "," & txt_traite_s.Value & "," & txt_traite_m.Value & "," & txt_traite_c.Value & "," & txt_impression_s.Value & "," & txt_impression_m.Value & "," & txt_impression_c.Value & "," & txt_prov_risques.Value & "," & txt_prov_grantie.Value & " );"
Can anyone solve it?