Cleaning Up "Grouped" Data
Oct 26, 2005
I have a question and I'll apologize in advance because it may or may not really be an Access question.
I have a large ASCII spreadsheet that I have imported into Access. It has 4 columns, and has these headings: CodeID, Group, Division, Account. Many Division and Account combinations can exist within a Group, and there can be many Groups under a specific CodeID. Unfortunately the way I received the data was in a grouped fashion, like so:
CodeID Group Division Account
1234 1111 0001 0001
0002 0002
2222 0001 0001
0003 0003
1235 1111 0001 0001
0003 0003
etc.
Is there a way - easy or otherwise - to populate the blanks with the appropriate information? Essentially to "ungroup" I guess. I need to do this so I can sort and modify the data. Obviously as it is I can't do much with it.
Note, when I imported the data I had Access add unique counter as a primary key. That is allowing me to keep the data in order, but doesn't help beyond that.
Any suggestions???
View Replies
ADVERTISEMENT
Nov 16, 2007
Hi
I have a table that contains telephone numbers.
The data is in a variety of formats.
Here are some examples:
087 123456
087-123456
(W)087-123456
(H)123456
I would like to strip out hyphens, brackets, spaces, etc.
I hope to do this in a straighforward make table query.
Is there a function available that will strip out non-numerics characters?
Thanks
Noel
View 1 Replies
View Related
Jan 11, 2006
I need to create a program that will regularly import a text file of over one million records into an Access table. I've been give a list of about fifty different updates to perform on the data to clean it.
I can't imagine performing all these updates in one query. However, creating fifty individual queries seems horribly inefficient from a processing perspective.
I'm accustomed to stepping sequentially through a table in FoxPro, which seems ideal to me for this type of situation. What is the best way to handle this in Access?
View 4 Replies
View Related
Apr 3, 2008
So i'm not sure where this post belongs as it may encompass several items.
I have an excel sheet where every row has an email address and a product purchased. I've imported it all into access but would like to run a query to find out how many people that bought x purchased y. Or tell me how many people that x did not buy a,b, or c.
Part of the problem is that each record is one product. My data looks like this:
email: Product:
e1 p2
e1 p7
e1 p33
e2 p2
e2 p9
e3 p1
e4 p1
e4 p3
I was able to group some of the data by email address in access but can't figure out a good way to query it. I'm thinking I probably need to merge the data where each unique email address is a record and the products have all the products purchased instead of just one but I really have no clue how to do that.
Is this easily do-able? how would I go about this?
Thanks alot!
View 2 Replies
View Related
Nov 26, 2014
I am having trouble writing what I think should be a fairly straight forward query... I am have a table with 2 fields, the first contains a list of subjects, the second contains a list of scores that students got in their tests in that subject.
So the data looks like this:
Maths 54
Maths 69
Maths 41
English 71
English 55
Art 44
Art 43
Art 66
I would like to write a query that selects only the highest score for each subject and presents it like this:
Maths 69
English 71
Art 66
View 4 Replies
View Related
Jul 20, 2015
I'm very new to Access and I'm attempting to write an expression in a query that will calculate the % of the count of "Exchange" field (Exchange is a text field and is grouped and the count based on each unique name) where the total count is based on the filter where "Group" = 'FS' or 'S'.
The below seems to work, but there is a better way of going about this (especially if I have to add more filter criteria). I added a pic of the query I'm trying to build.
PercentofTotal: (Count([Exchange])/DCount(Count([Exchange]),"[Holdings]","[Asset Group] = 'FS' OR [Asset Group]='S'"))
View 11 Replies
View Related
Oct 20, 2014
I have an Excel file with a name range "DBIAS" which identifies all database data.
Then I have an Access file with a form to import that database (better, that named range) into an Access table. While importing, I have to filter some records or grouping by some field.
I cannot run correctly a VBA code to get data (filtered and/or grouped) from that name range and save those records to an existing or a brand new table.
I could get those data as DAO.recordset and printed out with "Debug.print" on immediate window, but I cannot complete the final step: writing those records to a table.
View 3 Replies
View Related
May 5, 2006
Ok, I am working on an exsisting database at work that has alot of code/forms/reports and so on. Whoever worked on this before me apparently didn't believe indenting code or anything like that. Now I don't want to to go all this code to clean up the structure of the code. I remember back a few yeras ago I found an app for VB that did this for you. No clue where I came across it at or even what it was called but I was wondering if there was anything available like that for Access?
View 3 Replies
View Related
Sep 5, 2014
In given table name is formatted as:
HERNANDEZ JR, FELIX / BRADY, ANDEL
and I need to make it to "Felix Hernandez" How would I be able to clean up "/" , "JR," in my query.
View 8 Replies
View Related
Feb 21, 2014
In a table, there's a column, "Siebel or Sales Ref". Some of these will have a long sales reference number or an ID from a Siebel sales system (pants), in the format 1-XXXXXXX. The problem is that some have two IDs, separated by a forward slash (/), so like 1-ABC1234/1-DEF5678. He wants to go through the data, and where there's a double ID, split it out, and create a new row with the second ID.
I'm not sure this can be done in Access, I would have done a loop in Excel, but it's his database.
Code:
INSERT INTO ActualBaseData
SELECT *
FROM (SELECT 'ITQ ID', Deadline, Lot, 'Bid Progress', 'Framework Type', 'Tender Type', 'Siebel or Sales Ref'
FROM TempBaseData
WHERE InStr('Siebel or Sales Ref', '/') = 0
[Code] .....
View 6 Replies
View Related
Oct 27, 2014
I want to release an Access file which has eveything locked down and cleaned up so that my Form is showing full screen (and nothing else) for an optimal user experience.
View 2 Replies
View Related
Dec 28, 2007
PROBLEM: String Manipulation
"Cleaning up Mom's Christmas address labels"
I need guidance on the best string manipulation functions (Instr, Left, Right) to cleanup my mother's Christmas address list of 300+ names.
I have successfully imported the text file into Excel and exported to Access; fieldnames: FULLNAME, ADDRESS, CITYSTATEZIP
I have found instructions on how to breakout FULLNAME field into FIRSTNAME and LASTNAME.
But within the FULLNAME field are many combinations of titles (Mr., Mr. & Mrs., Dr., HON.) with inconsistent periods applied.
Which one of string manipulation functions:
Instr
Left
Right
would be best for extracting these various titles from this name field?
I understand the concepts behind the above functions, but not enough experience using them to understand the tedious syntax or which string manipulation function would be best for extracting the varying title entries to a separate created field called TITLES.
So far, I have deduced this will be a multi-step process. But asking for guidance:
1.) Which string function is best suited for this?
2.) Example of the function syntax for an update query?
2.) Suggested order to administer update queries?...
to extract misc titles from the FULLNAME field.
I am a novice-casual Access user.
Thanks, Greg
(If someone would copyright these steps into a book called "Cleaning Up Mom's Christmas Address List"... I am sure they could retire from sales on Amazon. :-)
View 14 Replies
View Related
Jun 12, 2012
I want to create a report for the Monthly cleaning plan of a hotel. For each day, how many rooms need new sheets, how many need new towels etc.
At this point I can generate a report for any given day.
This could be an example of what I want to achieve
Code:
------------ Date | Date+1 | Date+2 | Date+3
New Sheets 2 1 0 2
New Towels 1 3 0 1
Full Clean 0 1 2 0
"Date" is a date tat you can set, after which you'll get the following 30 days("Date+1","Date+2" etc)
I thought that a CrossTab query would give me what I want,but using the wizzard I can't get the result that I want.
Haven't worked with crosstab queries before so maybe I'm doing something wrong, or maybe this isn't even possible with a crosstab query.
View 4 Replies
View Related
Nov 16, 2005
Hi,
I want to make a church database grouping families together and identifying each persons position in the family while still having each family member with an individual profile, i.e. birthdays, anniversaries, position, classes, memos, possibly donor info.
How do I do this?
Thanks
View 1 Replies
View Related
Jun 12, 2007
Ok, I'm missing something simple here I'm sure, but I can't see the wood for the trees at the moment.
background guff
What I have is a system tracking actions being undertaken. There's an SLA for these actions which means they should be completed within 10 days. At the moment we have no reporting on whether or not we're meeting this SLA.
Now obtaining the information for all the data this year is fine and dandy and works ok. Where I'm having problems is doing a monthly breakdown for the ytd.
I'm using the following query to give me my raw data:
SELECT [Parent Table].ID, [Parent Table].[Date entered into database],
[Parent Table].[Leave Date], [Parent Table].DateCompleted,
DateDiff("d",[leave date],[datecompleted]) AS DaysToCompleteFromLeaving,
DateDiff("d",[date entered into database],[Datecompleted]) AS DaysToCompleteFromEntered,
DatePart("m",[leave date]) AS [month]
FROM [Parent Table]
WHERE ((([Parent Table].[Date entered into database])>#1/1/2007#)
AND (([Parent Table].[Leave Date])>#1/1/2007#)
AND (([Parent Table].DateCompleted) Is Not Null));
Which gives me the various dates, the number of days it took to complete the record from when a person left the company ,from when their information was loaded into the database and finally a number for the month.
I have a query that happily gives me the average completion times on a monthly basis:
SELECT Avg(CInt([DaysToCompleteFromLeaving])) AS AvDaysFromLeaving,
Avg(CInt([DaysToCompleteFromEntered])) AS AVDaysFromEntered,
[Completed Leaver Dates].month
FROM [Completed Leaver Dates]
GROUP BY [Completed Leaver Dates].month;
but I seem to be having a great deal of difficulty specifying criteria on a Count to show me the same breakdown.
I can get a total count of records per month:
SELECT [Completed Leaver Dates].month,
Count([Completed Leaver Dates].DaysToCompleteFromLeaving) AS CountOfDaysToCompleteFromLeaving
FROM [Completed Leaver Dates]
GROUP BY [Completed Leaver Dates].month;
But what I want to do is split that number into two columns, records where the completion date was >10 days and records where the completion date was <= 10 days which is where I'm having some problems.
Putting a critera in design view for the count field still returns the total number of records per month and returns the following SQL query:
SELECT [Completed Leaver Dates].month, Count([Completed Leaver Dates].DaysToCompleteFromLeaving) AS CountOfDaysToCompleteFromLeaving
FROM [Completed Leaver Dates]
GROUP BY [Completed Leaver Dates].month
HAVING (((Count([Completed Leaver Dates].DaysToCompleteFromLeaving))>10));
I'm fairly sure it's in the HAVING clause, but I'm not sure what I'm missing.
View 1 Replies
View Related
Jul 30, 2007
Hi Guys,
can anybody help me on the below?
i need to generate a query contains
name, project, total_Hours_worked (only if total is >45) for a week by each employees. the query should contain total hours for all the weeks.
The table contains week_ID column and dyas worked column.
i can create a query which gives me sum of day_hours worked using selecct sum(day_hours) as Total.
but i badly need total sum of week hours worked by each employees. so that i can generate overtime report grouped on weekly who has woked more than 45 hours per week.
the table is attached. please see and help me if you can.
Thanks
Kiran Konsaai
View 2 Replies
View Related
Nov 29, 2007
Hi,
I'm trying to create a report that separates info depending on Prodno. For now it lists all the right information like this
Code:Product Aisle Rack Shelf0218 7 6 20775 5 6 20775 4 4 40775 0 1 30963 1 1 71000 7 4 61000 2 3 71006 8 8 8
which is fine and dandy. What I would prefer is it do like this
Code:Product Aisle Rack Shelf0218 7 6 20775 5 6 2 4 4 4 0 1 30963 1 1 71000 7 4 6 2 3 71006 8 8 8
I am assuming it is some kind of group by in the details section? Any help would be great,
Thanks,
View 2 Replies
View Related
Jan 18, 2006
Hi!
I use MS Graph and need show 2 data types:
1. Invoice Value, EUR
2. Date
Date axis group records by month, for Example Period from 2005.01.01 And 2006.01.01 has 12 positions in Date Axis.
Her I have problem: I need show sum of Values for every month.
For example in Period from 2005.01.01 till 2005.02.01 I have 3 Values: 200, 500, 600EUR. Her I need to show 1300EUR, and so every month. Her is my strandart code:
Dim strSQL1 As String
strSQL1 = "SELECT Date As Date, Value FROM Table WHERE (Date BETWEEN 2005.01.01 And 2006.01.01)"
Me!chrStatistik.RowSource = strSQL1
Thank You in advace for help.
View 5 Replies
View Related
Aug 23, 2006
OK, first time posting so I'll try to be clear here. I work for a special education agency and have created a database to track student/therapist information. Now, I have reports showing which kids are in which schools and who their therapists are. What I can't figure out is how to print a report, grouped by school, that will display which therapists are active in that school.... any ideas?
View 1 Replies
View Related
May 8, 2013
I have a table with:
Invoice Number | Customer Name | Item | Item Cost | Invoice Date | Paid | Date Paid
Example of data:
AK001 | A Brown | Blue Car |1000 | 1/4/2013 | Yes | 20/4/2013
AK001 | A Brown | Red Car |2000 | 1/4/2013 | Yes | 20/4/2013
AK001 | A Brown | Yellow Car |500 | 1/4/2013 | Yes | 20/4/2013
AK002 | A Brown | Black Car |1000 | 7/4/2013 | Yes | 20/4/2013
AK003 | B Smith | Blue Car |1000 | 12/4/2013 | Yes | 25/4/2013
I want to create a report from this table that outputs as:
Invoice Number | Customer Name | Total Price | Invoice Date | Paid | Date Paid
Example of report from Example Data:
AK001 | A Brown |3500 | 1/4/2013 | Yes | 20/4/2013
AK002 | A Brown |1000 | 7/4/2013 | Yes | 20/4/2013
AK003 | B Smith |1000 | 12/4/2013 | Yes | 25/4/2013
Is there an easy way to do this.. or will I need to make a new linked table with the invoice number as a lookup?
View 2 Replies
View Related
Sep 30, 2011
I'm creating a database of companies. Every company has at least one field of services they provide. There are three main groups which contain all the services (Let's say I have three tables).
What I need is to have a form/table (query?) where I can assign to a company any number of services from any group. Is it possible to do it in a form where I could simply add a service by choosing a group first and then a proper service, and then add more in the same way?
View 3 Replies
View Related
Oct 19, 2006
This probably is a very stupid question but I am a new user and have been pulling my hair out for a week over this. I have created a report and linked it to my table but it generates a list of all of the values in my table on the report. What I want is a way to generate a report page for every row in my database table grouped on a key record that is unique to each data row. If anyone has any advice it will be much appreciated, thanks.
View 1 Replies
View Related
Mar 14, 2013
I have a database that tracks students and their test scores. I am trying to figure out a way to where it will pull the student's latest test score and compare it to see if they fall within standards. So far I have 3 tables.
tblStudents
studentID
studentName
studentEmail
tblResults
resultID
resultStudent
resultDate
resultTest
resultScore
tblTests
testID
testName
testPassScore
The query I have written looks like this: SELECT tblStudents.studentID, Max(tblResults.resultDate) FROM tblStudents LEFT JOIN tblResults ON tblStudents.studentID = tblResults.resultStudent GROUP BY tblStudents.studentID." The data pulls just fine at this point. What I'm trying to figure out is how to then add more columns to this query to start doing comparisons. When I try to add more columns it tries to use them for additional grouping and adds many more records.
View 8 Replies
View Related
Mar 5, 2015
I have simple table data structured as follows;[Origin], [Code], [Weight]. The Code field is a lookup field that will contain one of 8 choices; 10, 13, 13c, 23, 25, 27, 27a, & other. The other fields are pretty much self explanatory. Sample data would look like this:
Code:
Origin Code Weight
Edison 13 4.25
Edison 13c 2.87
Piscataway 10 5.45
Middlesex 23 1.24
Edison 13 5.21
What I need to create is a totals query where I first group by origin value, then a column for each "code" value which totals the weight for that "code". A sample output would look something like this:
Code:
Origin 10 13 13c 23 25 27 27a other
Edison 9.46 2.87
Piscataway 5.45
Middlesex 1.24
The only way I can think to accomplish this is to restructure the original table to include a field for each code and enter the weight in the appropriate 'code' field. If this is the only solution then Ill have to live with it, but is there any way to create this output using my original structure?
View 1 Replies
View Related
Nov 6, 2014
I have created a Query that shows
The Product ID, Product Name, Quantity, Price, Gross Margin, Sales, Profit.
The only problem is that in the two tables I was given in my assignment, there were multiple dates for the purchase, thus even though I have 74 products there is multiple listings i.e.
110-10 Sofa Chair 5x $2 profit:$10 - october 11
110-10 Sofa Chair 6x $2 profit:$12 - october 12
I need to group it so that it becomes this.
110-10 Sofa Chair 11x $2 profit:$22
The date/location is not important, only the aggregate sales. Here is my SQL ....
SELECT SALES.PRODUCT_ID, PRODUCTS.ITEM, PRODUCTS.PRICE, SALES.QUANTITY, PRODUCTS.GROSS_MARGIN, Sum([Quantity]*[Price]) AS Sales, [Quantity]*[Price]*[Gross_Margin] AS Profit, PRODUCTS.WIDTH, PRODUCTS.DEPTH
FROM PRODUCTS INNER JOIN SALES ON PRODUCTS.PRODUCT_ID = SALES.PRODUCT_ID
GROUP BY SALES.PRODUCT_ID, PRODUCTS.ITEM, PRODUCTS.PRICE, SALES.QUANTITY, PRODUCTS.GROSS_MARGIN, [Quantity]*[Price]*[Gross_Margin], PRODUCTS.WIDTH, PRODUCTS.DEPTH, SALES.TRANSDATE
HAVING (((SALES.TRANSDATE)>=#9/1/2011# And (SALES.TRANSDATE)<=#12/31/2011#))
ORDER BY Sum([Quantity]*[Price]) DESC;
Nordic_Inventory1.zip
View 11 Replies
View Related
May 5, 2015
I am trying to get active member count by month in MS Access.
Table looks like this:
admin ID firstName sDate eDate 1 Adam 01/01/2015 01/04/2015 2 Ben 01/02/2015 15/04/2015 3 Carl 01/02/2015 13/03/2015 4 Don 13/01/2015
Required output would be like this
2015-Jan: 2
2015-Feb: 4
2015-Mar: 3
2015-Apr: 3
2015-May:1
etc..
I got this far:
SELECT DISTINCT count(a.ID) as mCount, year(dimDate.Date) as [Year], month(dimDate.Date) as [Month]
FROM admin AS a INNER JOIN dimDate ON (dimDate.Date >= a.sDate AND dimDate.Date <= a.eDate) OR (dimDate.Date >= a.sDate AND dimDate.Date = null)
GROUP BY year(dimDate.Date), month(dimDate.Date)
ORDER BY year(dimDate.Date), month(dimDate.Date)
Query4 mCount Year Month 31 2015 1 71 2015 2 44 2015 3 13 2015 4
View 2 Replies
View Related
