Querie Order By Num Then Text In 1 Column?

Nov 20, 2006

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!

View Replies


ADVERTISEMENT

Column Order

Jun 8, 2005

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.

Please can anyone help
Fiona

View 4 Replies View Related

Possible? Query Column And Order By Frequency

Aug 14, 2005

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 :)

View 2 Replies View Related

Reports :: How To Set Order With Designated Column

Jun 27, 2013

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

View 4 Replies View Related

Division By Zero In When Order By Column Number

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

Forms :: Datasheet Column Order Not Saving

Feb 8, 2014

Why 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.

View 7 Replies View Related

Auto-rearranging Column Order In Query

May 10, 2012

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 Related

Forms :: Column Order In Subform Datasheet View

Nov 22, 2014

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.

View 9 Replies View Related

Queries :: Order To Get A Sum Of Volume With Only One SKU Code Listed In Column 1

Jun 21, 2013

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?

View 1 Replies View Related

Access 2010 - List Box Changing Column Order

May 16, 2014

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...

View 8 Replies View Related

Tables :: Importing Excel Data - Maintaining Column Order

Mar 4, 2013

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 Related

Rearrange Column Order In Search Form (opening As AcFormDS)

Jun 17, 2014

I'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 Related

Forms :: Display Row Text From Subform Column In Unbound Text Box Of Main Form

Jan 6, 2014

My database has Main form and a Sub form. On main form i place one unbound text box named investigations. In subform of which datasheet there is a column named TestCode. I want unbound text box (Investigations) to display row values which selected in a column (TestCode) of subform.for example:Investigation field should display "CBC,HB,ALP".

View 5 Replies View Related

Displaying DATE Of Text Format In Descending Order

Jul 4, 2005

Hi All,

I have a field named "PICK_DATE" in text format where pickup date is stored in DD/MM/YYYY format.

Now I wanted to generate a report with the order by clause of PICK_DATE. But it is not giving me the desired result. I want to see the latest PICK_DATE at the top of the report in desending order.

Can any one pls help me out to solve this problem ? :confused:

Thanks in advance,

Jignesh

View 3 Replies View Related

Queries :: Work Order Number - Criteria To Filter By Text?

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

Forms :: Filling Multiple Text Boxes In Order By Choosing From Combo Box

Dec 1, 2014

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 Related

Appending Column Text

Mar 2, 2006

hello all,

I have a table with the following columns

ContractID
CostValue
CostType - string

The contractID is not unique.

What I am trying to do is for each contractID I want the cost type

For example if I had 4 rows
ContractID CostType Cost
1 a
1 b
1 c
1 d

I want the following returned from a select statement:
Contact ID CostType Cost
1 a,b,c,d


Can anyone suggest on how I could do this?

Thanks for your help
:)

View 8 Replies View Related

Help With A Querie

Sep 12, 2005

