I've created a new table to hold information about female customers and it would be nice if I could glance through it in alphabetical order of their surnames.
INSERT INTO TBL_FEMALE_CUSTOMERS SELECT * FROM TBL_CUSTOMERS WHERE GENDER='F' ORDER BY SURNAME ;
In MS Access the resulting table data is NOT sorted by surname, but it works fine when I'm using my trusty SQL Server database. Can this be done in Access? ...and yes, I know I just need to use a query to sort the data, but I'm more interested to find out why it works in SQL Server and not Access. Cheers, Sam
SELECT Count(Query1.Route) AS NumberofReRoutes, Query1.Route FROM Query1 WHERE [Start Date] <= #9/30/2014# AND [End Date] >= #9/1/2014# GROUP BY Query1.Route ORDER BY "NumberofReRoutes" DESC;
The last line seems to make absolutely no difference. I can change it to ASC, or I can remove the line entirely, and nothing changes. I need these sorted by the NumberofReRoutes field, but this method isn't working!The output at the moment is two columns: Route, and NumberofReRoutes.
Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?
I have a query with the following criteria in one of the fields:
>=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom())
fom is a function for first of the current month. I need this query to be specific to what month it is when its ran so i want to only have this criteria if the month is > = october. If it isnt October or greater, i want the criteria to reflect this.
>=DateAdd("m",-12,fom()) And <=fom()
Which also works by itself. But when i add it to an iif statement it always produces no results. Below is the iif statement.
Iif(month(date())>=10, >=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom()),>=DateAdd("m",-12,fom()) And <=fom())
I have also added the column name to each expression and it still doesnt produce any results.
I am creating a multi-search form for a student database, where after I enter my search criteria I hit a "Run Query" command button and then it opens a query form with all of my criteria.So far I can search using last name, first name, and middle name. When I try to search with a start date and end date I am have issues.The start date and end date is for the class date. In the query form under the field, class date, for criteria I wrote:
Between IIf([Forms]![Search Form]![Start Date]="",1/1/10,[Forms]![Search Form]![Start Date]) And IIf([Forms]![Search Form]![End Date]="",4/25/15,[Forms]![Search Form]![End Date])
I want it when I write a date in the start date and end date I want it to give me a list of all the students who took the course between those dates. Also, if I leave the dates blank I want it to search all dates. The dates 1/1/10 and 4/25/15 are just the dates I gave because that is far back as my database goes.
I have a startup form with three buttons. All three buttons have Tab Stop set to No. However, when I open the form, there is a highlighted box around what would have been the first button set in the tab order had Tab Stop been set to Yes. Is there a way to not display the light green box around the text in the button?
My tab order is the way I want it and when the user enters after the last field on the form, they get a new record. Problem is, the cursor is putting them in the last field on the form instead of the first one. I don't get it. The first field is first on the tab order list. Help.
why do we need to order MS Access application when the user request it in the company?
Suppose one user buy MS Access application, then we can install the other workstation, right. But, everytime, we need to order MS Access for that user account/workstation.
In contrast, ms word, ms excel, powerpoint, we don't need to order it, every workstation must have them, except ms access.
Can anybody response me why we need to order ms access for each user account/workstation?
I have sorted my table by the primary key as when i open the form that loads fields from the table, i would like the most recent record entered to appear instead of the first record that was entered. The problem i have is when i run the form it still shows the first record even though i have sorted the primary key field in descending order. How do i fix this?
On a form, I want to fix the tab order of the text boxes. So I go to the tab order property of the form and when it opens, it doesn't let me change the order. Can anyone help me on this?
I'm using Access 2003 and a week ago I ran into a very strange problem with one of my tables. The table has 15 fields named Field1, Field2, Field3,....Field15. The weird thing that is happening is if I open the table in design view it will list the fields in order (Field1, Field2, Field3....Field15), but when I open the table in datasheet view Field10 and Field11 get flipped (.....Field8, Field9, Field11, Field10, Field12....). Has anyone every seen this before? I'm guessing it somehow got corrupted. It wouldn't be a real big deal for me to just delete the table and create a new one, but if there is a fix to it then I was wondering if someone could share that with me. Thanks.
I'm working on an order management database for a small company and have an order details form which I hoped to list each product of the order. As each customer has a different price per product I have a combo (based on a query) to choose the product and display the unit price (working fine) - the quantity and product ID are also input on the form and stored in the order details table (un-tested). My problem is on the first line of the order you choose the product and it and relevant details are shown, however, on line two, three etc when you choose the product - the other lines change to this too. How do I fix this???
I have a database which in one part records customer orders. What I would like to is identify customers who have not ordered for a given time period i.e. 30 days.
I know once I have built the query, I will require a parameter for the time period i.e.30 days.
I started by trying to record the last date ordered for a customer in a query, but am having problems with this. I had a query including information from my table for Orders (tblOrders). This had:
Order ID Customer Name Date Order Required
I do not need any more info apart from these fields.
The results would include all orders, so for some customers there would be numerous 'Order ID' & 'Date Order Required'. In the query, I did try to select the summary option, to group information by Customer - but the 'Date Order Required' was always the first date ordered. What I would like is only the last ones for all customers to be shown.
Can someone provide some guidance on how I can achieve my objective.
I have got a number of queries that query an MSSQL database that were running fine, but then I decided to tidy them up by giving them more meaningful column headings eg Pupil ref instead of tblPupil. Some of the queries are still fine but in some of them I can't get the columns to display in the order I want. In one, I have got columns called Round, Pupil ref and School but I can't stop the Pupil ref column coming first. If I delete it and put it in again, I can get it to go where I want (ie second) but as soon as I name it, it comes first and stays there even if I remove the name.
I have to run a seniority query but have an issue. I have job descriptions but can't put them in ASC order because they wouldn't be by seniority. For example:
Docker Docker supervisor Dock Chief Shift Supervisor Section Leader
In SQL I would do a decode function and assign each a number and then put them in order by thier number assigned.
What are my options in Query to perform this function. Thanks.
I want to sort my query results by VendorNumber (which is an AutoNumber won't be sequential due to deletions). I want to sort the query by what vendor number the current form is on. So if my vendor numbers are 1, 2, 14, 16, 17 and the vendor number on the form is 14, I want the query to be sorted with the Items from vendor 14 first then 16, 17, 1, 2.
Is there anyway that you can order a query to be descending by an expression in your query rather than an actual field from a table. I am trying to do this but the following message keeps appearing:
The Microsoft Jet database engine does not recognize '[ExpressionName]' as a valid field name or expression.
Basically, my database holds records of sites in the UK, and I have a select query (qry_SumGIS3) that opens up a summary report (rptSumGIS3) when a button is clicked on the menu page. The report opens perfectly, showing me all the information I need to know about all the sites in my database. However, it is ignoring the Order By Clause I have specifically told it to do. I want to order the records that appear in the report by a calculated field (OBJ_WS_OVERALL_CALC) in descending order (highest to lowest percentage). Instead, it is ordering it by the site id (RTP_ID). How can I stop it doing this?
This is my code (that was created in the SQL view of the query design): "SELECT MEASURE1.OBJ_WS_OVERALL_CALC, [Tab 1: Factual].RTP_ID, OBJECTIVES.OBJ_ID, OBJECTIVES.OBJ_NAME, MEASURE1.MEASURE_ID, MEASURE.MEASURE_NAME, MEASURE1.MEASURE_SCORE, MEASURE1.MEASURE_WEIGHT FROM [Tab 1: Factual] INNER JOIN (OBJECTIVES INNER JOIN (MEASURE1 INNER JOIN MEASURE ON MEASURE1.MEASURE_ID = MEASURE.MEASURE_ID) ON OBJECTIVES.OBJ_ID = MEASURE.OBJ_ID) ON [Tab 1: Factual].RTP_ID = MEASURE1.RTP_ID ORDER BY MEASURE1.OBJ_WS_OVERALL_CALC;"
The red line is the part of the code that I think is being ignored.
