Queries :: Sum Different Rows
May 3, 2013
I'm building a Bradford Scoring DB, everything is running smoothly apart from one niggle I would really like to get around and automate.Essentially, I'm hoping to run a difference sum query which is fine when it's on the same row, but the issue I'm having is that I'm trying to calculate this from another row, linked by name. This inturn will workout the difference between the two absence episodes i.e. it's been 80 days since their last absence end.
I did find a method which would view the previous AutoID, but this worked well for the whole table, but not within a query limited to name.The obvious workaround is to allow the team leaders to enter this manually, but this could be a/ open to small errors b/ mildly time consumingI, like most, work with people who want info NOW and with little effort.
View Replies
ADVERTISEMENT
Jan 22, 2015
How I might select all the records within a query except for the most recent one? I have an AutoNumber ID field and a date stamp to use , but I can't figure out how to exclude just the biggest record.I am trying to run an update query on the After Insert Event, so that when the user inserts a new record it will mark down similar older records as 'superceded'.
At the moment I can select all the records, and I can select just the largest one, but I can't seem to take one away form the other. Here is the SQL in it's current state:
Code:
UPDATE [Personnel-Qualifications] SET [Personnel-Qualifications].[Superceded?] = True
WHERE ((([Personnel-Qualifications].PersonnelID)=[Forms]![Personnel]![PersonnelID]) AND (([Personnel-Qualifications].QualificationID)=[Forms]![Personnel]![Child21]![qualificationID]));
View 1 Replies
View Related
Nov 13, 2013
I have a table with 4 columns: ID (1,2,3,4,...),Cases, Hours, RIR. In the RIR column I would like to use the following formula.I would like to use a query to Sum the last 12 rows (after index row 12 of course )
View 14 Replies
View Related
Mar 26, 2013
I have the following scenario and do not wish to use a crosstab for various reasons. I am combining three queries into one. The last query has information as follows:
Project Type Rev Budget
12345 Debt 100,000
23456 Impact 50,000
34567 Other 25,000
There are hundreds of these line items. The first two queries hold the project information as well and that would be the field that I would use to join each query. Currently the information above is in line form and I need to put it in column form to coincide with my other data. The final result needs to have the following column headings:
Project, Desc, Exp Budget, Expenses, Debt, Impact, Other, Rev Budget
Please note that the first four headings come from the first two queries so I have this taken care of. how do I change the query to put the Type by line item as a column heading and sum the Rev Budget accordingly without creating a table and doing appends or update queries? Can it be done in the query where I am combining the three queries?
View 2 Replies
View Related
Mar 16, 2007
Hi,
Is it possible to update multiple rows in a Table at one time using Update Query?
Ta
Kasey
View 5 Replies
View Related
Oct 2, 2013
How to insert x number of empty rows at once in a query? All fields allow null.
Say, if I wanted to "resize" the table to 3000 records...
View 6 Replies
View Related
May 12, 2013
So in my example you have a Weld (tblWelding_WeldingID) and each weld can have more than 1 consumable (tblconsumables_consumableID). have a third table that links the two, trackID,consumableID, weldingID)
What I need to be able to do in a report, is under the heading COnsumable, list the 1 or many consumables.
Also you select the consumable from a combo box..... if that just makes it that much harder...
View 2 Replies
View Related
Oct 16, 2013
I have a table which shows when a product is available in the stores.
For example.
Product Store Available
A NS Jan, 2013
A BC Marc, 2013
How can I combine the available/store field which just shows
Product Available:
A - Jan, 2013 in NS AND Mar, 2013 in BC.
View 1 Replies
View Related
Sep 23, 2014
I am trying to concatenate multiple rows into one record. I googled it and found many functions in VBA that do the job. However my problem is that my query will be linked directly to Excel file and then I get an error message saying that Excel could not recognise this function. I could potentially insert data into new table and link that table to Excel but I need to avoid end-user exposure to Access as much as possible.
So I am desperately trying to find a way of doing this without VBA code.
Example of data:
Customer Name |Order Number
Smith |O101
Brown |O102
Smith |O103
Green |O104
Brown |O105
I am trying to achieve below:
Customer Name |Order Number
Smith |O101, O103
Brown |O102, O105
Green |O104
View 14 Replies
View Related
Jul 7, 2013
Any way to join 6 rows into one and calculating average.
So I have temperature data in rows in time intervals of 10 minutes. I would like to join rows in one hour interval, and at the same time calculate average temperature in temperature field.
View 3 Replies
View Related
Apr 16, 2013
I am trying to get a total count of rows from a query on my tickets, Each ticket has a date and an ID number
Code:
SELECT tbl_ticket.ticket, tbl_ticket.entrydate, Count(tbl_ticket.[entrydate]) AS [Row Count]
FROM tbl_ticket
GROUP BY tbl_ticket.ticket, tbl_ticket.entrydate
HAVING (((tbl_ticket.entrydate) Between #1/1/2011# And #1/31/2011#));
I am trying to get this query built so I can attach it to a form.
I am trying to get three bits of data from this Query
1 a total count of all the Tickets in a given month
The ticket #'s and their date of entry.
So far this gives me a great list of Tickets and the date they got put in the system, but then for the total count it gives me 1 for each line. Even if some have the same date.
View 3 Replies
View Related
Apr 6, 2014
I need to change rows data into column.
I have a table name as Temp_Report1 with fields Lab_No, Test Name, Reslt, Test_master
Lab_no
Test Name
Result
Test_Master
4.414
Hemoglobin
00
HAEMATOLOGY
4.414
Red Cells
01
HAEMATOLOGY
4.414
E.S.R
02
HAEMATOLOGY
4.414
Reticulocytes
03
HAEMATOLOGY
etc
The another table where I want to copy data is New_repo2 with fields Lab_no, v1, v2, v3 etc. Need as
Lab_No
V1
V2
V3
V4
V5...
4.414
Hemoglobin
Red Cells
E.S.R
Reticulocytes
Etc
How to change this from rows to one column.
View 1 Replies
View Related
Nov 12, 2013
I have a form where we fill in information for supply of PPE to employees.
Each item must be signed for on a printed report.
I am encountering problems trying to create enough rows for my report detail for each signature of the number of items supplied.
For example, on the form I will select the "equipment" - 4 hats supplied and 3 boots. On the report I want the equipment set as the group and the detail to be a number or rows which equals the number of selected items. therefore under the Hats group heading I want 4 blank rows which are made up of 3 text boxes - Print Name, Signature & Date and another group heading for boots but with 3 lines.
The Query gives the results below: (Item name is linked to a PPE table containing Item ID, Item Name, Cost etc..)
IssueIDDateProvidedItem_Name AmountIssued
0001 01/11/2013 Gloves (Orange) 10
0001 01/11/2013 Hard Hats (Black) 2
0002 02/11/2013 Hi Viz (Large) 5
The report I aim to generate from this should look as attached ...
The query/report is set up as a parameter report so I will enter the issueID such as 0001 and only those items will appear on the report to print and sign.
View 4 Replies
View Related
Jun 10, 2013
I have a table where and account could have multiple rows with different data like Applied_Date and Trans_Code, and AMOUNT. The AMOUNT in two of the rows will be a positive and a negative and will be zero each other out. I am trying to create a query that will only return the the rows that do not offset each other. Here is an example of my table:
ACCOUNT CODE APPLIED_DATE AMOUNT
292020 M 5/11/2012 ($33.95)
292020 11 5/14/2012 $33.95
292020 A 5/30/2012 ($33.95)
View 3 Replies
View Related
Jan 20, 2015
I have a file structured as listed below. I need to pivot Patient Id, Patient Name, Send Reason, Provider Name and Visit Number into columns and then insert the values into the corresponding columns.
My data set contains about 1,000 rows, so I cannot simply paste special in Excel.
Patient ID 111111
Patient Name Doe, Jane
Send Reason Cannot resolve provider
Provider Name Doe, John
Visit Number ABCD#F1234567
I have tried Pivot and Transform, neither of which seems to work.
View 13 Replies
View Related
Dec 8, 2013
I have a query with years and sales column. I need to make a column that displays the difference in sales between years. My query is named "Qry" if that is needed. "Year" and "Sales" are the column titles if you need that for the SQL.
View 14 Replies
View Related
Oct 11, 2013
I am designing a bead ornament database for my mom to track inventory of beads, inventory of finished ornaments and cost/pricing. I have a table that joins the Item and bead part with the quantity of each bead part needed, it has the following fields: ItemMatlID, ItemID, BeadPartID, QtySo I have multiple rows of ItemIDs for all the BeadPartID & Qtys.Now I need a query with 1 line per Item and all BeadParts and their quantities. However, I need the BeadPart to be a row data and not a column heading. So a crosstab is out, I believe since it wants to make the BeadPart the column heading and not data in the query.
View 7 Replies
View Related
Sep 16, 2014
I have data like:
Code:
SubjectID VisitID VisitNum VisitDate VisitDetail Rspns
1 5 1 2/10/2001 Fever_1 Yes
1 5 1 2/10/2001 Age_1 12
2 7 1 5/08/2010 Fever_1 No
2 7 1 5/08/2010 Age_1 18
2 8 2 9/30/2010 Fever_2 Yes
2 8 2 9/30/2010 Age_2 18
etc.
I need to convert it to a non-normalized wide format, like this:
Code:
SubjectID VisitDate_1 Fever_1 Age_1 VisitDate_2 Fever_2 Age_2
1 2/10/2001 Yes 12
2 5/08/2010 No 18 9/30/2010 Yes 18
etc.
If I didn't have to worry about the VisitDate, a simple crosstab would do the trick:
Code:
TRANSFORM First(MyTable.Rspns) AS FirstOfRspns
SELECT MyTable.PatientID
FROM MyTable
GROUP BY MyTable.PatientID
PIVOT MyTable.VisitDetail;
View 1 Replies
View Related
Apr 26, 2013
I am creating a query to use in a Word 2007 mail merge.
For simplicity, let's say the query has three fields:
schname for school name, role for staff role, and name for the staff member's name.
The current query shows:
SchoolA, Admin, Jerry Garcia
SchoolA, President, Bob Hope
SchoolB, Admin, Janet Jackson
SchoolB, President, Mae West
I would like a query that would show one line per school like so:
SchoolA, Jerry Garcia, Bob Hope
SchoolB, Janet Jackson, Mae West
I've looked at Allen Browne's ConCatRelated function and can pull up a list of all names in a field called Expr1001, but that doesn't seem to be what I need.
View 5 Replies
View Related
Dec 22, 2014
I have a table with multiple rows per member that I would like to convert to 1 row per member. I have a table that looks a bit like this:
Member_IDDiagnosis
10000Hypothermia
10000Frost Bite
10001Fatigue
10001Dehydration
10001Exhaustion
99999Exhaustion
99999Hypothermia
And I'd like to convert it to this:
Member_IDDiagnosis - 1Diagnosis - 2Diagnosis - 3Diagnosis - 4
10000 HypothermiaFrost Bite
10001 Fatigue DehydrationExhaustion
99999 Exhaustion Hypothermia
The columns don't line up well but I am looking for each diagnosis to move into one of the 4 columns, depending on whether it is the first, second, third or fourth diagnosis associated with the member.
View 3 Replies
View Related
Feb 6, 2014
i have 3 simple tables as follows:
Table1:
OwnerID, Owner_LastName,
Table2:
TenantID, Tenant_LastName,
Table3
ID, Date, Amount, OwnerID, TenantID
I need query to display ALL the rows from Table3 and show columns of Owner_LastName and Tenant_Lastname. However, I want the rows in Table3, that do not have the value for TenantID to still appear, just with Tenant_LastName being left blank.
View 3 Replies
View Related
Jun 16, 2015
From a Access database that I inherited. Users used to be able (from a form) run a query and then add data (i.e, new rows). But now that the the database is split and the backend is on MS-SQL, they no longer can do this. I do not think this will be possible. I think they will have to add data directly to the table or have another form for adding data.
View 3 Replies
View Related
Jun 2, 2013
I'm doing a subquery to select the top 5 of products for each supplier. The selection needs to be done randomly on the products for each supplier. For this I have made the following query (based on Allen Browne's example):
SELECT tblProducts_temp.SupplierID, tblProducts_temp.GTIN
FROM tblProducts_temp
GROUP BY tblProducts_temp.SupplierID, tblProducts_temp.GTIN
HAVING (((tblProducts_temp.GTIN) In (SELECT TOP 5 Dupe.GTIN
FROM tblProducts_temp AS Dupe
WHERE Dupe.SupplierID = tblProducts_temp.SupplierID
ORDER BY RND(Dupe.GTIN) DESC)))
ORDER BY tblProducts_temp.SupplierID, tblProducts_temp.GTIN;
The query returns a random number of products, but not a top 5. So for supplier X one time 3 products and the next time for supplier X 7 products.The query without the RND function, so just the top 5 works fine:
SELECT tblProducts_temp.SupplierID, tblProducts_temp.GTIN
FROM tblProducts_temp
GROUP BY tblProducts_temp.SupplierID, tblProducts_temp.GTIN
HAVING (((tblProducts_temp.GTIN) In (SELECT TOP 5 Dupe.GTIN
FROM tblProducts_temp AS Dupe
WHERE Dupe.SupplierID = tblProducts_temp.SupplierID
ORDER BY Dupe.GTIN DESC)))
ORDER BY tblProducts_temp.SupplierID, tblProducts_temp.GTIN;
View 2 Replies
View Related
May 20, 2013
I am a beginner in SQL, I have a problem regarding combining rows/ columns having value. Below is my example:
Table1:
1 123 23 43
1 234 3 431
2 34 34 34
2 54 3 123
3 1 32 32
2 341 1 32
Output Table:
1 123 23 43 234 3 431
2 34 34 34 54 3 123 341 1 32
3 1 32 32
or instead of combining rows, getting the result by combining columns with the same condition.How can I obtain such result?
View 3 Replies
View Related
Jul 24, 2013
I am trying to connect individual responses to a survey to a series of variables describing each environment where the survey was conducted. I have about 1,400 individual survey responses (of about 34 questions each that have been separated into columns) and about 42 environments that are described with 30 different criteria each. They are matched by the environment description
- A line for each respondent and their survey answers
- On that same line would be the criteria of each environment.
Thus, the environment criteria should be repeated several times..However, when I join the fields in a query and require both fields to be equal, When I prioritize the environment panel, I only receive 42 records and the survey responses are blank. Prioritizing the survey panel gives 1,400 rows, but omits the environment info.
how do I populate the environment information variables on each row of the survey responses, even if that information duplicates the same variables on other rows?
View 8 Replies
View Related
Jun 18, 2013
Is there a way to create a query that will transpose table columns into rows:
Genes Cytoband
xxxx yyyyyy
xxx yyyyyy
xxx yyyyyy
xxxxxx yyyyyy
xxxx yyyyyy
xxxxxxx yyyyyyy
xxxxxxx yyyyy
xxx yyyyyy
xxxx yyyyy
xxxxxxx yyyyyyy
xxxx yyyy
xxxxxx yyyyyy
xxxx yyyyyy
xxxxxxx yyyyyy
Is there a way to display the table in a single row separated by commas:
xxxx yyyyy, xxxx yyyyyyy, xxxxxxx yyyyyyyy, xxxxx yyyyyyy
View 11 Replies
View Related