i have 2 tables jobs and jobs_details jobs have the fields name date account etc.
jobs_details have the print area, file, etc.
i need a querie to give me the total print area for each account on a particular date but i can't get it to work.
I've tryed this:
"SELECT DISTINCT jobs.Account, Sum(jobs_details.PlottedArea) AS SomaDePlottedArea, Sum(jobs_details.UsedArea) AS SomaDeUsedArea, jobs.JobDate
FROM jobs LEFT JOIN jobs_details ON jobs.JobID = jobs_details.JobID
GROUP BY jobs.Account, jobs.JobDate
HAVING (((jobs.JobDate) Between #3/1/2005# And #4/1/2005#))
ORDER BY jobs.Account;"
but it returns all the jobs.Account(it doesn't give the distinct) by all the dates

what am i doing wrong???

View 4 Replies View Related

Between Querie

Jul 13, 2007

Hi there

I've got the following query in my application:
SQL = "INSERT INTO tblreport3 ( exchange, NoCustomers2, [time] )"
SQL = SQL & "SELECT Table1.Exchange1, Table1.Customers1, Table1.Duration"
SQL = SQL & "FROM Table1"
SQL = SQL & "WHERE (Table1.IncStart Between #txtdatefrom# And #txtdateto#);"
DoCmd.RunSQL (SQL)

The problemis when I run the application it gives me a error 3075 saying missing operator in query expression.

Can someone please tell me what is missing here?

Thansk

View 1 Replies View Related

How I Can Run The Querie With VB???

Feb 18, 2005

Hi

How i cun Run Queries with VB

My Querie : Delete Records in table1, It Query in access no string of VB

I want when i delete one record in table2 run this Querie

I try with this command DoCmd.OpenQuerie DelNoExistsRec.
but i hav one message : The action or method requires a Query name argement????

View 5 Replies View Related

Modules & VBA :: Text Column Holds Value Of ID

Feb 22, 2015

I have a table Actions as follow

Action ID Action
1 cutting
2 sowing
3 ripping
.. ..

At some time , on clicking the button that opens this lookup table I found some of the values of Action equal to Action ID

Action ID Action
1 1
2 sowing
3 3

What are all possible scenarios that would lead to Action column taking value of Action ID?

View 9 Replies View Related

Align Text In A Field Column?

Aug 16, 2011

how to make my text on a column align to it reads upwards?.

1) is it under the "Field Properties" (the bottom of the screen, last option) or

2)is it under the "property sheet". under some other option? perharps.

i tried option #1 and i tried all the option under the "align text" but nothing happened.

View 1 Replies View Related

My Querie Keeps Duplicating

Nov 25, 2005

Hi

Please can some help me asap. I need to do a querie which joins two tables together. I have a telemarketing table and an appointment table. The idea is that when an appointment is made telemarketing should be done afterwards to the same company. Therefore I want to view all of the telemarketing done and all of the appointments done and then match companies which have had both an appointment and telemarketing. However when i do this if there has been more appointments made for one company that there has been telemarketing the telemarketing duplicates itself so that the two have the same amount done which is not what I want??? Does this make sense?? Can some one please help????

Thanks

View 5 Replies View Related

What Is Wrong In This Querie?

Jul 2, 2005

Hi everyone,

Now, I'm totally lost!
When I create a query with the Query Design view in Access with this SQL query:


SELECT T_Cryzout_Disponibles.LCLCL_Disponibles_Total, T_Cryzout_Disponibles.DateEmission, T_Cryzout_Disponibles.NomReceveur, T_Cryzout_Disponibles.Ordre
FROM T_Cryzout_Disponibles
WHERE (((T_Cryzout_Disponibles.LCLCL_Disponibles_Total) Like 'C*') AND ((T_Cryzout_Disponibles.DateEmission) Is Null) AND ((T_Cryzout_Disponibles.NomReceveur) Is Null Or (T_Cryzout_Disponibles.NomReceveur)=''))
ORDER BY T_Cryzout_Disponibles.Ordre;


everything is good, I get what I'm suppose to get. But When I'm doing it in VBA with this code:


RExtraction.Open "SELECT TOP " & NbrLCLCL & " LCLCL_Disponibles_Total FROM " & QuelleTable & " WHERE (((LCLCL_Disponibles_Total) like 'C*') AND ((DateEmission) Is Null) AND ((NomReceveur) Is Null)) ORDER BY Ordre", ConnectionBD, adOpenStatic, adLockOptimistic

RExtraction.MoveFirst


It gives me an error that says:
BOF or EOF is egal to True.

And I really don't know what is wrong with this. It is pretty much the same as the query I entered in the Query Design.

I'm really confuse.

Thank you in advance for your help!!!

View 1 Replies View Related

Another Date Querie

Aug 27, 2005

I'm trying to construct a query that will show all fields where the date is greater than today’s date less eight days, and also pick up fields which contain no data.

The first part I have achieved with the criteria >Now()-8

But I've been unable to find the correct syntax for picking up fields which contain no data.

View 2 Replies View Related

My Querie Keeps Duplicating

Nov 25, 2005

Hi

Please can some help me asap. I need to do a querie which joins two tables together. I have a telemarketing table and an appointment table. The idea is that when an appointment is made telemarketing should be done afterwards to the same company. Therefore I want to view all of the telemarketing done and all of the appointments done and then match companies which have had both an appointment and telemarketing. However when i do this if there has been more appointments made for one company that there has been telemarketing the telemarketing duplicates itself so that the two have the same amount done which is not what I want??? Does this make sense?? Can some one please help????

Thanks

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved