Hi all,
I am wondering if it would be possible to do this:
Use a query to create a list of items from the column (duplicates removed) ordered by the frequency that they occur.
For example the column lunch
-------
|lunch|
|-----|
|pizza|
|kebab|
|tacos|
|pizza|
|tacos|
|pizza|
-------
Would end up like this:
-------
|pizza|
|tacos|
|kebab|
-------
Thanks in advance :)
I have a table with fields like this one but the weeks go all the way up to 52. What I am trying to do is count the number of consecutive zeros and if it is more than five, count how many of the following fields have a number in them and if that number is less than the number of zeros preceding it identify that person.
For example Joe would be identified below because he had 6 consecutive zeros and then he had 5 weeks of numbers immediately following the string of zeros. Bob would not be identified because he had 5 consecutive zeros and then 5 sets of numbers immediately following the string of zeros so the zero frequency isnt higher than the number frequency immediately following.
ID
Name
Date of Hire
1
2
3
4
5
[code]...
Why does access automatically rearrange column order in a query after closing and reopening? Is there a way to stop it? It doesn't affect the usefulness of my query but I set the columns up in a specific order and I'd like them to stay that way. This is an update query. I see that it is grouping them by name. Anyway to stop that without giving each field a unique name? Screenshots attached.
View 1 Replies View RelatedI 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.
Please can anyone help
Fiona
Hi, I have a querie that returns various data based on the page of a part's Sub-Assembly. There are 4 basic items returned for each page that display in my subform it displays as follows:
SpecID - Page # - Part# - Description - etc.
4 basic SubAssembly Descriptions are
1 - A Text Description of Assembly as a whole (varies for each record and usually has a number such as 2" or 3" or 4" etc at beginning of field)
2 - Valve
3 - Selenoid
4 - Actuator
I want to sort these records so that the first field is that Text description field. I tried sort descending and this does sort descending but puts text field last. (i Know that this is because it is sorting on text first and then intergers. But I don't know how to program multiple OrderBy for this column. How can I sort by intergers first and then by text on this field? :o
Thanks for any help!
I create an inventory report which source is from a query. I have set a field "Date" displaying in ascending order in query. But the report cannot achieve that result.
My code is:
Private Sub PrtDelRec_Click()
DoCmd.OpenReport "DeletionTransferRpt", acViewPreview
End sub
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 RelatedWhy Access 2010 does not save the Datasheet order?? I keep setting the order and it puts it back again
I have set the order in the design. I have set the tab index individually for each item. I says in the tab order that they are in the correct order. I reorder them in the datasheet and click on save. Nothing sets that order. It will revert it back to the order that IT wants every time.
I have a subform in which I display a table in Datasheet View. The problem is, I cannot figure out how to place the columns in the order (i.e. from left to right) I want.
For some reason, right-clicking on a column header in Form View and selecting Freeze Fields will shift that column to the left but I can find no consistent pattern to it and no other way of shifting a column. Reordering the columns in the table itself seems to have no effect on the order they appear on the subform and moving the controls around in the subform in Design View also seems to have no effect.
I created a query, and in field one i have a list of SKU codes (of which many are repeats), and in field two I have their corresponding volumes. In Fields 3-5 I have some various algebraic calcuations.
Now, in order to get a sum of the volume with only one SKU code listed in column 1, I use a group by on the SKU code, and a sum for the volume. When these are the only fields in the query, this works great. But when I add in the other calculated fields, the run query returns to showing each SKU code multiple times, and shows the volume and the calculations correctly. When I change the other calculated fields to Sum, then I get the aforementioned overflow error and no output at all.
Is there a way to both set it so that the output is only showing one SKU code, the total volume for that SKU, and the weighted average output of the other calculations in one query?
I have a list box and a series of 11 fields a user can search by. My problem is that the List Box has changed the column order.
Example: In the design wizard of the list box I had it such that the columns were in order 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11
Now after I hit search the fields populate out of order... 4, 9, 2, 1, etc...
Hi, am sure this is simple....! Can't find a solution.
I have records which have YEAR and APPARATUS as two of the fields. All I want to do is write a query which displays all the records chronologically by YEAR then within each YEAR to group them by the biggest subset according to APPARARTUS.
Therefore if there were 20 people who used Motorised equipment, 15 who used electrical and 10 who used gas, the records would be grouped in that order. Thus showing the pattern in change in use of specific apparatus over the years.
Why can't I DO THIS!!??
aLI
I've been using MS Access 2007 for years to manage some Excel data. running some queries etc. Just recently I've been encountering problems when importing data into an existing table. When I do it now, I get a "Subscript out of Range" error. To troubleshoot, I imported into a new table and when doing so, the fields no longer match the column order of the spreadsheet. They all get imported but appears in a different order. I think this is why I am getting the error message. How can I go about ensuring that the data gets imported properly into my already existing table? My fields in "Design View" will match the order of the Columns in the Excel spreadsheet.
View 2 Replies View RelatedI'm opening a search form using DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria for user filtering purposes. I added a column which it now appears as the last column - I need it to be the 4th WHEN THE FORM OPENS. I can move after it opens, but it won't default there. Tried rebuilding the query and the form, but it still opens with this column at end of form - how can it be placed where I want it?
View 2 Replies View RelatedI am trying to run a simple update query to copy data from one column (Addrl1)to another column (Working_Addrl1) within the same file and I can't for the life of me figure it out. Then I need to repeat for addrl2 and addrl3 to working_addrl2 and working_addrl3.
View 7 Replies View RelatedI have created a cross tab to extract pipeline and sales for Q1 2014, Q2 2014, Q3 2014 & Q4 2014... the user can select the quater from a multivalued text box...
Now for the final output, have created another query which pull the above four quarter in each column from the cross tab...now the problem arises when i change the quarter to Q2 2014, Q3 2014, Q4 2014 & Q1 2014..it gives an error "Microsoft office Access database does not recognizes "Query name" as a valid field name or expression".
The error is because the second layer of query does not identifies Q1 2014.
How do i make access change the column automatically when the Q1 changes to Q2...
I am looking to add a column in a query that will give a Y or No to previous column data if it contains TEXT or NUMBER (It could read "TEXT" or "NUMBER" or even Y for text or N for number).
View 3 Replies View RelatedHello All!
I'd really appreciate some help for what I fear is actually a very amature question...any assitance would be greatly appreciated!
I have the following table:
SubCount---------HomeDetailsID------FoodDetailsIDFK--PrimaryName
Ruteete--------------199------------------54-----------Amatehe
Ruteete--------------218------------------54-----------Amatehe
Ruteete--------------199------------------54-----------Amatehe
Ruteete--------------198------------------90------------Amoozi
Ruteete--------------204------------------90-------------Amoozi
Ruteete--------------192------------------20----------Banana Juice
By Subcounty, it shows what homes in the subcounty are consuming a particular food item. What I would like to do is count the number of times that food item is being consumed in each subcounty and generate a new table to compile this data.
And I am trying to use the count function to return the frequency of each Food being consumed. However, the count function counts all the records and returns a value of 6. What I would like is it to count the frequency of each food and ultimately build the following table:
SubCounty---NumHomesFreqBasedOn--FoodDetailsIDFk-PrimaryName---Freq
Ruteete----------------6------------------54-----------Amatehe------3
Ruteete----------------6------------------90------------Amoozi-------2
Ruteete----------------6------------------205--------Banana Juice----1
I was thinking that this may involve using a loop to get a count on each specific food but the logistics of doing this are beyond me at the moment. Does anyone have any suggestions?
Thanks!
I have tried posting this message before, but with no results. I am trying to get a query that will give me all of my customers and show what their last order date is. The query that I have now is coming back with ALL the dates the customers made purchases. Here is how I have the query set up:
Field1 Customer ID
Field 2 Customer name
Field 3 Address
Field 4 State
Field 5 Phone #
Field 6 Order Date (Last in Group by)
Field 7 Shipping Method
Field 8 Salesman name
The query is a select query and I hit the totals button on the toolbar. In Field 6-Order date, I have it grouped by "last".
Another problem that I have it I am working in a 97 access database. I have tried to convert it to 2003 with no success. So, I am not able to make NEW queries or reports. I can modify them, but when I close the query or report design, it does not save.
Can anyone help me with this query problem? It is very perplexing.
The main form of our contacts database has its source in a query which places all contacts in alphabetical order by surname. How do I get the query to sort by surname and then by first name? Also, how can I get the query to run each time a new contact is added so they are put into alphabetical order when they are saved, rather than appearing at the end of the list?
View 2 Replies View RelatedHi All, I am modifying some asp product catalog query to sort by order of 'price' so the data from the DB is displayed on the page from cheapest to most expensive, I am a newbie but keen.....
<%if not request.QueryString("cid")<>"" then%>
</font>
<%end if%> <%
if request.QueryString("cid")<>"" then
set rs = cn.execute("Select * From Products Where CategoryID= " & request.QueryString("cid"))
end if
%> <%
if not rs.eof then
while not rs.eof
%>
This extract shows a request to select products from a specific category but I want to disply them in order of ascending price....can any power users out there set me straight. greatly appreciate it.
cheers now
I have a combo box that contains the months. They appear in month order on the form but when I query, they are in alphabetical. How can I get this to be in month order on the query?
View 12 Replies View Relatedi 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 7 Replies View RelatedI have a little problem
I have an access database containing the following
LibraryYearMonthVisits
Lenzie1999Dec1095
Lenzie1999Feb789
Lenzie1999Mar1293
Lenzie1999Apr1526
Lenzie1999May1231
Lenzie1999Jun1171
Lenzie1999Jul1206
Lenzie1999Aug1287
Lenzie1999Sep1206
Lenzie1999Jan1451
Lenzie1999Nov1706
Lenzie1999Oct1414
Lenzie2000Oct956
Lenzie2000Jan1113
Lenzie2000Feb858
Lenzie2000Mar1110
Lenzie2000Apr1198
Lenzie2000May1203
Lenzie2000Jun1105
Lenzie2000Jul1152
Lenzie2000Aug916
Lenzie2000Sep985
As you can see the months are in order alphabetically. How can I fix my query so the months can be in the right order. Jan,Feb,Mar etc?
I imagine it is a simple procedure but I am not sure how. PLEASE HELP :confused:
Derek
Seems easy but I can't get it to work
Table Services
Pk_Service
Service_Name
MyDate
Fk_License
Table Licenses
Pk_License
License_Name
I need to get all the services for one license, but only once, you'll see what I mean
Pk Service Service_Name MyDate Fk_Licence
1 Base 10/5/2004 5
2 Base 12/6/2004 5
3 Super Base 11/4/2004 5
4 Base 20/10/2004 5
5 Super Base 17/7/2004 5
there's about 7000 records with the same 4 services recurring all the time
All I want for one license is the all distinct services for a chosen date
If I select License 5, and today's date, 26/10/2004, I would need the latest service (closest date but has to be inferior or equal), in this case
Pk Service Service_Name MyDate Fk_Licence
4 Base 20/10/2004 5
5 Super Base 17/7/2004 5
Here's the query I have:
select distinct Service_Name from Services where Fk_License=5 where MyDate <= SelectDate order by DateDiff('d',SelectDate,MyDate) DESC
where Fk_License is to get only the services for a particular license
distinct if to only get each service once
MyDate <= SelectDate if to get only the services older than the selected date
order by is to get the latest service cause there will be a lot of services older than the selected date
they say distinct conflicts with order by
Anyone knows how to do this query right?
I have started a table to manage expenditure with dates payments and headings. Set up a query to summarise the monthly amounts by each heading. The Query is sorting the date in date in alpha order not date order so I get February before March etc. How do I get it to show chronological order?
View 3 Replies View Related