Group Columns Under One Title
Jun 23, 2007
This is the table:
ID | Item | 2/2/07 | 3/2/07 | 5/3/07 | 7/3/07
---------------------------------------------------------
101Tap10152010
102Ply25201010
Note that 2/2/07 till 7/3/07 are field names having purchase quantity.
Now I want the query to show something like this:
ID Item <----Feb------> <-----Mar------>
2/2/07 | 3/2/07 | 5/3/07 | 7/3/07
---------------------------------------------------------
101Tap10152010
102Ply25201010
I want the two columns 2/2/07 & 3/2/07 to be combined under one field name 'Feb'. Similarly for 'Mar', combine 5/3/07 & 7/3/07.
Open attachments to have a clearer picture.
Please help me out over here.
View Replies
ADVERTISEMENT
Sep 17, 2007
Folks,
I need your help on GROUP BY clause. I have been struggling to get his report in a way I wanted for some time now. Here's what I like to do:
I am writing some project related data into a temp table (tblAppendForRskTblReport) by clicking a command button on a form. The data in tblAppendForRskTblReport looks as shown in the excel file tblFile1.xls (attached).
I wanted to make a report in Access 2003 which looks like another excel file rptFile2.xls(attached). As noticed, I wanted group the records into two main groups based on column "fCurrentlyOffered". Then for one group which reads "Yes" (column "fCurrentlyOffered") divided into several sub-groups based on column "chrCategoryID1" like Category A, category B, ....
I tried to use the following query:
SELECT A.chrProjectName, A.chrAqcsPropNum, A.memGuranItem, A.memFuel, A.memOpertgCondn, A.memPred, A.memGuar, A.memMargin, A.memCustReqst, A.memPermitValues, A.memRiskLevel, A.memLDs, A.memBasis, A.memCorrectAction, A.memComments, A.intCategoryRowID, A.chrCategoryID2, A.fCurrentlyOffered, Count(*) AS RecordCount
FROM tblAppendForRskTblReport AS A
GROUP BY A.chrCategoryID1, A.fCurrentlyOffered;
I could get a report based on column "chrCategoryID1" only (see attached sample excel file rptFile3.xls), but don’t know how to separate the rows (as new group called ‘Not Offered’) which reads 'No' under the column "fCurrentlyOffered".
Any attempt I make by modifying the above SQL leads to an error msg as follows:
"You tried to execute a query that does not include the specified expression 'chrProjectName' as part of an aggregate funtion."
I have Count(*) as an aggregate funtion in my SQL but I don't know why it's still complaining?
My question is: can I get the report as I mentioned above (as shown on rptFile2.exls) using SQL or do I need to handle this via VBA?
Can someone help me on this or offer any tips/idea?
PS: Some of the column data may looks like redundant info (normalization), but they are purposely replaced with 'deleted' or 'N/A' words for privacy.
Thanks/Shan.
View 1 Replies
View Related
Jan 17, 2007
SELECT contractid, min(RTRbalance) AS minRB
FROM ACHACT
GROUP BY contractid;
will have two culumn
What if I want to select all columns of ACHACT associated with minEB in each contractid group?
THX.
Jeff
View 1 Replies
View Related
Jan 15, 2014
I have a table that looks something like this in structure:
P_date (date)
Location_No (integer)
Invoice_type_A (integer)
Invoice_type_B (integer)
Invoice_type_C (integer)
Invoice_type_D (integer)
I want to create a query which shows the sum of the number of all four invoice types for a specific date regardless of the Location_No. By putting something like "Invoices_total: Invoice_type_A + Invoice_type_B + Invoice_type_C + Invoice_type_D" in the top field I am able to get the query to show me the total number of invoices per date AND location, but I would like the total number of invoices for a given date regardless of location number. How can this be done?
View 3 Replies
View Related
Sep 2, 2013
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.
View 2 Replies
View Related
Mar 12, 2013
I have would like to have a option group requery a list box with 2 yes/no columns as the criteria.
First Option = No criteria
Second Option = Yes in first column only
Third Option = Yes in second column only
So far I've been able to get option 1 and 2 to work, but I've not been able to define the same value to the third option, some of the values in the 2 columns over lap so using a single option doesn't seem to be the right method.
View 1 Replies
View Related
Oct 24, 2014
I have 30+ columns each table I need to change into sum of.. but its default is group by, and need to change multiple columns to sum - rather than manually each one..
View 1 Replies
View Related
May 29, 2015
Despite Google I can't seem to figure this out.
I have some data in a format similar to:
Name / Style / description / speed / distance
john / driver / careful / 80 / 5500
mary / driver / careful / 70 / 7000
pat / racer / reckless / 100 / 6000
anne / driver / careful / 75 / 1000
peter / racer / reckless / 110 / 6500
don / snail / slow / 60 / 6000
I want my report to total by style, without details and to look like:
driver careful 13500
racer reckless 12500
snail slow 6000
How do you get a report to sum the group items by a specific item and to hide the details of that group summing?
View 2 Replies
View Related
Mar 28, 2013
Is there a way to have an expression in the control source of a text box in a report, that re-starts or is exclusive for every group within the report?
View 5 Replies
View Related
Apr 3, 2008
I will try to explain what I have, but for a little more information please see the thread where I was trying to get a single instance of this query to run: http://www.access-programmers.co.uk/forums/showthread.php?t=146582
This above thread was to allow users to select a valid month from a "quota" table of data to compile work data for that particular month. The way this all works is I delete a history table, append the data from my work table, and then add the data from a quota table. Each time a user runs this report, those three queries run dependant on the month selected. At the bottom of this report I sum up the columns to give an overall total for production numbers. The table is set up as:
For MMM YY (selected)
Item........Repaired......Quota
widget1........1...............3
widget2........2...............0
widget3........6...............4
widget4........2...............2
Now what I need to be able to do is to retrieve all these totals at once; so that the numbers can be graphed by month. This history table that is built is not perminantly stored; since all the 'data' for it is contained throughout a couple other tables. I didn't see a reason to need to store 'all' that data again since it could be retrieved as needed.
Is it possible to write a query that will run this process for all distinct dates in the quota table and sum the data for repairs and quotas? Something like this:
Date.........Repairs........Quota
MMM YY.......18..............8
MMM YY.......27..............10
MMM YY.......16..............18
Or have I bitten off more than I can chew :)
View 7 Replies
View Related
Oct 9, 2006
Hi,
I want to put a value from a field in a table in the Title Bar.
So far i have done (Looking at 2 ways to create, 1 with module below or directly in the form, only just lloking at the module way so is not finished as i am waiting to get it to work directly first):
Module
Option Compare Database
Function ChangeTitle()
Dim dbs As DAO.Database
Dim prp As DAO.Property
Const conPropNotFoundError = 3270
On Error GoTo ErrorHandler
' Return Database variable pointing to current database.
Set dbs = CurrentDb
' Change title bar.
dbs.Properties!AppTitle = "DYSMS " & (SoftwareVersion.tblSoftwareInformation)
' Update title bar on screen.
Application.RefreshTitleBar
Exit Function
ErrorHandler:
If Err.Number = conPropNotFoundError Then
Set prp = dbs.CreateProperty("AppTitle", dbText, _
SoftwareVersion.tblSoftwareInformation)
dbs.Properties.Append prp
Else
MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
End If
Resume Next
End Function
Directly
Does not work
Dim stAppTitle As String
stAppTitle = "DYSMS " & (SoftwareVersion.tblSoftwareInformation)
Me.Caption = stAppTitle
Does work
Dim stAppTitle As String
stAppTitle = "DYSMS " & (Now())
Me.Caption = stAppTitle
So need to declare the SoftwareVersion field i guess??
Thanks
Dan
View 4 Replies
View Related
Apr 17, 2007
Hello!
In my form I have the title of a CD which is under the name of 'CD_Title' which is taken from my CD's table and I was wondering how to show it up in a Label so when I flick through the albums the Label changes to the album that it is selected!
I need to know...please help me!
:(
View 3 Replies
View Related
Mar 10, 2006
For Access 2003 - is there a way to stack one word on top of another when you rename a column? So, instead of having two words side by side for a column title, is there a way to put one on top of the other?
For example - change from this "today's date" to "today's
date"
Thanks
View 1 Replies
View Related
Feb 23, 2005
This should hopefully be an easy one for somebody:
How can I not display the title of any opened form at the very top of the screen. I would prefer to have the switchboard title remaining visible instead of the form title.
For example I have a form called 'frmPC' so the top of the screen shows 'frmPC : Form' when the form is open.
Anyone?
Much appreciated,
Paul
View 2 Replies
View Related
Mar 21, 2005
i have a form, and i would like that on one of the fields, as soon as i type something in it when i leave the field, i want it to automatically convert it to title case. can this be done please?
Cheers
View 1 Replies
View Related
Mar 25, 2005
I know that you can change the title of your application in the startup options in Access.
But can it also be done by vba code at runtime?
View 1 Replies
View Related
May 30, 2007
I have an *.mdb file with a bunch of tables, queries, forms, macro's etc. The file is named "xxx" but the display in the top left hand corner of the access window (next to the red key) is "yyy". I've looked everywhere and can't seem to find where this information is stored - as I need to change the "yyy" to match the filename "xxx" (the *.mdb was originally used for something else). Anyone know where this info is stored or how to change it?
Thanks in advance to those who respond!
View 3 Replies
View Related
Apr 23, 2008
I have a cross_tab query, that users print on a monthly basis.
The user selects whether they want to include data for one or all of three products. So the query could show data for example for Salmon only.
I need to be able to show on the printed query, what products it is displaying for. At the moment the query is called "TotalxProccessor_Product". Once the query is printed there is no way to tell if it is for Mussels, Salmon, Oysters or all three products.
Is there an easy way to manipulate the title the query prints our with?
Or do I have to go through a report to have control?
View 1 Replies
View Related
Mar 11, 2005
Does anyone know how to put in a file reference number automatically into the subject bar of an email?
This is the code that i am currently using:
On Error GoTo Err_Handler
MsgBox "THIS FEATURE WILL ONLY EXPORT FILES WITH TODAYS REFDATE"
If [CboExport] = "Today" Then
Dim rst As Recordset
Dim db As Database
Set db = CurrentDb
'Create Recordset
Set rst = db.OpenRecordset("SELECT tblAT.FileNr, tblAT.RefDate " & _
"FROM tblAT " & _
"WHERE (((tblAT.RefDate)=Date()));", dbOpenDynaset)
'Check to see if there has been records recorded today
If rst.EOF = True And rst.BOF = True Then
Beep
MsgBox "THERE ARE NO RECORDS WITH TODAYS DATE, REFER TO SPECIFIC DATE."
rst.Close
Set db = Nothing
Exit Sub
End If
'Export query as MS EXCELL
DoCmd.SendObject acSendQuery, "qryTodaysDate", acFormatXLS, company@somewhere.com, myboss@somewhere.com, , "File reference number ?????????"The following file is the latest extract from me as at " & Date & "."
regards, Steve
View 5 Replies
View Related
Mar 3, 2006
Hard to search for ths really, but I have a form with two buttons: Save and Exit.
Save saves the current record
Exit does an Undo if the form is .Dirty
If I cancel from the title bar, then the records are saved and the Close event is activated.
What I need is to detect if the Cancel box was clicked so that I can do an Unload before the form closes
TIA
Liam
btw, I'm getting the hang of this. It's a lot different from mainframe Cobol but it's fun to learn new stuff.
View 1 Replies
View Related
Nov 12, 2012
I'm using Access 2010. After creating a new database the standard screen appears that contains a grid to enter data and across the top of that grid is a row which is supposed to contain column titles. According to Microsoft Access all I have to do is double-click the column title so that I can set it to whatever text I want. the cursor remains in the next row assuming I want to enter some data. Why can't I change column titles from their default values?
View 3 Replies
View Related
Oct 24, 2014
Any workaround so that I can place more characters than the 64 limited in a field header.
In essence, I need to export a text or CSV file from Access every day, and the row header for the first column (i.e, in cell A1 if opened in excel) needs to read:
"Action (SiteID=US|Country=US|Currency=USD|ListingType=Hal f|Location=US|ListingDuration=GTC)"
I am currently unable to have a field header with that length, because Access does not allow my table header to be more than 64 characters.
View 7 Replies
View Related
Aug 8, 2013
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.
View 2 Replies
View Related
May 5, 2006
I saw the solution to this on this forum a while back but no matter what I search under I cant seem to find it again!
Can someone remind me how to change the Title of the error message boxes I can change the error message - by changing the Err.Description - but how do you replace the 'Microsoft Office Access' with your own Title?
I am running Access 2003 on XP.
View 1 Replies
View Related
Nov 14, 2006
I need to import a .csv file into a table. The problem is the top title information is on the file, like below. I do not want to import report name, report date, include rows, number of rows, and the blank space. I need to start the import on the header row. This looks fine in excel of course, but when i need to import it into access, there is no need for it. How do i either strip that out during the import or skip it.
sample csv file:
----------------------------------------------------
Report name: xxxxxx
Report date: xxx
Include rows where.....
Number of row: xxxx
date,firstname,lastname,dollar,address,text
10/4,john,smith,888,12 w st,,
10/4,jane,smith,7575,34 w st,,
----------------------------------------------------
Any help would be great! Thanks!
View 2 Replies
View Related
Aug 14, 2007
Is it possible to fully flash (Clear) the MS Access application window title bar?
I mean to let it appear fully empty without the "MS Access" and the Access logo, the key.
I made it but I wasnt able to remove the MS Access logo, the key from the title bar
Thanks,
View 1 Replies
View Related
