Reports :: Extract A Day And Compare Against MTD
Sep 10, 2013
I have a dbase with payroll data for a 31 day period. Each day (1 to 31) is a field in a record and is named as Day1, Day2, Day3 and so on. I have designed a Query that presents each days data, and sums on a MTD basis. I am forced to design my records using this format as I am importing data from a separate system that cannot be reformatted.
I'd like to be able to:
Choose a day and report the data and it's MTD value.For example, I choose to report payroll for Day8. I need a report to display the current day's data (Day8), and the MTD results (sum Day1 through Day8).Can this work without creating 31 separate reports?
View Replies
ADVERTISEMENT
Sep 7, 2014
I'm designing a database for a laboratory. There are many tables that will contain test results. They all have in common a field called ID( primary key) linked by a one to one relationship because one patient has reports in different tables. The ID is unique to each patient. For the Reports, i hit the rocks... How can i structure a report that can extract a patient's records from the different tables where they appear in the database?
View 8 Replies
View Related
Dec 6, 2005
Currently I have a remark field and want to just extract the amount. The data input is not in a structured manner therefore I could not use the mid function.
Example :
Remark
CASH PURCHASE $50K.DEBIT 3007484701
SUB $20,000 FIRST STATE DIVIDEND
AMT:$10,900(FS BRIDGE FD)NO DISC
View 3 Replies
View Related
Sep 13, 2006
Hi,
I am currently working on a report that i need to extract part of the data from the memoranda field. i have a problem to extract the details that i need.
Example as follow :
Field Name = Memo
Details in Field = ?********~SPGEO1398789159173897 *********?
what i have to do is to extract the data starting from "SPGEO1398789159173897". Also the length of the number can be varied.
i have tried to use the instr, Trim, LTrim but still couldn't get what i wanted.
Can someone here please give me a hand on this
Much appreciate for your help
Ronny
View 7 Replies
View Related
Mar 20, 2007
Hi, Everyone,
I have an excel column that has building location and building name in one column, example 1245 Accounting, how can I copy the 1245 into it's own column, please help, i can do it either in access or excel. Please.
View 3 Replies
View Related
Mar 31, 2006
I have a table in which one field badly needs separating into two. Currently, it is laid out as a mixed case text string followed by an upper case text string, e.g. O little town of Bethlehem FOREST GREEN.
I need to move the upper case string (FOREST GREEN) to a new field (and then trim the original field accordingly). Since there is no delimiter between the two parts of the field, I have no idea how to proceed. Can anyone help?
Thanks.
View 2 Replies
View Related
Aug 15, 2006
I have a very simple question that I have not find the result or maybe I do not know what to look for!
I have a query containing a field named year and one named sales, what I need is to have a single result of total sales per year. example
year sales
2005 100
2005 100
2006 80
2006 70
2006 60
The result I would like to put then in a form is:
total 2005 200
total 2006 210
All this by simply running a query without indicating any parameters.
Thanks
M
View 6 Replies
View Related
Aug 17, 2006
OK, so i have finally got round to re-building my client's database as suggested several times by RV.
I have three main tables that I need to extract data from - tblMembers, tblModules, tblCompleteModules.
Basically its a database containing the training details of Scout Association leaders. Each Leader needs to complete 26 modules over a period of time.
tblCompleteModules contains the data for the modules that have been completed, MemberID, ModuleID Date Completed and CompletedID.
I know how I can extraxt the modules that members have completed, but how can i extract the data for the modules that have not been completed. For example I need to report on the number of people who haven't completed module number 20 for example.
Any ideas?
View 2 Replies
View Related
Aug 22, 2006
Hi,
If I have a date that is formatted like this 01/01/2006, however do I write a query that shows the date as follows Jan 2006.
Thanks
Regards
View 6 Replies
View Related
Nov 3, 2006
i have some problem regarding this.--
I hve two table- 1.Ordbook
2.Ownmas
in Table Ordbook there is two field ORD_NO and Location
in Table Ownmas There is also Two field Ord_no and Sch_val;
but i enter the order no in table Ordbook normally.
suppose 7825,5855
but i enter the order no in table Ownmas as 7825/5855
so when im going to join these two tables by linking ORD_No and Ord_no. i find there is no match. bt my requirement is to match these fields by ANY no and want to retrieve the sch_val from Ownmas .
View 1 Replies
View Related
Apr 30, 2008
Hi,
I have a table which gets data from an external text file. I imported this into an Access table (version 2003).
I have a field (PersonName) in the table (Employee_Credits) which has data in the following format.
FirstName LastName (EmployeeID)
It is always in the same format. Using an SQL Query in MS Access, how can I extract the contents within the parenthesis and insert into a column titled “EmployeeID”
Any ideas?
Thanks.
View 2 Replies
View Related
May 9, 2007
Is there some free software somewhere to allow me to compare the vba in two differnat access dbs highlighting the differances??
THanks
View 2 Replies
View Related
Dec 21, 2006
Hi All:
I need a mechanism that takes the excel spreadsheet or table and determine if there are any new deal groups. (In this spreadsheet I allready have a deal group field) I will be using Book and DealGroup as combination primary key to compare against the table "8 -- YTD Principal Investments" which will hose the YTD numbers and serve as a historical table we will update throghout the year.If there is a new deal group the append into table "8 - YTD Principal Investments"
Thank you
View 1 Replies
View Related
Apr 26, 2007
Please help with query/report. I have the following problem.
One student evaluates course before and after. There is several questions related to this evaluation (all are same before and after) and for one question there is 5 possible answer (stored in option group)
I would like to build a report or a query where I can compare data from each student (before and after) for all questions. Is this possible to do in access.
So, StudentA: questionOn: answer: one columen: before, second column: after…. Next question.
View 1 Replies
View Related
Mar 11, 2008
I have a word doc from which I want to import data in a Database.
The word doc has a table, something like this :
Projectnumber 10700004
LSname LSTK number one
POnumber 1170-LST-04
Contactname Mr. Karel van Straten
Vendorname Mothercompany name S.A.
Manufacturer Operating Company name
Otherdata Others
Mancity 46100 Hamburg
Mancountry Germany
Manorder 31-0111-009
Mancontact Mrs. Angela Duval
Mantel 0049 2323 209 151
Manmail email@testing.de
At this stage I copy the table data and paste it into a memo field in a Dbase Query and try to manipulate the text in that query to get some of the data segregated , i.e Projectnumber : 10700004 , Mancountry : Germany , etc.
However, this doesn’t work as I want.
e.g. I’m trying to get the Manmail text thru this qry :
Manmail: Mid([Memofield],Len([Memofield])-InStr([Memofield]," ")) , but the output is not consistent.
Would there be a better way to get the original ( doc-table) data in the same way in my database.
Note :
I could use an additional step : copy/paste to Excel and then import to Access, but that’s something I would like to avoid.
Any suggestion will be appreciated.
Cheers, Ron
View 4 Replies
View Related
Apr 12, 2008
I think I am on the right track, but I am stuck with importing the date from my excel filename. Currently the following code imports all Excel files from the directory into a table in my database. The excel files are saved like "ABC_BNG_GTR_04012008.XLS" The numbers represent the date. I need for the date to be extracted and place in table into the "callDate" field. When I run my code, I continue to get a runtime error 13 - type mismatch. The code stops at the Mid() statement. What am I doing incorrectly? Thanks in advance.
Option Compare Database
Private Sub btnImport_Click()
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
Dim filename As String
Dim path As String
Dim TheDate As Date
DoCmd.SetWarnings False
path = "C:UsersChinaboyDesktopData"
'Loop through the folder & build file list
strFile = Dir(path & "*.xls")
While strFile <> ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend
'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
Exit Sub
End If
'cycle through the list of files
For intFile = 1 To UBound(strFileList)
filename = path & strFileList(intFile)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "sheet2", filename, False
'Inserts date in date column based on file name
TheDate = Mid(strFile, 12, 8)
CurrentDb.Execute "UPDATE sheet2 SET callDate =" & "'" & TheDate & "' where callDate is null"
Next intFile
DoCmd.SetWarnings True
End Sub
View 14 Replies
View Related
Oct 22, 2005
I am trying to extract data from one field and then put it into another field. I have 2 fields, Code_A and Code_B. I am trying to take the Code * data from Code_A and put it in Code_B. Any help is appreciated.
Code_ACode_B
AlphaCode 3
Code 3
AlphaCode 3
Code 2
DeltaCode 3
Code 3Unknown
CharlieCode 3
Code 3
View 4 Replies
View Related
Sep 19, 2006
I have read some tutorials online on how to split data into multiple fields.
http://www.databasedev.co.uk/splitting_fields.html
While this is the gist of what I need - it is much more complex.
I am dealing with adresses.
This inherited database has 2 fields - ZIP, and ADDRESS. I need to extract the data within the ADDRESS field to populate other more detailed named fields.
I would like to ultimately have my fileds to be: STREETNUM, STPREDIR, STREETNAME, STREETSUFX, SUD, SNUM.
Example: (Current ADDRESS data)
512 N Manchester Dr Ste 201
(extract ADDRESS and import into)
STREETNUM = 512
STPREDIR = N
STREETNAME = Manchester
STREETSUFX = Dr
SUD = Ste
SNUM = 201
--
Now I have tried several queries to extract the first bit of data 'STREETNUM', using the example on the link I posted above. INSTR, RIGHT, MID, LEFT.
Problem is there are also P.O. Boxes formatted like: PO BOX 1234, so my STREETNUM column includes the PO, when it should include the 1234.
PO Box 1234 should translate to:
STREETNUM = 1234
STREETNAME = PO BOX
All the rest of the fields aren't needed.
As you can see, I'm in OVER my head.
I would greatly appreciate any assistance or direction in accomplishing this task.
I am not a beginner with Access, but I am far from being an expert. A breakdown of some complicated tasks may be necessary... :-)
Thanks in advanced.
View 2 Replies
View Related
Sep 12, 2007
I have an expression below that I am trying to use to extract text from within parentheses in a field, but it is leaving the last ")" on the extracted text. Can anyone help me determine what I need to do to just return the text? Thanks.
The length of the field is 100.
Mid([SIDE_DRUG_DESC],InStr(1,[SIDE_DRUG_DESC],"(",1)+1,Len([SIDE_DRUG_DESC])-(InStr(1,[SIDE_DRUG_DESC],"(",1)+1))
View 3 Replies
View Related
Apr 22, 2008
Hi - I need to extract the month from a date via an SQL pass through query. e.g. if the date reads 19/02/2008, I need to have returned to me 'FEB', so that I can link the month (string) to another table.
I have though about "decoding" the date - I've seen that practice used in other scenarios, but never on a date format. Any suggestions?
Thanks
View 1 Replies
View Related
Apr 23, 2008
I have a timestamp field in a database and only want to display the Date. In the query I placed this: Date Received: Format([RItemTimeStamp],"Short Date")
Works fine however if I try to run a query to serach for a date range it doesn't give me all the days. Is there a better way how to truncate the Date of a timestamp so that I can sort it correctly?
View 1 Replies
View Related
Feb 17, 2005
I have the following SQL definition:
SELECT DISTINCT tblFile_Management_Received.Claim_Number, tblFile_Management_Received.Date_Checked_In, tblFile_Management_Received.Checked_In_By, tblFile_Management_Received.Copy_or_Original
FROM tblFile_Management_Received;
The results would show as:
ClaimNumber Date Checked In Checked In By Original or Copy
111111111 2/16/2005 8:34:24 AM XXXX Yes
111111111 2/16/2005 1:23:19 PM XXXX Yes
222222222 2/16/2005 8:34:52 AM XXXX Yes
222222222 2/16/2005 1:24:49 PM XXXX Yes
I am trying to use "Distinct" to show the following records when the query is ran (the desired results would be each unique claim number based on the earliest check in date):
ClaimNumber Date Checked In Checked In By Original or Copy
111111111 2/16/2005 8:34:24 AM XXXX Yes
222222222 2/16/2005 8:34:52 AM XXXX Yes
Can anyone help me with this??
Any help is greatly appreciated.
Thanks
View 4 Replies
View Related
Aug 24, 2006
Hello,
I have a table with a field named SSN. This field contains data with the following format:
123-45-6789L
As you can see my field has a text format and I need to create a make table query that will extract all the character that are places in position 12 and 13 (simply the letters after the ssn).
I have tried with Mid([SSN],12,13) but nothing happens.
Any help? Thanks.
View 5 Replies
View Related
Sep 16, 2013
I do have a module to extract a number from a string :
Public Function ExtractedValue(strSent) As Double
If IsNull(strSent) Then
Exit Function
End If
Dim strTemp As String
Dim intLoop As Integer
[code]....
My problem is that the string I do have is like:
19.0 EA
195 PAC
42 M
150 L
the problem is that when the number is like 19.0 EA the value I get is 190, in reality it should be 19. The rest is fine as there are no dot's.
View 3 Replies
View Related
Jan 4, 2007
Hi, all.
I have string: strSQL = SELECT SUM column FROM table
I need compare it with constant number, for Example:
If strSQL > 50000 Then ....
its not working, any Ideas?
Thank You in advance
View 4 Replies
View Related
Mar 8, 2007
Hi there!
I'm bulding this database where the user will have to insert a full phone number. What I would like to know is? if is there any way where Access can pick the first 5 digits of the number to compare with information already imputed in. by example if the user input the number 01942789789, access pick the number 01942 to look for a match? I know I could do with two diferents fields one for the code number and other for the number, but my boss only want one field. Is there anyone that could help me
Thanks
www.joaobeca.co.uk
View 1 Replies
View Related
