Order 2 Different Fields In Query By Number
Sep 2, 2005
i have a query which looks at 2 fields: "employee number" & "agency number", i would like the query to list the employee numbers in order first (1,2,3...) then the agency numbers in order (100, 101, 102...). ive changed the ascending/ descending order for them both but doesnt help. Its mainly the form that looks at this query i would like to fix so that is shows all employee records in order of employee number first and then underneath all agency numbers in order of their number (some employee numbers & agency numbers are null) hope this makes sense , any help would be great
View Replies
ADVERTISEMENT
Jan 2, 2015
I'm trying to create a query that generates random numbers for each record, sorts them by that field, then selects the top record. This should randomize the record being selected.
I can use the Rnd([ID]) function which does appear to generate a random number. Problem is that each time I exit the program and come back in, it always selects the same record. When I remove the Top = 1, to show all the records, every row does have a different random number but it does not appear to be sorting by this field.
If I run the query, here is the number I get: 0.98609316349029
Exit the program, restart, and run the query again: 0.98609316349029
If I refresh the query, the second and third time does appear to be random but the first result is always the same. how to generate truly random numbers?
View 7 Replies
View Related
Aug 27, 2015
I have a question regarding the number of fields in a query. I have a query that is looking at production scrap by categories. There are fifteen categories of type of scrap and in addition to these I am using the query to work out the total % of total scrap for each category. My problem is there are not enough fields to do all the calculations. Is there a way of adding more or is there a better way of doing what I am trying to achieve?
View 10 Replies
View Related
Feb 3, 2015
I have a table with two numberfields (let's call it NumbersToFilter and FilteringField).
I want a query that shows me ALL NumbersToFilter data WHERE FilteringField is the highest.
Eg. if this is my table:
NumberstoFilter / FilteringField
999 / 50
947 / 44
920 / 50
941 / 48
I want my query to return 999 and 920 (filteringfield max = 50)
I'm currently using an aggregate query, using group by NumberstoFilter and max FilteringField, but it returns all the fields (the maximum is simply on top )
View 2 Replies
View Related
Nov 27, 2007
A short question deserves a short answer:
DMax
View 1 Replies
View Related
Mar 26, 2013
I have an excel data file that is updated monthly (and I am not able to have the source file format changed of course) that will be my linked table for Access 2007. The file reports monthly data and adds the new month to the right of the historic in a layout like this:
Tree | Orchard | Province | Jan % red | Jan % green | # Limbs | Feb % red | Feb % green... etc so each month it adds 3 new fields to the data.
I have queries (rightly or wrongly) that will calculate the number of apples that month and report on those higher than a certain percentage. I would like to do this without having to create a static table to append to each month as the new data file will always show the full YTD results anyway (and I don't like to store data in my database).
I have a query that counts the number of fields in the raw data file and am wondering if there is a way to have access only run the number of queries required based on the number of fields. i.e. if 11 fields then run queries 1 thru 5, if 14 run 1 thru 6 etc.
View 3 Replies
View Related
Jan 25, 2007
Dear All,
I know that there may have been many posts on this and I have searched for a total of about 2 hours in these forums before posting, so please don't shoot me down for asking this.
I simply need to know how I can create a unique, alphanumeric order number. I was going to use Autonumber, but from what I have read on these forums, that is not the way to go, as Autonumber is simply to identify the record.
What I need is an number like so: ST000. It needs to start at ST142 and increase by one each time. Obviously I would like to avoid gaps from records being deleted or not completed etc.....
I really have no clue how to do this, and would appreciate it if anyone can point me to a post that would help, or offer me the solution.
Thanks,
View 13 Replies
View Related
Aug 13, 2015
i am going to use the autonumber as an order number but I want " SC2015 in front of it so i end up with " sc20151 , sc20152 but next year i want it to change the year SC201650 .how to put the sc2015 in my report without any input from the user
View 2 Replies
View Related
Jul 30, 2013
So, I have some complicated calculations I am aliasing in my SQL query. My end goal would be to sort by my final calculation from all the other calculations I have performed but I am running in to some issues. I know I can't sort by referencing the alias itself without saying order by (column number). My calculations are usually pretty small (around .001 and up). Whenever I order by the column number I would like to use (select... from... where...order by 73) I get a "Division by zero" error in access. I figured this was an issue with how small the calculations were, so I multiplied by 1000000, then ordered by that column number. Still division by zero...I went ahead and changed all 0 values to 1, still division by zero. There are no zero values in the column itself, it's either 1 or something in the thousands. I can order by other columns, just not the one I want to use. I don't want to have to re-do my entire calculations in my order by clause.
View 3 Replies
View Related
Jun 25, 2013
I am on code want to display records on the form by order_number, but i am unable to do it.
Private Sub Combo63_AfterUpdate()
Dim rs As DAO.Recordset
Dim intcount As Integer
On Error GoTo ErrorHandler
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Master_Log WHERE Order_number = " & Combo63.Value & "", dbOpenSnapshot)
[Code] ....
View 6 Replies
View Related
May 6, 2013
I am trying to create an auto-sequential order number
Example : AB000112 - AB is standard prefix, ''0001'' is the first invoice number, ''12'' is the year 2012.
so the next order number should read AB000212 and the next AB000312
Out of this, I have to issue several debit notes relating to the same order number when the payments are due from customers over a period of time, this will be
001AB000112
002AB000112
003AB000112
Can such numbers be the primary key?
View 4 Replies
View Related
Aug 14, 2014
This query tell me what quantity I am short of to make a part
diffqty: [StockQty]+[OnOrderQty]+[PendingQty]-Sum([OrderQty]*[ProductQty])
This will return a negative quanity iE -200
what I then would do is copy that to a purchase order but remove the negative sign can this can the formula be changed in anyway to give the positive number .
View 3 Replies
View Related
Jan 3, 2014
I am just beginning to manipulate Access from Excel.
I am trying to insert a row into a table. I am adding values for every field (8) except an Autonumber.
I have 2 Insert strings which are identical except for the fact that the one that doesn't work, doesn't specify which fields I am entering data into, which I presume shouldn't be necessary in this case.
When I try to use the second version I receive the error message...
Number of Query Values and Destination Fields are not the same.
The string that works perfectly is:-
Code:
strSQL = "INSERT INTO Clubs (ClubNumber,ClubName,ClubGrade,ClubRegion,ClubPosition,ClubHasHistory,clubinleague,cluboriginalposition) VALUES (" & clubCount + 1 & ",'" & lstrNewClubName & "'," & lintNewClubGrade & "," & lintRegion & "," & 0 & "," & vbFalse & ",'" & lstrNo & "'," & 10 & " )"
The one that generates the error message, which is identical except that I have removed the list of fields is:-
Code:
strSQL = "INSERT INTO Clubs VALUES (" & clubCount + 1 & ",'" & lstrNewClubName & "'," & lintNewClubGrade & "," & lintRegion & "," & 0 & "," & vbFalse & ",'" & lstrNo & "'," & 10 & " )"
The execute command is:-
Code:
gcnConnection.Execute strSQL, , adCmdText + adExecuteNoRecords
View 1 Replies
View Related
Dec 30, 2013
I am receiving the error above when I try to execute the code below. I have checked the fields in the code as well as the table and I can't see what I am missing. The tblMeasure table which is the subform have the following fields:
MUserLoginID - NumberMainMeasureID - AutoNumber - Primary KeyMeasureName - TextMPositonName - TextMeasureScore - NumberMeasureWeight - NumberMeasureTotal - NumberMeasureDesc - MemoMStaffApraisedID - Number
The UserDeatils table which is the main form have the following fields:
StaffID - NumberStaffName - TextDepartmentName - TextStaffPosition - TextStaffGrade - TextStaffBDate - DateStaffEDate - DateStaffApraisedID - AutoNumber - Primary Key
The link between the two forms are StaffApraisedID to MStaffApraisedID then StaffID to MUserLoginID then StaffPosition to MPositonName. When I select a member and click the Duplicate button I received the error above. The area highlighted in red is where it errors out.
Code:
Private Sub cmdDuplicateData_Click()
On Error GoTo Err_cmdDuplicateData_Click
Dim OldStaffID As Integer, NewStaffID As Integer
OldStaffID = Me.StaffApraisedID
'Add new record to end of Recodset Object
[Code] .....
View 1 Replies
View Related
May 27, 2015
I am looking into re-creating something I have created in Microsoft Excel into Microsoft Access.What I do in the Excel spreadsheet, is when I type in an order number, various fields in the spreadsheet are populated using vlookups and ODBC's.
I wish to re-create this in Access but not sure how. I have a basic form, with various fields such as, order number, part number, description, quantity.What I have in my mind is that I would like to be able to type in an order number, then the rest of the fields would be populated by a query to a SQL Server database.i can see a way to do this using VBA but would rather look for a more direct way if possible.
View 2 Replies
View Related
Jun 30, 2015
Is it possible to do a criteria like the "Between" to pull data like a work order number?
Ex WO#: WO5551212
I would like to be prompted to enter a Work Order Number when I run a query, then have the query display all of the data for that particular work order number.
View 4 Replies
View Related
Nov 26, 2004
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
View 6 Replies
View Related
May 29, 2015
DLookup function. (this is for a stamp collection database).
On my form ("InventoryInput") I have a text box called "Catalog" for a numeric entry and a text combo box for selecting a "Country" in drop down list.
I want to query a table called "CatNameList" to get the "StampName" of the item (based on the entries of Catalog and Country) and populate that name in the text box. The fields in this table are called "StampName", "CatNumb" & "CName" respectively.
I have successfully placed the following expression in the control source of that textbox and able to populate the StampName I need based solely on the catalog number alone.
That express is :
=DLookUp("StampName", "CatNameList", "CatNumb = Form![Catalog]")
So it will populate the "StampName" data to match the "Catalog" number entry just fine.
However, I need to add a second layer to incorporate the Country.
Example : There is a catalog "1" for "USA", and a catalog "1" for "Canada" but both have different "StampName".
I have been attempting to get that second piece added with no success. Here is the expression I have been trying to get to work :
=DLookUp("StampName", "CatNameList", "[CatNumb] = " & [Catalog] & " And CName = '" & [Country] & "'")
Right now, the text box is just blank with the above expression. I thought it may be because there was no match found, but I have triple checked to ensure I have the spelling correct on the country name in both places.
Basically, I just need the dlookup to take the "catalog" and "country" off the form and match it to the "CatNameList" table fields of "CatNumb" and "CName" to give me "StampName" field back on the form.
View 4 Replies
View Related
May 29, 2015
Having problems getting dlookup to work in the control source field of a text box.
My form has fields : Catalog # (numeric value) and Country (drop down text selection).
I would like to query a table CatNameList for a name (text) if the catalog # and country find a match on the table.
My field names on the CatNameList table are : Name, Number (to validate against the Catalog # entered on the form) and CName (to validate against the Country drop down on the form).
I am successfully able to populate the name from the CatNameList table on my form using lookup of the catalog # using this :
=DLookUp("Name","CatNameList","Number = Form![Catalog #]")
However, I will eventually have several catalog numbers that will be identical in the table CatNameList, thus why the country is important as the second criteria to be added into the dlookup.
I have tried for a few hours unsuccessfully to add the second portion to my dlookup.
This is what I have currently (not working) that I have been playing with, I'm sure I'm missing a quote mark, & or something simple.
=DLookUp("Name", "CatNameList", "Number = Form![Catalog #] And CName = ‘”& Form![Country] & ”’”)
View 14 Replies
View Related
Apr 28, 2013
I have an acces form to input client orders. Each order has its own order number a different client. On the same form I have a button to generate a report based on the inpuuted data on the same form which works perfectly fine.
Now I need to use same report to save each order in a specific folder on my computer in pdf format. However I would like to have the file generated as follows ordernumber "-" clientname.pdf. This way each order is saved in the same folder without overwriting the previous one.
View 1 Replies
View Related
Feb 26, 2015
I've been asked to edit a current report that lists our production in week number order. I need to look at way we can 'flag up' orders that are within a 4 week period from the current date.
For example,
10 Orders in total in our database. 5 of which are due the drawings back within 4 weeks from today (26/02/15). I'm looking for a way for the report to show the 5 orders as priority, either by formatting the orders in bold, a different colour or under their own heading/group.
View 14 Replies
View Related
Mar 6, 2014
I have some tables with identical field names, but the fields are in a different order. Is it possible to do a union like this, or do I have to rearrange the data within each table first?
For instance:
Table A: Field1, Field4, Field2, Field3
Table B: Field3, Field1, Field4, Field2
Table C: Field2, Field3, Field1, Field4
And I'm trying to combine those tables into TableABC: Field1, Field2, Field3, Field4.
View 3 Replies
View Related
Dec 6, 2007
Hello people.
Im just after a bit of advice please??
I have created an audit trail for data held on a form so that when a field is changed it adds a line of text to a memo field with the date of the change and the old and new field values. The problem I have is that each time something is changed the line of text is added beneath the existing text in the memo field. This means that for users to view the most recent change they have to scroll all the way down to the bottom of the memo field. Is there a "Quick" way of me having the most recent entry first.
I am using the vbnewline command when adding the text. A sample is shown below:
Dim Response As Integer
Dim AnotherItem As Integer
Dim sqlstr As String
Set Myform = Forms![DataDetail]
Producer = Forms![ListOfProducers(Existing Data Item)]![LstProducers].Column(1)
Response = MsgBox("Are you sure you wish to add a Producer?", vbYesNo, "Data Dictionary v1.0")
sqlstr = "INSERT INTO DataProducers ( Data_Id, Producer_Id ) SELECT Data.Data_Id, Producers.Producer_Id FROM Data, Producers WHERE (((Data.Data_Id)=[Forms]![DataDetail]![Data_ID]) AND ((Producers.Producer_Id)=[Forms]![ListOfProducers(Existing Data Item)]![LstProducers]));"
If Response = 6 Then
DoCmd.RunSQL sqlstr
Myform!LstHistory = Myform!LstHistory & vbNewLine & "Changes made on " & Now & ""
Myform!LstHistory = Myform!LstHistory & vbNewLine & "The Producer '" & Producer & "' was added to this data item"""
Myform!LstHistory = Myform!LstHistory & vbNewLine & "-----------------------------------------------------"
MsgBox "Producer has been successfully added.", vbInformation, "Data Dictionary v1.0"
Thanks folks...
View 2 Replies
View Related
Jun 23, 2005
Hi,
I have created a form with 3 subforms. This form is split in to 3 parts on a 3-page tab control.
I want the user to enter data in the order below. All fields are required. My problem is that Access97 wants all fields of the main form to be completed in the order set by the table. ie. before I fill in a sub form. I have tried setting the form tab order property but it is being overridden.
First page:
a) Complete first 6 fields of main form
b) Complete subform 1
c) Complete subform 2
Second page:
d) Complete 5th field of main form
e) Complete subform 3
f) Complete 6th field of main form
Third page
g) Complete three fields of main form
Any help appreciated,
Seth
View 1 Replies
View Related
Apr 9, 2015
am trying to change the tab order but have noticed that some of the fields are missing from the tab order form. I am attaching a screenshot to show which tabs are missing.
Screen Shot 2015-04-09 at 21.44.55.png
View 7 Replies
View Related
Aug 1, 2015
I have a Table with 6 fields, 2 are named Initial and Surname. Is it at all possible to have a combo box on a form with these to fields "joined" with the alphabetical order on the Surname. I understand that using a query will not work as the result is based on an expression
View 11 Replies
View Related
