Hi,
I'm a newbie to Access. I have created a query showing the result base on other calculation. The returned results are in numeric. I would like to know how to sum up the returned results on row to a new column.
A b new column
100 200 300
200 150 350
Your kind help will be highly appreciated
I was able to use the UNION ALL qry. But, when I have another file (like original2) that does NOT have all the columns listed in the UNION ALL qry, I get a Parameter value box asking for the missing columns when I run the qry.
Example:
original1IDDateGroupChristianJohnnySteve 18/5/2013A1528/5/2013B338/5/2013C2348/5/2013D2358/5/2013E5
original 2IDDateGroupChristianJohnny18/6/2013A212528/6/2013B2338/6/2013C2248/6/2013D22
The UNION ALL qry includes all the possible resources ( includes all the possible column fields Christan, Johnny, and Steve).
When I run the UNION ALL qry with the original2 file, An "Enter Parameter Value" box is displayed with the mssing column name "Steve".
Is there a way to Map the original2 table into a working table with all the columns, or use VBA code to construct the UNION ALL qry to only include the existing columns? My data has variable columns and I'm trying to avoid the parameter popups.
I have a MS ACCESS 2010 database with a data table which i am trying to create a query from. I have 6 columns of data( one with an ID Field and 5 Name Fields). Below i have made examples of how it first appears as a simple query and the second will show you what i would like it to look like.
What the simple query looks like: [URL] ...
Second what I want the query to look like: [URL] ....
Hi everyone....
I have imported a data sheet into Access that had certain columns a different color...green...they were colored so that the end user would
know which columns were to have input. (visually)
Is there anyway that I can keep this format or color the columns in Access?
Also the end result is from a Query...does that make a difference ?
Thanks....
Hi all,
This is a strange one. I have a VERY simple query that runs directly off a single table which contains about 7 fields and one PK. I am pulling through 4 columns from this table including the primary key. I am renaming the columns using the format "NewName: Record_Id" format. I have also tried changing the caption on the query column properties.
Basically, the problem is that I save and close the working query but when I either close the database down or make a change to another form or query, the column names all get changed to a single value of the table, currently "Report Title". When the query is run, all the columns are shown with the same header name and the data results are all the same.
I have checked my relationships and they are fine and I have also done a compact/repair but without any luck. Can anyone please help me?
Thank you.
Gareth
Could anyone tell me if there's a hardcoded maximum number of fields/columns in a query?
I ask, because through some experimentation and the urging of others on the boards, I've finally taken a look at the SQL view of a database and found it rather easy to understand. There, I found I had more flexibility filtering a Query via SQL in comparison to the query design window. (unlimited "Or" vs. 8).
So, even though there's a definite number of columns in the Query Design window, can I ignore than and just enter what I need in the SQL view?
Hi folks.
I've been looking for an easy way to have 12 columns in my query which are each of the 12 months and are all using the same date field "dateSent". So i'd want to just use the month part. within the actual columns there will be a disbursal field which will tell a user how much money has been sent (if any) on that month. then If done correclty, it could be analysed in Excel.
I've attached an example of what is required of me. I'm expected to have the same format but i'm unsure how I'm going to go about this.
I've attached an example. Maybe someone might know a good way to do this and perhaps point me in the right direction
I appreciate all help
Thanks.
I have a problem trying to sum a numeric column based off another column in the same table.
What I am trying to accomplish is to sum a numeric column if a corresponding column is Not Null.
I have uploaded my sample DB that to illustrate my problem. The query I am testing with is called Query1 and I am looking at summing point values from the task table grouped by the test case those tasks are in. I can easily sum the total number of points for each test case. But when I add another field to the query to sum the point values of the task in the test case that have a date entered into its corresponding date field it will not sum them correctly.
The result I get is the same sum total is shown for all three fields in the query.
I have tried several attempts at making criteria for this to work but nothing seems to work the way I want it too.
Here are my attempts:
IIf(Not (Sum([Task].[Points]))=IsNull("Attempted_Actual"),Sum([Task].[Points]),0)
IIf(Not (Sum([Task].[Points]))=IsNull("Completed_Actual"),Sum([Task].[Points]),0)
Not IsNull("Attempted_Actual")
Not IsNull("Completed_Actual")
My question is:
Is it possible to do want I want all in one select query or would this be something to code in a VBA module and then just call that module in a report?
My plan was to do this all in a query then build a report to display this query.
I have attached my dummyDB file and you can look at the Test_Case Table and expand each test case to see the data I have populated.
Any guidance would be appreciated.
Thanks,
Hi. I am trying to do a query on a table that has 3 columns
ID, Person1, Person2
I want to query the data to find out how many rows each person's name appears in either Person1 or Person2.
I don't need seperate counts for when each name appears in Person1 or Person2, just the total for each person.
Result example that I'm looking for:
If table contained:
1 Eddie Sam
2 Pete Max
3 Sam Pete
4 Dave Eddie
Query to look like:
PersonsName Total
Eddie 2
Sam 2
Pete 2
Max 1
Dave 1
Surely this is easy to do?
Anyone? Thanks. Martin
I run a query with several columns. The first column in that query is full of individual names; about 50. I have another spreadsheet with three names. I used to be able to set a criteria or a filter (whatever you want to call it) in a query that when "Run" would only return to me the names from the spreadsheet with 3. Not a drop box or a pop up where I have to fill out the name, just a spreadsheet with all the transactions that occurred with these specific three individuals.
View 1 Replies View RelatedI have a text file that I will be importing into a new table once a week (every week) that I will need to add about 30 more columns to before I export it to a new text file in preparation for importing it into another Access application.
Is there a way through a query to add the columns without having to open the table and manually add them each week?
Thanks,
David
Hi, everyone. I've been reading this forum for several days now and the information I have picked up here has been wonderful. Thanks. Unfortunately, I haven't been able to find what I'm looking for on a problem I'm having. I apologize for the long post, but I wanted to include as much information as possible. Thanks for taking the time to look at my problem.
My Background: I am a Computer Managed Maintenance System Planner for a foundry. I am responsible for the administration of all maintenance and repair records for the plant’s mobile equipment (dump trucks, fork lifts, etc.) I’ve been using Access as a report writer for several years. I have pretty good understanding of report writing and basic query writing, although I have never used pivot tables or crosstab queries, and I have only used amend/update queries a few times. I have a novice’s understanding of relational databases and I recently picked up a book on VB for MS Applications and have been devouring it. The rest of my limited VB knowledge comes from reading these forums, so my programming knowledge is still in its infancy.
What I’m Using: I am on a network that uses Windows 2K, to which I have only regular user privileges. We use an Oracle database (it is used for everything in the plant) with a third party CMMS. I use Access 2002 to do my report writing. I have a small database set up that contains links to the necessary tables in Oracle and a few tables I created to help “massage” my data for queries and reports.
My Problem: Our CMMS app is only set up to retain the current and the previous hour meter/mileage readings. I need to be able to retain all my hour/mileage readings, however, so that I can track up/down time, utilization, etc., over several different periods of time, including years.
What I Want To Be Able To Do: I want to archive my hour/cycle readings in a table so that I can reference them for later queries and reports. A couple of typical questions I want to be able to ask of this information are
1)“What was the last known hour/mileage reading for this/all equipment on <date>?”
2)“How many hours/miles have passed between <date1> and <date2>?”
What I Have Done So Far: I’ve done research into crosstab queries and pivot tables. I don’t fully understand these, but they don’t seem to be what I’m looking for. I could be wrong, though. Then I got to thinking that the easiest thing for me to do might be to make a button on my main form that would run a query to append the hour data from the db to a table I made to store the info (tblMeterArchive). The table would have a column for the Equipment# and then the append query would create a new column with today’s date as the heading and dump the data into it. The problem is I can’t figure out how to get a query to add a column to a table and stick today’s date in the heading. I’ve searched these forums and I’ve done a Google, to no avail. I have even taken time to just start going through every thread to see if this sort of thing has been discussed before. So far I haven’t found anything and my boss is getting a bit peeved at the hours I have spent working on this so far this week.
So, my question is can an append query create new columns in a table? Or, am I barking up the wrong the tree and there is a better/easier to accomplish my goals? Even a link or pointer to the proper way to search for the information I need would be much appreciated.
Thanks again for taking the time to look at my problem.
Is it possible to total columns in a query? Right now, I have a query that produces the following column counts, but I'd like to total Pending, Overdue, etc. This data is being displayed in a subform.
Process Pending Overdue Total
-------------------------------------
Engineering 1 2 3
Procurement 0 6 6
<etc>
-------------------------------------
TOTAL 1 8 9 <- this is the line I want to add
Here's what the query (qryStatusRptB) looks like thus far:
Field: Process
Table: tblProcesses
Total: Group By
Field: Pending
Table: qryStatusRptA
Total: Sum
Field: Overdue
Table: queryStatusRptA
Total: Sum
Field: TotalRecords: Count(*)
Table:
Total: Expression
Can someone explain how to get the TOTAL ROW in here? (I can do it via another query, but that won't work since the data is displayed in a subform. I've tried crosstabs without success.)
Many thanks,
Christine
Hello! I'm new here, and I'm back into Access after a few years of not using it.
What I'm trying to do seems simple, but I can't seem to get the sytax down.
I have 6 columns that have dollar figures in them. I want to get the totals for all 6 columns to show in one field. I'm using the sum([field]) to total the individual columns, but I can't seem to get all of them together in one sum. Is this possible?
Thanks!! :D
hi there
how many columns is the maximum in a query?, because i have one that has 26 columns , but i need some more... what can i do?? is there a way to add some empty columns to a query??
thanks
Hi,
I have 2 tables, one "new" and the other one called "old".
The thing is that the "new" one has more information (more columns) than the "old". So I can only do a Union query on the columns that do match, but it is possible to have the information from the columns in the "new" table added as well into that query?
Do I have to create the new columns in the "old" table before the Union query?
BR
Why is my query limiting itself to only 16 columns? when I tab right for another field, it jumps back to the first field. I have other queries with far more columns than that. My DB size is 3,400 kb.
View 3 Replies View RelatedI have two calculated columns in my query for example... table.num1 - table.num2 as "Col1" and table.num1 - table.num3 as "Col2"
I then want another column equal to:
Col1 - Col2
I know that I could say:
(table.num1 - table.num2) - (table.num1 - table.num3)
but I am wondering if there is an easy way to just take two calculated columns of the query to use in the calculation of another column.
doing something like:
Col3: Col1 - Col2
doesnt work because it doesnt see Col1 and Col2 within the tables.
I have several tables that I need to join together to create a single form for multiple entries. Job Table, OPR Table, Organization, Program List Table, Program Notes, Rank, Reference Table, and Status Table.
Ultimately, I need to be able to display all of these in one form and allow for adding/editing notes from the Program Notes section. I also need to be able to let the user look up all info by selecting the Program Name and have the other field populate correctly. The issue I am running into is that I cannot get the Program Notes table to join to the Program List table correctly.
This is what I attempted to use in SQL:
SELECT [Program Name], [Status], [Reference], [Self-Inspection], [IG Checklist], [Continuity Book], [Bragging Paper], [Program Strengths], [ORI Reports], [Best Practices]
FROM [Program List Table]
UNION
SELECT [Log Date], [Log Entry]
FROM [Program Notes]
However, I keep getting an error. I have read that it is because I don't have the same amount of columns, but the tables don't have the same information.I have also tried to do it through a normal query and through a third table, however I get errors about ambiguous outer joins and I can't seem to make that work either.
Hi guys, I am trying to create a query collecting data from 4 tables as well
as calculating totals delivered but it's hurting my head trying to work out
all the joins can anyone help please?
The 4 tables are as follows:
Orders
=====
OrderNumber
CustomerName
CustomersRef
OrderDetails
=========
Order_Items_ID
OrderNumber
ItemNumber
Qty
ItemType
Description
Deliveries
=======
DeliveryNoteID
OrderNumber
Date
DeliveryDetails
==========
DeliveryDetailID
DeliveryNoteID
ItemNumber
QtyDelivered
The relationships are pretty straight forward, OrderNumber on Orders and
OrderDetails are linked as are DeliveryNoteID on Deliveries and Delivery
Details.
I want the query to list every single item for all orders as well calculating
how the quantity remaining to be delivered based on deliveries so far and Qty ordered.
So I want to create a report that pretty much just displays:
Orders.OrderNumber - Orders.CustomerName - Orders.Customer_Ref -
OrdersDetails.ItemNumber - OrdersDetails.Qty - OrdersDetails.ItemType -
OrdersDetails.ItemDescription - Deliveries.DeliveryNoteID - Deliveries.Date -
DeliveryDetails.TotalQuantityDelivered
Can this be done as one query or do I need to join two queries together?
It's hurting my head :(
Hi,
I am trying to compare two columns' values within the same QUERY table, but I kept on getting the "Data type mismatch in criteria expression" error. What am I doing wrong?
Here is my Select Query Statement:
SELECT qryDedparmDedetail.EMP_ID, qryDedparmDedetail.[Employer Amt], qryDedparmDedetail.[Employer Actl], qryDedparmDedetail.[Admin Amt], qryDedparmDedetail.[Admin Actl], qryDedparmDedetail.[Employee Actl], qryDedparmDedetail.[Employee Amt], qryDedparmDedetail.FirstOfSTATUS, qryDedparmDedetail.FirstOfAGENCY, qryDedparmDedetail.FirstOfTITLE, qryDedparmDedetail.FirstOfFORMAT_NM, qryDedparmDedetail.RepUnit, qryDedparmDedetail.FirstOfDEDTYPE_CD1 AS Expr1, qryDedparmDedetail.SumOfNBR, RepUnit.REPUNITDESC, qryDedparmDedetail.LeftType
FROM qryDedparmDedetail LEFT JOIN RepUnit ON qryDedparmDedetail.RepUnit = RepUnit.REPUNIT
WHERE (((qryDedparmDedetail.[Employer Amt])<>"Employer Actl") AND ((qryDedparmDedetail.LeftType)="01"));
I have a table with 4 columns :
Product Price Quantity Supplier
Product1 5 240 A
Product1 7 19 B
Product1 6 12 C
Product2 96 0 A
Product2 98 23 B
Product2 99 44 C
There are 3 suppliers for the products (name of the suppliers are A, B and C).I want to make a query with the following result :
Product1 5 240 A
Product2 98 23 B
In other words :Showing a grouped list (grouped by products), with the lowest price of the supplier who has stock (quantity >0).I can make a list of grouped products with the lowest price, but it's not possible for me to show the stock and the supplier that's related with it.
I have a database that tracks students and their test scores. I am trying to figure out a way to where it will pull the student's latest test score and compare it to see if they fall within standards. So far I have 3 tables.
tblStudents
studentID
studentName
studentEmail
tblResults
resultID
resultStudent
resultDate
resultTest
resultScore
tblTests
testID
testName
testPassScore
The query I have written looks like this: SELECT tblStudents.studentID, Max(tblResults.resultDate) FROM tblStudents LEFT JOIN tblResults ON tblStudents.studentID = tblResults.resultStudent GROUP BY tblStudents.studentID." The data pulls just fine at this point. What I'm trying to figure out is how to then add more columns to this query to start doing comparisons. When I try to add more columns it tries to use them for additional grouping and adds many more records.
I'm looking for an expression or SQL for use in a query that will count the number of columns in another query. I do not need to count the records, I just need to know how many columns. I can't seem to locate a reference to a column count - everything points me to record count.
View 13 Replies View RelatedStudent ID
Command over subject
Teaching Subject
Explaining things
Pedagogy Methods
Solicited_Participation_Class
1
Good
Average
Bad
Good
Bad
2
Bad
Average
Good
Bad
Good
3
[code]......
I have a table given above, table name is student. Which has following sample data.
I want to count the no. of students who say Good, Average ,Bad for every indicator Output from query:
Good
Average
Bad
Command over subject
2
3
1
Teaching Subject
1
5
0
[code]......
How this can be achived from query in MS Access 2010
I have a table tblItemSold
ItemNum Date Sold
1111 Aug-10-2014 25
1111 Aug-9-2014 24
1111 Aug-8-2014 23
2222 Aug-10-2014 11
2222 Aug-9-2014 12
2222 Aug-8-2014 13
3333 Aug-10-2014 5
3333 Aug-8-2014 3
I want to write a query and transform above into
ItemNum Aug-10-2014 Aug-9-2014 Aug-8-2014
1111 25 24 23
2222 11 12 13
3333 5 3