I've created some queries on some data that return summary information to then chart over time. The fields being returned are YearGroup, Academic Year, Term, TotalI want the data to get sorted by YearGroup (Year7, Year8, Year9 etc) however, when it sorts the data it sorts it starting with Year10, Year11, Year7, Year8, Year9.
View RepliesHi, okay completely new to access. I'm trying to create a chart based on a query with 1 record 8 fields. I can not figure out a way to rearrange the data into two fields 8 records.
The background is I have a hundred units, each with 8 wheels. I have a table with the following fields.
-Unit part number
-Diameter of wheel 1
-Diameter of wheel 2
-Diameter of wheel 3
-Diameter of wheel 4
-Diameter of wheel 5
-Diameter of wheel 6
-Diameter of wheel 7
-Diameter of wheel 8
So, the query tells me how many wheel diameters between a certain range, with the following fields:
- Diameter >530
- 530> Diameter >540
- 540> Diameter >550
- 550> Diameter >560
- 560> Diameter >570
- 570> Diameter >580
- 580> Diameter >590
- 590> Diameter >600
Now I want to plot the range on the x axis and # wheels in that range on the y axis of a bar chart.
Any suggestions on how I can achieve this? How do I create the diameter range as a record rather than a field?
Many thanks!!!
I am infuriated with MS Access now!!!!!
I am building a nice little system and now I am developing the reporting features. I want the results of a query to populate a chart.
Basically it is a monthly chart, so would have 12 months along the x axis and a quantity on the Y. A simple line chart!
I want 4 different lines each representing a form of occurrence. However Access limits you to using a maximum of 6 fields! Are you serious!!! I need 12 just for the months!!!!
Is this a serious feature of Access or am I being stupid?
I know many people who export a lot of stuff to Excel for charting, and now I know why. Is this my only option? I wanted to avoid multiple applications, but if I have to, will have to learn how to automate to an excel template, some thing I am not aware of how to do currently and am limited in my VB programming abilities.
I cannot believe that a powerful tool such as Access could be sooooo limiting in it's reporting capabilities!
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 RelatedI work at a program where we have individuals try out for a field (coming from various backgrounds). The program has been in place since 2012, and I wanted to start looking at the data for analysis and comparing trends, etc; I imported our master file (excel) into Access. It's in one big table, column headings include roster number, name, gender, type, FY (fiscal year), and finalcode (Select, Non-Select - but put it in as 1 or 2 in the field)).
I created a query, and was able to get data I was looking for.I then set it to Pivot Chart (bar graph).
Bottom (Believe its X) Axis: I have Type, Year.
Right Side of Chart (TypeCodeNumber)
Top of Chart: Count of CodeNumber
It gives me the totals (whether select or non-select) just fine, but I have been racking my brains, watched tons of YouTube videos and read a lot on how to make it give me a percent. I found a way to go Percent on the left side of the bar chart, but the Values go up to say 3300%. I know the problem is with the Count up top.
I have a union query with 2 fields: Order and Row_Heading_Full.
Code:
SELECT DummyClientType.Order, DummyClientType.Client_Type AS Row_Heading_Full
FROM DummyClientType
UNION SELECT ("25") AS [Order], ("Totals") AS Row_Heading_Full
FROM DummyClientType;
The purpose is to pull the Order and Row_Heading_Full fields from a table and add another entry that with "25" as the Order and "Totals" as the Row_Heading_Full at the end of the list.
I want it to be ordered sequentially based on the Order field. But instead it is ordered like this: 1, 10, 11, 12... 20, 21, 22... 3, 4, 5... In other words it is ordering only by the first digit and not by the number as a whole.
When I remove the union aspect it is ordered properly:
Code:
SELECT DummyClientType.Order, DummyClientType.Client_Type AS Row_Heading_Full
FROM DummyClientType;
But that defeats the purpose because I am not adding a final entry of "25" and "Totals".
This is something I occasionally see in Access and has been bugging me for quite a while.
As an example, when I have a table (all text fields except for the ID field which is an Autonumber with a unique index - ie just what Access creates when you import data) and I try to make a new table from a query by indexing the Autonumber field in descending order (ie to reverse the order of the table), it doesn't work properly.
So if I have:
SELECT [mytable].* INTO [mytable sorted] FROM [mytable] ORDER BY [mytable].[ID] DESC;
When I preview the data (ie run the select query to have a look at it), it looks fine.
When I change the query to a 'Make Table' and I then I check the table it makes, the order changes part-way down the list, so looking at the ID field it runs from number 2669 down to 2087 correctly, then it goes from 1960 to 1956, then 1803 to 1799, then 1751 to 1747, etc etc etc. After a while it seems to correct itself again, and orders normally down to #1
I'm using Access 2002.
I have a table with multi columns with unsorted data.
I want to run query to sort data in multiple columns.
How can i do it?
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?
I have this working query:
Code:
INSERT INTO TB_SISTEMAS ( LOGIN, SISTEMA, PERFIL, DATA )
SELECT Left([dbo_BACKUP_ACESSOS.LOGIN],255) AS LOGIN, dbo_BACKUP_ACESSOS.SISTEMA, Left([dbo_BACKUP_ACESSOS.PERFIL],255) AS PERFIL, dbo_BACKUP_ACESSOS.DATA
FROM dbo_BACKUP_ACESSOS
WHERE (((dbo_BACKUP_ACESSOS.SISTEMA)<>"ACTIVE DIRECTORY") AND ((dbo_BACKUP_ACESSOS.DATA)="2014-03-23"));
But Iwant to be able to use a set of data to be used in the Replace Statement, so I create a table to add each string I would like to have replaced by "nothing", and trying to make the replace query to look there in order to find what to replace.I also created a table where I will list the systems that I dont want in the select, so I removed the "ACTIVE DIRECTORY" and replaced by the colum that have the list of system I dont want listed.This is the result:
Code:
INSERT INTO TB_SISTEMAS ( LOGIN, SISTEMA, PERFIL, DATA )
SELECT Replace((Left([dbo_BACKUP_ACESSOS.LOGIN],255)),[PREFIXOS_E_SUFIXOS]![Valor],"") AS LOGIN, dbo_BACKUP_ACESSOS.SISTEMA, Left([dbo_BACKUP_ACESSOS.PERFIL],255) AS PERFIL, dbo_BACKUP_ACESSOS.DATA
FROM dbo_BACKUP_ACESSOS
WHERE (((dbo_BACKUP_ACESSOS.SISTEMA)<>[SISTEMAS_EXCLUIDOS]![Sistema]) AND ((dbo_BACKUP_ACESSOS.DATA)="2014-03-23"));
The thin is that this keeps asking me to enter the parameter value for "PREFIXOS_E_SUFIXOS!Valor" and for "SISTEMAS_EXCLUIDOS!Sistema"
I have a column containing an id that consists of the first two letters of a weekday followed by an incrementing number. For example, for Monday, I have "MoA1" "MoA2" "MoA3" ... "MoA11".
The problem is that when I sort my list, it is ordering it: "MoA1" "MoA10" "MoA11" "MoA2" "MoA3" etc. Currently, my order by property is set to
MID(TABLENAME.SORTFIELDNAME, 3, LEN(TABLENAME.SORTFIELDNAME))
From a performance perspective, does it matter in what order a number of clauses are specified ? For example if many records satisfy ConditionA but few records satisfy ConditionB, is it better to put ConditionB first ?
SELECT Fields FROM Table WHERE ConditionA and ConditionB
or
SELECT Fields FROM Table WHERE ConditionB and ConditionA
I have a query that is returning running conditions for a machine over multiple shifts. the table is set up like this
Code:
date shift parameter condition
09/13/13 1 front 500
09/13/13 1 middle 450
09/13/13 1 back 475
09/13/13 2 front 510
09/13/13 2 middle 460
09/13/13 2 back 490
this is just a small example. there are about 24 different parameters reported on in a shift..I want the query to sort by date ascending, shift ascending, and then by parameter in a specific custom that i define. I want it ordered "front, middle, back" for each shift. Again this is just an example, there would be 24 parameters I want in a specific order. Then do I need to define the sort order again for the report based on this query?
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.
I have a query that Counts the number of times a model number is used. I use totals and the count function under total to get that count. I have the top 10 models used and the query returns my information correctly but. When I make a form or report and place a chart in that uses this query, the results are in alphabetical order not in the order of usage. I need my chart to either go from descending or ascending order of usage and not by alphabetical order of the model number or name. What am I doing incorrectly? It seems like the chart would display the same way my datasheet would in the query.
Here is my sql statement.
SELECT TOP 10 Count(tbl_Closed_Jobs.OEM_Model) AS CountOfOEM_Model, [OEM] & " " & [Desc] AS Expr1, tbl_Closed_Jobs.Desc
FROM tbl_Closed_Jobs
GROUP BY [OEM] & " " & [Desc], tbl_Closed_Jobs.Desc, tbl_Closed_Jobs.OEM_Model, tbl_Closed_Jobs.OEM, tbl_Closed_Jobs.Plant
ORDER BY Count(tbl_Closed_Jobs.OEM_Model) DESC;
I have stored the costs in my database with a date they apply from so I have a series of costs which I can then refer back to using the order date to see what cost I should use at any one time. My table structure is as follows:
Code:
tblExtruderCosts
ExtruderID PK - identifies the extruder
FinishID FK - identifies the finish
Cost - the cost for that finish from that extruder
DateFrom - the date the cost applies from
tblExtHead
ID PK
ExtruderID FK
FinishID FK
Orderdate
I have a simple form with a text box, I enter a series of part numbers separated by newlines, and then I use the following code to query for each of the separate part numbers:
Code:
Dim strSql As String
Dim part_nums() As String
Dim num As Variant
Dim multivar As Boolean
multivar = False
[Code] ,......
This all works perfectly, but what I need to do is have the query return the records in the same order I entered the part numbers. They are automatically returned in alphabetical order by item_no, and when I enter 15+ part numbers it becomes slightly difficult to search through them all on the form to find the one I am looking for. It would be a lot easier if they were output in the same order that I type them in. Is there any way to make this happen?
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 a query with a float/delta column which is the expression:
Code:
Float: [Date1] - [Date2]
When I try to change the order by in the column filter drop-down i get a data type mismatch in criteria expression error.
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 am trying to use a join query to join two tables in order to create a form. I have done this on two other occasions in my database with no issues. Now it is only pulling the ID and I want it to pull the description. I have looked at the SQL view for the other queries that are similar as well as the design view and all of them are set up the same way with the exception that this new join query is pulling CertID rather than the CertDescription.
Is there any reason why it would be doing that? Is there an error that I could possibly be doing? It seems according to my notes that I have created all the queries the same but this last one does not pull the same info as the other queries.
I'm taking my first database class and I'm working on a hospital project in which I need to determine which pair (one doctor and one nurse) has a decremental performance as weeks go on.
I have an "r" and "s" queries.
These are the fields:
r: [DOCID,NURSEID, WEEK, RESULT]
s: [WEEK, RESULT]
I would like to find what pair had a "Good" result in week 1, "Average" in week 2, and "Bad" in week 3. My problem is that the results need to be in this specific order.
The professor told us to use a formula and I got the solution after building 6 queries that involved union, difference, and cartesian.
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 .
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?
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.
Hi all,
I want to create a 100% stacked column chart (as can be used in Excel) into a report in my database, however I cannot find it anywhere to use.
Does anyone know if it is possible to use this type of chart in Access and if so, how to do this?
Again, help would be greatly appreciated.
Thanks