Move GrandTotal Column To Right Of Weekly Columns
Jan 16, 2007
Below is the code that I have for a query. Currently the
GrandTotal Column appears to the left of all the Weekly Columns.
Is there a way I can have the GrandTotal column appear to
the right of all the Weekly Columns? The weekly columns
will expand or contract depending on the dates selected.
PARAMETERS [Forms]![Queries_ReportsFRM]![StartDateTxt] DateTime, [Forms]![Queries_ReportsFRM]![EndDateTxt] DateTime, [Forms]![Queries_ReportsFRM].[FaultCategory] Text ( 255 ), [Forms]![Queries_ReportsFRM].[SystemGroupProblem] Text ( 255 );
TRANSFORM Val(Nz(Sum([Totals]),0)) AS SumOfTotals
SELECT [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory, [Trends-1-3TON-WEEK].Problem, Sum([Trends-1-3TON-WEEK].Totals) AS GrandTotal
FROM [Trends-1-3TON-WEEK]
GROUP BY [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory, [Trends-1-3TON-WEEK].Problem
PIVOT [Trends-1-3TON-WEEK].YearMonthWeek;
View Replies
ADVERTISEMENT
Aug 11, 2014
I have a report that displays maybe about 4 columns that read data and if a columns reads zero then I have a code where it will hide, but my problem is that I cannot get the columns to move over once the column that is zero is invisible.VBA code that will hide a column that has zeros and move over the remaining columns so that when the report is ran it will not show just an empty white space.
View 10 Replies
View Related
Mar 2, 2014
Basically, I have a database table that is maintained every week. It is about e-mail account licenses for Office 365.
Each column represents a week of license data for every mailbox account which is about 10 000 plus users with the date as the field headers( something like "License information as of 06122013").
Basically, I have created 52 queries based on the license type I require to be calculated & I have a form created to control it. However, right now it is all output only as I have yet to create any user parameter to specify on any of the queries about the date I want
As I am currently unsure how to specify all the queries to use one field date header which I want the user to specify via drop down list in order to calculate all the licenses on this specific date.
For example, if I want to see the license information for 06/11/2013, I would choose "License as of 06112013" & it would then run all the queries based on the header specified earlier & output that information on the form.
Right now the queries are all configured as the "Select" type.
View 1 Replies
View Related
Mar 11, 2007
Hi, I have data from a form on my website in a text file, that corresponds to each visitor's input, each 13 lines in the form belongs to one visitor, as shown (twice) at the end of this message.
What I would like to do is have each visitors inputs translated to ONE row, with 13 columns/fields each. It could be appended to the same table or preferably generated in a new one. Note, there are no blank fields, some won't have data after their title, i.e. addy_line_2: is often blank, but at the very least, addy_line_2: or another field name is always there.
It would ROCK if I could also automatically take the name of each field out, i.e. each name is continuous characters up to the : (colon) ...
Thanks in advance, my Access knowledge is obviously limited, I'm sure this is fairly simple!
The fields:
Phone: 213-555-1212
Submit: Continue
addy_line_1: 1000 Melrose Place
addy_line_2:
badge:
city: Los Angeles
email:
homegroup:
name:
program:
state: CA
volunteer:
zip:
Phone:
Submit: Continue
addy_line_1:
addy_line_2:
badge:
city:
email:
homegroup:
name:
program:
state:
volunteer:
zip:
thank you thank you thank you thank you thank you !!
View 1 Replies
View Related
Feb 13, 2006
OK, I have a truck mileage database with a columns for monthly "StartingMiles" and "EndingMiles" - At the beginning of each month I want to automatically move the entire content of the "EndingMiles" column to the "StartingMiles" so I can start a new reporting month. I'm assuming I can use an Update Query to do this - correct? How do I setup the Update query to perform this?
Or is there a better way?
View 1 Replies
View Related
Feb 13, 2014
I have an append query. I want to get a column that is the sum of 3 other columns:
Oil_T_C_(bpd) Gas_T_C_(MMcfd) LNG_T_C_(Tonnes_per_Annum)
1000 2000 3000
I want to get a column called Total_Throughput
What is the exact syntax that I have to put into the query design form?
so: Total_Throughput: than what?
View 14 Replies
View Related
Jun 30, 2015
Currently I am having a table in which the data is in 1 column, but this needs to be in 2 columns. How to do? I have:
VendorCode VendorName Quality MaxClaim
411411 SCA PACKAGING MUNKSUND 1001 FSC Controlled Wood
411411 SCA PACKAGING MUNKSUND 1200 FSC Controlled Wood
411411 SCA PACKAGING MUNKSUND 1300 FSC Controlled Wood
411411 SCA PACKAGING MUNKSUND 1400 FSC Controlled Wood
411411 SCA PACKAGING MUNKSUND 1500 FSC Controlled Wood
411411 SCA PACKAGING MUNKSUND 1600 FSC Controlled Wood
411411 SCA PACKAGING MUNKSUND 1700 FSC Controlled Wood
411411 SCA PACKAGING MUNKSUND 1001 FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1200 FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1300 FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1400 FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1500 FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1600 FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1700 FSC Mix 90%
But what I want to have is:
VendorCode VendorName Quality MaxClaim MaxClaim
411411 SCA PACKAGING MUNKSUND 1001 FSC Controlled Wood FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1200 FSC Controlled Wood FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1300 FSC Controlled Wood FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1400 FSC Controlled Wood FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1500 FSC Controlled Wood FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1600 FSC Controlled Wood FSC Mix Credit
411411 SCA PACKAGING MUNKSUND 1700 FSC Controlled Wood FSC Mix 90%
I looked everywhere, but the crosstab function is not the one, as that function will put all other data in separate columns, whilst I only want to have 2 column MaxClaim.
View 3 Replies
View Related
Mar 25, 2006
Hello.
I don't quite know where to ask this...
i didn't think things through when i initially created the DB, and created a column called dateOfBirth that holds user entered dates as dd/mm/yyyy.
This causes many query issues, and I would LOVE to have this changed.
Is there a script i can run that will take dateOfBirth, determine the month, write the month as Jan/Feb/Mar/... in a column named dobMonth, and take the day value from dateOfBirth (1-31) to a column called dobDay?
BEFORE:
dateOfBirth |
23/10/00 |
After
dateOfBirth | dobDay | dobMonth|
23/10/00 | 23 | Oct |
I don't care about the "year" data, and I already have the dobDay and dobMonth columns set up...
Thanks.
View 10 Replies
View Related
Dec 6, 2014
adding columns in Access2013,I'd like to add a column to an existing datasheet and place it between 2 existing columns,I see where and how to an a new column but don't see where I can insert it where I want it.
View 2 Replies
View Related
Feb 7, 2008
How can I move multiple columns data into a single column so that:
Name Age Location
Mike 25 Essex
Jack 32 Surrey
Bob 36 Newcastle
appear in a single column with data appended column-wise as
Mike
Jack
Bob
25
32
36
Essex
Surrey
Newcastle
Any help would be much appreciated. I'm a novice at VBA, so if anybody could do the code, it would be great!
View 3 Replies
View Related
Feb 7, 2008
How can I move multiple columns data into a single column so that:
Name Age Location
Mike 25 Essex
Jack 32 Surrey
Bob 36 Newcastle
appear in a single column with data appended column-wise as
Mike
Jack
Bob
25
32
36
Essex
Surrey
Newcastle
Any help would be much appreciated. I'm a novice at VBA, so if anybody could help me with the code, it would be great!
View 14 Replies
View Related
Jul 3, 2014
I have a column that's called "Date" which stores values for begining to end date as 'Text' like: 070314-073114 So I need to split it to 2 columns that has the Start Date as 070314 and End Date as 073114?
This database is still in Microsoft Access, and eventually after cleaning it up, we will move it to SQL Server.
View 14 Replies
View Related
Nov 24, 2014
I have an imported table and within this table contains a column that needs to be further delimited. I've read that it is possible to delimit columns using the left, right and mid functions, however, I need to delimit it to more than 3 columns. Maybe an estimated 6.
Examples of the data contained in the column are as follow:
ITM~W01GGASPAPP1B:W6400~12.34.56.78~~W01GGASPAPP1B ~W01GGASPAPP1B~ACK~
ITM~a01gibapp3a:AC900~12.34.56.78~~a01gibapp3a~a01 gibapp3a~ACK~
TEC~~01.234.567.89~~~~ACK~
I need to delimit it by "~". It must also be similar to Excel's text-to-column feature whereby if there're no values between 2 "~", it will be recorded as null.
The main thing I require from each record in the column is contained within the first and second "~" (even if it's null).
View 3 Replies
View Related
Oct 14, 2014
I am working with other data that has been created by someone else.
There are a number of columns with the same information in (a serial number). What I need to do is get this into one long column so that I can run other queries from it.
So far I have tried using this SQL:
SELECT A1-TX1 POWER AMPLIFIER 1
FROM SM_Cabinet_T
UNION ALL
SELECT A1-TX1 POWER AMPLIFIER 2
FROM SM_Cabinet_T
[Code] .....
But it is not working - Is it to do with the field names or am I entering something incorrectly?
View 8 Replies
View Related
May 21, 2013
I have 4 tables that store information on guest charges (stayinfo/room rate, fuel charges, bar tab, merch charges) each linked to table carrying guest ID's. Is there a way to combine/merge all the tables into one in such a way that all of the guestID's are in one column, all of the charges are in another column etc? Basically adding one table to the bottom of another.
View 1 Replies
View Related
Feb 4, 2008
I will need to create a new column in a query with data based off of two columns currently in my DB.
Current fields are: [TestType] & [TestReason].
New TEMP field will be [TestCombo]
TestType has 4 possible option via drop down.
TestReason has 21 possible options via drop down.
TestCombo will be one of 10 options depending on the data in TestType & TestReason.
examples: (here is an example of how the combo field will be populated. There will be ten total rules like this. one for each possible text option in the combo box.)
If [TestType] is "DRUG" & [TestReason] is "PRE" or "RAND" or "PA" then [TestCombo] would be "DOTDT".
If [TestType] is "ALCOHOL" & [TestReason] is "PRE" or "RAND" or "PA" then [TestCombo] would be "DOTAT".
If [TestType] is "DRUG" & [TestReason] is "N-PRE" or "N-OTHER" or "N-FUP" then [TestCombo] would be "NDDT".
The data does not need to be saved anywhere at all in the database, just generated when pulling this query. It will then be exported and dumped into another system.
Thanks. Here is a temp copy of the database with some junk data to maybe make it a little more clear.
View 9 Replies
View Related
Jun 6, 2013
I want to create the date from multiple columns to single colums. Just for example
table 1 (local purchase)
Itemname Date
Apple 12/01/2013
Mango 13/01/2013
Table 2 (Import purchase)
Item Name DAte
Apple 12/04/2013
Mango 08/06/2013
Now i want to make one query, which can you the date as follows when we give criteria = apple
Item Name Date purchase mode
Apple 12/01/2013 local
Apple 12/04/2013 Import
Means two dates from different table into one query column... One way in my mind to make one table for both tables.
View 9 Replies
View Related
Feb 1, 2005
HELP :confused:
Hi,
I need to copy 5 columns in an Access table into a single column in Excel. How can I do this?
Pictures To Help explain below:
http://uploads.savefile.com/users/uploads/1_154.jpg 261kb
http://uploads.savefile.com/users/uploads/2_154.jpg 192kb
View 4 Replies
View Related
Oct 4, 2013
I have a table that has two columns "Nationality" and "Gender". I wanted to run a query that will Group the nationality and then split the gender column into two columns and after that it will count both gender columns for each nationality. When I posted this question in "Reports" section I got the suggestion to use the SELECT COUNT in SQL. It worked but only for nationality. I couldn't get it work for the Gender column. I searched alot and the only thing I got was the SQL function to split data from one column into two but that also didn't serve the purpose (check the link to see why: [URL]) At last, I went on to create a cross tab query. Selected Nationality as Row header, Gender as Column header and Customer ID as calculation point. And there I got the result I needed. The SQL Code looks like this:
Code:
TRANSFORM Count(Register.[Customer ID]) AS [CountOfCustomer ID]
SELECT Register.National, Count(Register.[Customer ID]) AS [Total Of Customer ID]
FROM Register
GROUP BY Register.National
PIVOT Register.P_Gender;
Crosstab query did the trick..
View 1 Replies
View Related
Jun 24, 2014
My question is that can we do multiplication of data of two columns and result is automatically displayed in third columns in datasheet view.? Is it possible ?
View 1 Replies
View Related
May 16, 2013
I'm trying to make a database to track inventory or several items. Basically, I have four tables:
1) RawMaterialList - includes a list of all raw materials.
2) PartList - includes a list of all finished product using said raw materials.
3) RawMaterialRecieving - contains details from each packing slip of incoming raw materials.
4) ShipmentRecord - contains details of daily shipments.
Each of these tables is fed by a form of the same name. I should note at this point that I basically taught myself how to use Access and I imagine I'm in the dark about quite a few things it can do. I've made several databases over the last few years, but I'm stumped at this point.
Here's my problem. In the form RawMaterialReceiving, I have several fields aside from basic information:
1) Item - a list of of raw materials from table RawMaterialList
2) Description - also dependent on info entered into table RawMaterialList
3) Quantity
But, I have 12 of these instances.
Item1, Item2...Item12;
Description1, Description2...Description12;
Quantity1, Quantity2...Quantity12.
My problem is I want to add up the quantities of each raw material and I'm not sure how to go about that. Lets say on May 13, I received 15pcs of Part A and 20pcs of Part B. I enter this information as Item1 and Item2 respectively. On May 14, I received 30pcs of Part B. I enter this information under Item1. Now I want to add up all of Part B (50 pcs). But Part B has one value listed in the field Quantity1 and one value listed in the field Quantity2.
Example:
1st Entry:
May 13
Item1 = PartA Description1 = PartA's description Quantity1 = 15
Item2 = PartB Description2 = PartB's description Quantity2 = 20
2nd Entry:
May 14
Item1 = PartB Description1 = PartB's description Quantity1 = 30
How do I get it to add up Part B to get 50pcs?
View 10 Replies
View Related
Jul 23, 2015
So i have a access database with a main field that we can call vendor # (LIFNR) and another called Company code (BUKRS). There are multiple company codes under a single vendor #. Example:
LIFNR
BUKRS
0000010535
1010
0000010535
5060
0000010535
5610
0000010536
1010
0000010536
5060
0000010536
5610
What I am trying to do is create a 3rd column where i can have a unique row for each of these fields without it repeating. There is no unique identifier in this table and that is what i am trying to achieve.
View 3 Replies
View Related
Mar 29, 2012
I have a database with all the hours employees have logged stored in the database. Our payroll company wants an excel spreadsheet that has very specific info in particular columns and fields on the excel spreadsheet, so I'm trying to design a query which will put the correct info in the correct fields per their system.
The challenge is, I have currently a query with Employee ID, Overtime Hours, and Regular Hours as separate columns.
I need to translate this to a query with a single column for hours and a separate column that designates those hours as OT or Reg, with two rows for those employees who have both types.
Current:
ID / Regular Hours / OT Hours
101 / 70 / 7.5
102 / 30 / 0
103 / 5 / 0
Needed:
ID/ Hours / Type
101 / 70 / Reg
101 / 7.5 / OT
102 / 30 / Reg
103 / 5 / Reg
I don't know how to create a query or a formula in a query to break out each employee row into multiple rows with different data in the hours column. It seems like there's something pretty straightforward that I've done in a similar vein but it doesn't seem to work - I can do the opposite and combine those hours by using the SUM function in a query, but I can't seem to break it out this way.
Access 2007, Windows 7.
View 5 Replies
View Related
Jul 17, 2013
I trying to combine three columns that I have into one column without combining fields.
Currently what I have:
(see image below)
What I want:
ID-----MOC
##----name1
##----name2
##----name3
##----name4
##----name5
etc
The list I have will be much longer and will be changing frequently, which is why I can't just go on excel and manually do this.
View 14 Replies
View Related
Jul 22, 2005
I'm sure this must have been the subject of a thread before, but after an hour seaching my head aches.
I'm running (Access 2000 on XP Prof) a sort of planning board. The week commencing dates change automatically, as per previous postings on here.
I have a job run from a command button to then change a field representing the week the job is to be run in ie week 2 jobs to week 1, week 3 to week 2 and so on. So far so good.
I would like this job kicked off automatically once the first time the db in opened in the new week.
Any advice or pointers to appropriate thread welcome.
Ta
View 4 Replies
View Related
Mar 14, 2008
I'm not really sure what to do on this one, I'd like to hear some suggestions on what everyone thinks..
I have a database where users enter information Bi-Weekly into a form. I have 2 date fields set up, one is called the RecordDate, it is the date at which the user has entered the actual data into the database. Ignore this date.
I have another date, called MainDate, which is the date that all departments need to have submitted the reports (Each department fills out these bi-weekly reports [approx 100 departments]), meaning that one complete set of 100 reports is entered every 2 weeks. Even though they are all entered at various dates within those 2 weeks, usually almost at the end of the 2 weeks, all of the reports will contain this MainDate and it will be the same for all departments every 2 weeks.
I'm looking for the easiest method to accomplish letting a user select these bi-weekly dates and making sure that the dates are correct, validation?.
Some thoughts I had
1) For instance, if typing in the bi-weekly dates, I want it so that they can only type in the dates of every 2 weeks, no other dates will be allowed.
** Best option in my opinion 2) Have a small calendar that expands when clicked right next to the MainDate field so that on the calendar you can only click on dates every 2 weeks, or have every 14 days a different color on the small box for the date. [the users seem to like the ease of using calendars, one of the reaons is is a good option in my opinion]
3) Have a dropdown? That shows the current date that is allowed to be selected based on some sort of 14 day interval cycle. (i.e. after 14 days of one cycle are up, the only option in the dropdown is to click the next MainDate, which will be exactly 14 days from that just passed.
Anyway to have this date automatically filled in after 14 since the last MainDate entry? Bi-Weekly reports are due every second Thursday.
Thanks in advance to any input you can offer
Dave
View 1 Replies
View Related
