Convert Date Column To Two Columns
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 Replies
ADVERTISEMENT
Oct 9, 2014
Select * from Table where CDate(CStr(Nz(AnnouncementDate,0))) >=CDate(10-10-2014) and CDate(CStr(Nz(AnnouncementDate,0))) <= CDate(01-10-2014);
Here AnnouncementDate Column is Short Text
It gives me a type mismatch error..
View 3 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 28, 2014
I want to convert a number column into a text column.
Number Column is called Customer.
Below I can't match to the other table containing the text column Customer No.
How can I convert columns in vba?
Code:
strSQL = "SELECT CALCULATED_Forecast_TNS.*, USEFUL.TRP_in_EUR " & _
" FROM USEFUL INNER JOIN CALCULATED_Forecast_TNS ON (CALCULATED_Forecast_TNS.[Reference No] = USEFUL.[Reference No]) AND (USEFUL.[Customer] = CALCULATED_Forecast_TNS.[Customer NO])"
Set qdf = CurrentDb.CreateQueryDef("REPLACABLES", strSQL)
DoCmd.RunSQL "UPDATE REPLACABLES SET CALCULATED_Forecast_TNS.TRP_in_EUR = USEFUL.TRP_in_EUR"
View 1 Replies
View Related
Aug 28, 2006
Hello All,
I have created a table in access with the following column headings:
ID, A, B, C, D, E.
Column ID contains 9 digits text;
Column A column contains email address;
---------------------------------
Column B contains Currency ($); |
Column C contains Currency ($); | Source Fields
Column D contains Currency ($); |
---------------------------------
Column E contains order number;
Now I need to normalize this table to produce a new table with this structure - UNION QUERY is ruled b/c i have more than the three source fields:
ID, Source, and AMT
Where -
ID: would equal to ID;
Source: would be the value of the heading such as B or C or D;
AMT: would be the currency value of each corresponding column.
Help with the Module or any other way to do this will be greatly appericated.
Thank you.
-testcode
View 3 Replies
View Related
Jun 28, 2007
Hello,
I'm helping someone can help me with a little problem I am having with a peculiar data arrangement in Access. I have the following scenario. My table is set up with the following columns:
AgentID Skill_1 Skill_2 Skill_3 Skill_4
100 51 52 53 54
I want to create a new table that arranges the data like this:
AgentID Skill
100 51
100 52
100 53
100 54
Does anyone have any suggestions how I can do this?
View 5 Replies
View Related
Nov 18, 2004
I don't know if it is the right place to post this question, but I know many people here are good at both Access and Excel. The question is:
In an excel sheet,
A1: Smith, John
A2: 111 Pine St.
A3: San Diego, CA
A4: (555) 128-549
A5: Jones, Sue
A6: 222 Oak Ln.
A7: New York, NY
A8: (555) 238-1845
A9: Anderson, Tom
A10: 333 Cherry Ave.
A11: Chicago, IL
A12: (555) 581-4914
2. Type the following formula in cell C1:
=OFFSET($A$1,(ROW()-1)*4+INT((COLUMN()-3)),MOD(COLUMN()-3,1))
3. Fill this formula across to column F, and then down to row 3.
4.Note that the data is now displayed in cells C1 through F3 as follows:
Smith, John 111 Pine St. San Diego, CA (555) 128-549
Jones, Sue 222 Oak Ln. New York, NY (555) 238-1845
Anderson, Tom 333 Cherry Ave. Chicago, IL (555) 581-4914
This is exactly what I want. And I can do step 3 manually to copy that formula to each cell. But,
My question is in step 3. If I have thousands of lines, how can I fill that formula to thousands cells? from C1-C1000, F1-F1000.
I really appreciate it. Thanks.
View 1 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
Dec 16, 2006
Hi,
I have a query that outputs results as follows:
Expr1 Expr2 Expr3 Expr4 Expr5
1 2 3 4 5
I want to write another query to output the above result as follows:
Expr
1
2
3
4
5
Does anyone know how to do this please? Thanks!
View 10 Replies
View Related
Oct 15, 2011
I need to convert the following dataset from:
ClmSys Code
1111 V1
1111 V2
1111 V3
2222 V2
2222 H4
2222 H4
[code]...
View 4 Replies
View Related
Oct 13, 2014
I have a table that has just Months in a column. As in January, February......
I want to convert these to Numbers as in 1-12 in VBA. I tried all the DatePart in every combination but can't seem to get the results.
View 5 Replies
View Related
Jun 11, 2014
I have two separate reports. They have identical formatting. When I click on Adobe: Convert to PDF, this is what happens. Report 1 will convert with light blue in the column headers. Report 2 will convert with grey scale in the column headers. I have no clue as to how to make them both light blue or both grey scale. My boss wants them to match.
View 9 Replies
View Related
Mar 25, 2013
Is there a way to use a convert use a "text to column" function in access that separates at a fixed width?I have an access table (that I can't change) that includes the data and time in the same column and I want to separate the two (date and time) in a query. I can't use the "Left" function because my date isn't a consistent 10 characters.
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
Jun 30, 2014
I am trying to format an excel spreadsheet through access, specifically trying to convert a column from text to dates (I receive the data in text format and need to translate it to dates).
Code:
Dim excelApp As Object
Set excelApp = CreateObject("Excel.Application")
excelApp.screenupdating = False
excelApp.Visible = False
Set excelWB = excelApp.workbooks.Open("Z:DataBasicSMData.xlsx")
[Code] ....
I'm no longer getting an error, but it isn't actually modifying the spreadsheet...
View 2 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
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 1 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
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
