Code:SELECT * FROM mytable ORDER BY type, value
hi i am trying to order a select statement by two columns, so it first orders by type, then by value. the above code would work in php/mysql (i think!) but not with asp/access. any ideas?
cheers
I have a query with an INNER JOIN and ORDER BY that is working great. Now, using the same JOIN, I need to update values in one table with the values in another. I thought it would be simple until I learned you can't do an ORDER BY with an UPDATE. Is there another way to achieve the same result? If you remove the 'ORDER BY', the statement below doesn't produce an error but the results are not correct:
UPDATE TableA INNER JOIN TableB ON (Left(TableA.CDN,6))=(TableB.CDN)
SET TableA.HCC = TableB.HCC
WHERE TableB.HCC Like '241*' AND TableB.BBB = 'X' AND TableA.CCC = "1234" AND TableA.HCC IS NOT NULL
ORDER BY TableB.HCC, TableA.CDN;
I have a Form which presents the results from a query. Upon clicking the header of each column of data the records are sorted A-Z and Z-A using the code below. This works fine when all records have data in the relevant field but not when several records have a value of null. It appears that when null values are present the "If Me.OrderBy = "property" & " DESC"" always evaluates as not true, even though all the null values are on top and all the filled in values are in appropriate order.
A second issue is one of my fields is not sorting properly at all. I have two date fields, one sorts the other does not. The one that sorts is a user entered date (8/22/2012) which defaults to "9/9/9999" if the user does not enter a different value. The other date field which does not sort properly at all is a calculated date field which chooses the earlier of two possible dates (both associated with the same record) using
IIf([Senior Mortgage Maturity]>[Mezz Maturity],[Mezz Maturity],[Senior Mortgage Maturity])
This code is repeated for each of the six fields with the relevant field name in place of 'property'.
Private Sub Property_Label_DblClick(Cancel As Integer)
Me.OrderByOn = True
If Me.OrderBy Like "Property" Then
Me.OrderBy = "property" & " DESC"
Else
Me.OrderBy = "property"
End If
End Sub
Is there a way to order the values in a multi-select list box:
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20 appears in the listbox as:
1,10,11,12,13,14,15,16,17,18,19,20,2,3,4,5,6,7,8,9 . Is there a way to have it appear as it does originally?
I've got a rapidly expanding database that I designed to do my quotes for work and now I'm trying to expand it to add up the invoice amounts when the jobs come to fruition. I've got 99% of it working well but as I'm not the only one entering data I'm trying to make it as foolproof as possible and here is my problem.
I have a multi-line textbox that receives 'vehicles' in an abbreviated format and I need to get them in order based on a sort column number in the appropriate table. This i can do but the code I have found removes the duplicates which I don't want as I can can two identical vehicles on the same job.
e.g
Vehicle Sort Order
UNT/FL 500
FL4/45 400
E7/F5 600
UNT/FL 500
I want as
FL4/45 400
UNT/FL 500
UNT/FL 500
E7/F5 600
NOTE: Im working in MS Access 2003, only results in this SQL/database (not mysyl, MSSQL etc)
I want to be able to ORDER an SQL query and put the NULL values last.
I have a basic databse:
Table name: PeopleTable
Field names: TableID, PersonField, PersonID
The aim is to order by PersonID and put the NULL values last
See pic1.jpg;
The picture (pic1.jpg) shows the database as it is without a query.
See pic2.jpg;
Picture 2 (pic2.jpg) shows the query results when I use the orderby statement (SELECT * FROM PeopleTable ORDER BY PersonID As you can see it shows the results ordered by the PersonID however the NULL values are first.
See pic3.jpg
Desired results, it is ordered by the PeronID and the NULL values are last (NOTE this is an editied screenshot).How can i achieve this is MS Access 2003?
I wanted to auto populate an invoice record with same values as previously ordered by that same customer. I don't want to use default values as every customer orders different things.
BUT almost all customers make REPEAT orders of exactly the same things they ordered previously.
So...for example, can I search the invoice records by customer ID/Name and copy all the data from their previous into a new invoice record. I guess I could do this with an append query?
But will an append query run when I just click on 'new record' in the invoice form? Maybe there is a property '.on new record'...open append query....?
I have a pretty simple database that we use to track customers, orders, invoices, and payments. I have an order form that shows all of the order details, and has a subform with services, qty, price, etc. Sometimes we need different products that are part of the same order to appear on two different invoices (say an order is for part 100 and part 101. If part 101 ships first it needs to be invoiced, and part 100 will be on a separate invoice when it ships later), so one order can have multiple invoices.
I'd like to create a solution where when a user clicks the invoice button on the order form, they are able to select which lines from the subform should be invoiced on that particular invoice.
I am setting up VBA to run multiple append queries in consecutive order. The append queries are supposed to pull a four digit number off of a form that will allow them to know which data to append. I want the number to be stored as text to correspond with the tables I am working with, but I am having trouble getting the form to work.
Here is how I would like it to work:
I enter my 4 digits in the text box on the form. Say "1305" for May 2013. I want to then run my vba (my queries update using the forms!txtupdatequery!textbox I have put in the criteria of the queries) and all of them run.
I enter my date (as a number but I want it to be text) and the form gives me a #name? error.
I have 2 databases Product, with 3 entries ProdID, ProdName and UnitCost.
2nd database Order has Order ID, ProdName, UnitCost, Quantity, SaleDate and Total.
I want to create a form to input data into the Order databse that allows me to enter several rows using the data from the product table by selecting ProdName(Product) from list and UnitCost(Product) being inserted into each row automatically as a new ProdName is selected.
Can anyone hsuggest how best to do this.
I have a production database in which orders are checked for shortages at different levels of production in which :
1 raw product can be fetched in 2 or more final products, Shortages are checked as per priority.
In this database i have tables named "finalproductlist" and "rawproductslist" in which products are connected in one to many (see table "productmapping"). For E.g. 1 product in raw table can be fetched into 2 or more final products.
I have stocks query on both levels with which shortage is checked.
I want to create the order shortage query which gives the shortage at both levels prioritywise.
I have a tabbed form. The main form is titles ContractDtlsFRM. There are 3 other subforms in separate tabs. The first field in the ContractDtlsFRM is Contract No. I would like the form to sort in ascending order by this number.
I tried entering the following code in the Forms Order By event but it didn't work
Private Sub Form_Open(Cancel As Integer)
Me.OrderByOn = True
Me.OrderBy = [Contract No]
End Sub
I have created an access application for production system .
I have a final stock query and 2 to 3 orders based on priority and i want to create a shortage list based on priority ... here is detail :
ItemName....|Stock qty....|ORDER1....|ORDER2....|ORDER3....|
A.................|200............|40............. |90............|100...........|
B.................|500............|0.............. .|400..........|200...........|
C.................|30..............|40............ .|0..............|20............|
D.................|0...............|0............. ..|10............|0...............|
E.................|900............|400...........| 300..........|280...........|
F.................|130............|200...........| 50............|0...............|
Now I want shortage as:
ITEM NAME |O1 SHORTAGE | O2 SHORTAGE | O3 SHORTAGE|
A................|00..................|00......... ..........|30..................|
B................|00..................|00......... ..........|100.................|
C................|10..................|00......... ..........|20...................|
D................|00..................|10......... ..........|00...................|
E................|00..................|00......... ..........|80...................|
F................|70..................|50......... ...........|00...................|
What method Do I use to write a query as I have hundreds of Products and 40-50 orders????????
I have a form for user to select multiple items from a combo box, and 18 unbound text boxes , each time user selects an item in combo and hits "add" the value appears in txt box by order, I mean select1 then "add" filling txtbox1, select2 then "add" filling txtbox2 ... and so on till we reach txtbox18, then msg box appears that he filled the whole 18 boxe. I have already done this before for one txtbox which is a very simple operation, but i cannot figure out how to do it with multiple txtboxes.
View 2 Replies View RelatedI have what is probably a stupid question but I've been struggling with this one for a while. I have an ordering database which has an Orders table (containing Order ID, Date, Supplier etc) and an Order Line table within which I have a combo box for Product name, supplier, price, VAT rate, Line price etc. At the moment, I have the order line table as a subform within the Order form (run from the Orders table). The problem I'm having is the subtotal and total fields. At the moment these are in the Order Line table as I cannot figure out how to get these in the Orders table. In summary, can I make a calculated field in one table that calculates values from another table (linked by Order ID)?
View 3 Replies View RelatedI am using the formula:
Normal Pay: Sum(IIf([PAYCODE]="STD" Or [PAYCODE]="PAY_R" Or [PAYCODE]="RATE*",[AMT],0)) .
as an expression and need my query just to pick up one row with a total sum of these values, however I am still receiving 2 or 3 lines of seperate values.
Can anyone help?
Thanks
Paul
I have a Customer table with the normal customer info, name phone.Each customer has one or several parts. so I have a parts table.I have a service table and form.The service form contains the service number along with customer number.Each service has a service detail subform for service to the customers part.The problem I'm having and It seems to me to be an easy answer but it has escaped me totally. When I go to the Service detail form I want to pick the specific customers part.I cannot just select from the particular customer, but have to pick from a list of parts from every customer. I just want to see the parts from a certain customer that I have selected from the service order form.
View 3 Replies View RelatedI have a list of Vehicles in which each have their own maintanance schedules. Most of the maintanance for the Vehicles overlap. Instead of entering the same information over and over I'm assigning each type of vehicle a code number (example '05 Cargo is a 1, '04 Cargo is a 2). Now what I want to do is assign each maintanance the vehicle code, for instance I have 13 different types of vehicles that all need oil changes, so I want to assign the Oil change maintanance 13 codes. Is there a way to assign that maintanance 13 codes without having to enter it 13 times??
Thanks, Joe
Hi,
how could i get multiple values shown in 1 form only? i currently have a code to look through 6 fields and if the fields iar blank, you get a a message box. you press ok and the next message is shown for the next field if it is blank but it would be so much better to have 1 message box showing all blank fields required to be filled in.
all it does is check the fields as these are required before continuing. if anyof the fields are blank, then it tells you and you cannot continue untl they are all filled. it seems easy in my head!!
im sure this can be done but im not quite sure how.
any ideas would be greatly received.
NS
Hi All,
I'm a relative newbie to Access - I am wondering the best way to solve a problem. I am the admin for a database used to track projects and the quality control process. With this process we must keep up with the review of another database, the pieces of information entered which are erroneous and when the individual responsible corrects the data. To date, the errors have been manually typed into a memo field which is then pushed out to the end user in a report. The errors are very consistent and I want to create functionality that will allow me to select the errors (there are approximately 50 standard errors) from a list or using a button or whatever is wisest. Basically, I would love some best practice opinion here as I am totally stymied at this point.
Any advice for a weary admin?
Hi,
I am experiencing some confusion in setting up a many-to-many relational database, and I would greatly appreciate any assistance that anyone may have to offer.
Currently, I have two tables: one which is a last of organizations provided services, and the second table is a list of counties in the state. While many organizations only focus their services on one county, some offer services in multiple counties. The desired end product is a website in which users can search for available service organizations based on the county they have selected.
In Access, what is the best way to link each individual entry in the organizations table to the appropriate counties in which they offer services? For example, I will need to say Organization1 offers services in County1, County2, County43, and Organization 2 offers services in County2 and County62. When a user selects County2, it should return both Organization1 and Organization2… and so on.
What is a more efficient way to store this information than just having multiple columns?
Thank you in advance for your help.
I have a query, and one of the fields has a prompt for user input. Is there a way for the prompt to take multiple values?
View 1 Replies View Relatedhow to add times together from multiple subforms and display the data on the main form. Just for backround, I am creating a timesheet to be used by our workshop employees. The form is set up in such a way that there is a combo box where they can select their name which rotates the record so all the data is specific to their id, and then there is a tab box with a tab for each day of the week each containing its own subform with combo boxes to select the project they worked on, text boxes to enter the start time and stop time for that project, and then 2 other text boxes: one that calculates time worked on that particular project and then one that calculates the total time worked for the day since they typically work on more than one project in a day. So what I need to do is to take the values from the total daily hours boxes and add them all together and show the sum in a box showing the total hours worked for the week.
My most recent attempt was to simply make an expression in the control source as follows:
=[Sunday subform].[Form]![Daily Hours]+[Monday subform].[Form]![Daily Hours]+[Tuesday subform].[Form]![Daily Hours]+[Wednesday subform].[Form]![Daily Hours]+[Thursday subform].[Form]![Daily Hours]+[Friday subform].[Form]![Daily Hours]+[Saturday subform].[Form]![Daily Hours]
Is it possible to have a text field in a table to store multiple values by using a comma or some other type of seperator? I do not want to set the field to a lookup field because the number of values that would be included would be to many to continuously look through to select. I would prefer to just manually type in what I need and use a seperator to create a multiple value field.
View 7 Replies View RelatedI am developing an access db where employees are allowed to load tools from a tool store.
I have three tables: tblEmployee, tblTool, tblOnLoan.
tblTool includes a field "QtyOnHand" which is the quantity of a particular tool in store available to be loaned.
tblOnLoan is used to record which employee has what tools on loan.
When an employee loans a tool i need to be able to reduce the QtyOnHand of the tool and record the loan details in tblOnLoan.
When the employee returns the tool i need to increas the QtyOnHand of the tool and record the return against the original loan in tblOnLoan.
i have not yet been able to work out how to reduce or increase the QtyOnHand as tools are loaned or returned.
Can anyone please help?
I am trying to concatenate values from seperate columns. Which is fine, however if there are no values in any of these columns I wish to include a dot (.). I also need to add a dot (.) inbetween values.
So for example
Column1 Column 2 Column 3
B11 Europe
would concatenate as:
B11.Europe.