Clean The Records
Jun 29, 2006
here is the scenario:
i have a table that includes large number of records (10,000+) we call it raw data
the process is to "clean" the data by eliminating certain records: example eliminate records of Property Name that includes "restaurant" "golf" resort" etc....
The list of words that we want to eliminate (ie restaurant, golf, resort, etc...) is included in a separate table.
How can we produce a new table or query with cleaned records?:confused:
Thanks
View Replies
ADVERTISEMENT
Jun 17, 2005
I've built a Database which creates XML Files to import into another program. It just adds files to the program in a batch, so ever time I run the Export I want to Delete all the existing files for the holding folder. Have anyone done this before?
View 1 Replies
View Related
Apr 21, 2005
Hi all,
I have a linked table ( see attached image ). In it are several lines with the same invoice number. 1 line with this invoice number has some info I need to retrieve by serial number ( but this line doesn't contain the S/N). My question I guess is how can I retrieve a SerialNumber along with the 2 right most columns from the image?
IE: 0067320, A20900, 502060, 2120202019 23
I hope this makes sense. I was thinking of using a make table query to somehow clean this up, but not sure what to place in the query.
any other thoughts?
Thanks
Kevin
View 1 Replies
View Related
Mar 2, 2007
Is their a way to edit the following query so that it will delete from more than one table?
DELETE FROM tblSample
Or do I need to write a seperate query for each table I want to delete?
View 4 Replies
View Related
Apr 18, 2007
Hi,This is my first post here... I hope I am not asking a really stupid question. :)Basically I need to cleanly export an Access 2003 database to a TAB-delimited file... The database in question has 1406 rows and several columns... some cells have nothing in them -- this is a "contacts" database.Long story short, I need to export-out as TAB-delimited (or comma)... I have done this, but when I view my results in Excel, it looks like some things are messed-up... I think there are some tabs hiding around the database that I am not aware of.Any tips on cleaning-up my database for export to CSV (tab/comma)?Also, I have one field that is a dropdown list... When I export it out as CSV/TXT I only get a number and not the actual value of the chosen drop-down... Is this normal? How do I get the actual value and not just the number?Basically, I would like to just normalize this database and get it into a perfect-looking CSV/TXT TAB/Comma delimited file. :)I would be willing to pay for this help... I do not have much cash, but I really want to make sure I get this done right the first time.Many TIA's!Cheers,M
View 3 Replies
View Related
Jun 3, 2015
I have an access database that was set with security about 10 years ago.
I have now created another database in 2010 and I am importing all the objects to make a clean non secured database.
When I import tables it imports system table objects like MSysRelationships, but as it already exists it adds another MSysRelationships with a 1 on the end (MSysRelationships1).
Should I delete the original MSysRelationships and then rename the MSysRelationships1 as MSysRelationships.
There are numerous others like MSysAccessstorage, MSysACEs etc...
I am gathering that if I don't rename MSysRelationships1to MSysRelationships then the relationship data would be incorrect and the database wouldn't work correctly, but maybe I am wrong.
View 9 Replies
View Related
Jan 14, 2014
When we are managing incidents, users have decided on their own method of recording the incident... here are some examples
INC002546
INCIDENT002546
2546
INC# 2546
Inc# 2546
inc# 2546
Inc # 2546
The correct way is the following:
Inc# 2546
^ capital I, no space between "c" and "#" but a space AFTER the "#"
Then the 4 digit long number.I was thinking along the lines of trying to identify the number, removing everything else and then placing "Inc# " before it...
currently we allow notes, which should be made like such: Inc# 2546 - Notes.however, people have their own method for this too (however most of them are at least after then incident number so that makes things much easier)another issue is on the same database, some manual requests also appear which have different entry format: RQ# GK034LW2052 but that contains a mixture of letters and numbers... so when identifying the request number... as long as I make sure that it is a number and not a string of numbers and letters, I should be ok..
View 8 Replies
View Related
Jun 15, 2013
In an Access 2010 form is it possible to export select records and fields in those records to a specific location?
Code:
Set objDialog = Application.FileDialog(4)
With objDialog
.AllowMultiSelect = False
.Title = "Please select a File"
.InitialFilename = "C:"
.Show
If .SelectedItems.Count = 0 Then
MsgBox ("Action Cancelled")
Else
[code]....
The user can select the directory using the code above, but can specific fields in records be exported to a excel workbook in that selected directory?For example, if the are 5 records in the database can the fields LastName,FirstName,BirthDate in records 1,2,3 be exported to Setup.xlsx in that selected directory?
View 1 Replies
View Related
Nov 21, 2012
I have a table (tbl Team Info) which contains names and codes for teams within my business (>400 records) and another table (tbl Process) which contains a list of high level tasks (30 records).
I need to create something where for each team name 9in tbl Team Info) I can map them to the tasks that they undertake (in tbl Process) and assign a percentage of time then spend on each task. Each team could map to several different tasks.
View 3 Replies
View Related
May 29, 2014
In my simple database (attached), I need to mass duplicate Tasks and their Notes.
I have three tables: tbTasks (PK: Task_ID), tbNotes (PK: Note_ID), jtbTaskNotes (FKs: Task_ID and Note_ID). jtbTaskNotes is my many-to-many junction table that ties Tasks to Notes.
The main form (fmTasks), bound to tbTasks, has a subform (sbfm_TaskNotes) that displays notes associated with each Task. On themain form,you select which Tasks you want duplicated via a checkbox. The append query (quCopyTasks) will duplicate all tasks that have the checkbox checked. All good there. However, I can't figure out how to also duplicate each task's Notes.
I found Allen Browne's solution [URL] ....., but that only handles duplication of one record at a time, whereas I need to duplicate many records at a time (sometimes 10+ records). How do I go about duplicating multiple Tasks and their associated Notes?
Before you ask "why are you duplicating records?": There are times when tasks need to be re-accomplished and therefore need to have a new record. It's easier to duplicate records than it is to hand-jam everything again.
View 5 Replies
View Related
Jun 27, 2013
I am trying to get the records on start and end date, still showing error no records found.
My code is like this:
Private Sub Command90_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strReport As String
Const strcJetDate = "#dd/MM/yyyy#"
[code]...
View 1 Replies
View Related
Mar 22, 2013
One shows my form with the Transporters Subform with 3 entries, and 1 entry.The three line items that say "Transporter" are in one subform. I used this code
Code:
Private Sub Form_Current()
If Me.RecordsetClone.RecordCount >= 3 Then
Me.AllowAdditions = False
End If
End Sub
to limit the number of records I can add to 3 or less.My issue is that I lost the blank text box that allows you to add another record. So, if I only have one Transporter listed, there's no box to let me add a second or third.I have the following properties for the Transporters Subform set to "Yes":
Data Entry
Allow Additions
Allow Deletions
Allow Edits
Allow Filters
View 3 Replies
View Related
Nov 16, 2004
I have built a qry that initially shows the correct information. For example.
tblContent has 289 records with a Type = Class.
I built a Query to select from tblContent Type = Class and I get 289 records. I add additional criteria of Progress <>"Not Scheduled", I then get 206 records. I then add additional criteria Last Name <>"Demo" And <>"Care" And <>"Support". This brings up 200 records, but the query appears to duplicate each record 3 times. I do not have 3 of the same types of records.
The SQL Statement is below
SELECT tblProfile.LoginName, tblProfile.FirstName, tblProfile.LastName, tblProfile.Organization, tblProfile.CostCenter, tblContent.Title, tblContent.Type, tblContent.Code, tblContent.[Date Assigned], tblContent.[Date Started], tblContent.[Last Accessed], tblContent.Progress, tblContent.[Date Completed]
FROM tblProfile INNER JOIN tblContent ON tblProfile.LoginName = tblContent.LoginName
WHERE (((tblProfile.LastName)<>"Demo" And (tblProfile.LastName)<>"Care" And (tblProfile.LastName)<>"Support") AND ((tblContent.Type)="Class") AND ((tblContent.Progress)<>"Not Scheduled"));
The qry is named qryPhysical Class. I have provided the link to view the database. Can you help me?
http://briefcase.yahoo.com/turnerbkgabrobins
Thank you in advance for your assistance.
View 1 Replies
View Related
Jan 12, 2014
All seemed to be working well, however, I noticed that all my subtable records in the database are exporting with each Primary table record. In my output, I'm looking to see each primary table record followed by one or more subtable records from a one to many relationship.
(Office 2010) Access/Word
Private Sub cmdPrint1_Click()
Dim objWord As Word.Application
Dim docm As Word.Document
Dim db As DAO.Database
Dim rstLandSales As DAO.Recordset
[Code] ......
View 14 Replies
View Related
Feb 7, 2014
I have a table InvPrice and Updated Pricing
Need to delete all records from InvPrice that Match UpdatedPricing
InvPrice.StockCode = UpdatedPricing.StockCode
InvPrice.PriceCode = UpdatedPricing.StockCode
I have tried something like this...
Dim dbs As DAO.Database, sql As String, rCount As Integer
Set dbs = CurrentDb
sql = "DELETE * dbo_InvPrice Inner Join (dbo_InvPrice Inner Join UpdatedPricing on dbo_InvPrice.StockCode = UpdatedPricing.StockCode ) ON on dbo_INvPrice.PriceCode = UpdatedPricing.PriceCode "
dbs.Execute sql, dbFailOnError
View 14 Replies
View Related
Dec 12, 2014
I have a form which needs update ever month. When the form is opened, the end user can see the old records and data, and also a new record is added for any new data. I want to protect the old saved data and the user can only add, edit, or delete the new data in the newly added record. The problem is once a user adds the new data and moves to another record or another form, then he/she cannot edit or change the new data in case if there is any mistake or need to change something after couple of minutes.
I changed the Form Data Properties "Allow Additions", "Allow Edits", and "Allow Deletions" many different times and situations to solve this problem but with no success. I tried the following with NO success too: One of the Fields of the Record is (Month). In the Data Properties, I set a Default Value for this field as(December 2014) for instance. I set the Data Properties "Allow Additions", "Allow Edits", and "Allow Deletions" to (Yes). Then I put the following code in the Form's Current Event:
Private Sub Form_Current()
If Me.month.Value = "December 2014" Then
Me.AllowAdditions = True
Me.AllowEdits = True
Me.AllowDeletions = True
[Code] ....
View 3 Replies
View Related
Oct 31, 2007
Hi,
I was wondering if anyone would be kind enough to help me out on this problem I have.
I have a table:
---------------------------------
Source | LinkedTo
---------------------------------
A1 B2
B2 C1
B2 C2
C1 D1
C2 D2
C2 D3
B2 D4
A1 D5
Another table:
-------------------------------
Item | Load
---------------------------------
D1 2
D2 4
D3 6
D4 8
D5 10
So looking at the tables,
C1 = 2
C2 = 4+6 = 10
B2 = C1 + C2 + D4 = 20
A1 = 20 + 10 = 30
Here, there are 4 levels that I have to go into to get the grand total. If I did not know how many levels there were, how would I be able to do this through code? through queries if possible??
If anyone could help, that would be great! Thank you in advance.
Bear
View 3 Replies
View Related
Mar 19, 2007
I want to find duplicate records based on FirstName and LastName and delete the duplicate. Also, I want to delete any records which have a blank FirstName and LastName.
How can I do this?
Thanks,
Dave
View 3 Replies
View Related
Jul 28, 2005
I have form setup with two cascading combo boxes (facility and date) to select a record (other navigation, including mousewheel, is disabled). There is a button to open another form which allows addition/deletion of records by facility and date. When this form is closed, the combo boxes update accordingly on the initial form. However, if I select a new record whose facility/date I have just entered, it seemingly shows the wrong values for several of the other fields. If I check the corresponding table, the values are actually at default, though, and when I reopen the form they have reverted to default. Any ideas on how to solve this? I'm tempted to simply have the first form close and reopen when the second is closed, but couldn't figure out the syntax to close a form with a button on a different form. Let me know if there's any more information I need to provide. Thanks for any suggestions.
View 9 Replies
View Related
Nov 26, 2005
I have a mainform with 4 fields which are marked with 4 primary keys: soldtoId, shiptoID, PlantId and formulaID. PlantID is a long integer number field and the other 3 are text fields. there is no default values.
I have a subform1 that is linked by PlantID and FormulaID. These 2 field names are in the linked child fields.
I had a handful of records established already. Today, i added a command in the after update section of the PlantID field in mainform by adding Me.Refresh.
So when i change the plantID in the mainform, the subform gathers different plantID and formulaID data automatically. works nicely.
Here is the rub. I next tried adding NEW records to the mainform. that's the one with 4 primary fields.
i enter soldtoID and shiptoID fine. they have no refresh need. however, after the enter a PlantID, the me.refresh kicks in before i can get the the formulaID field. i get a debug error. something about a NULL. which i suspect is the formulaID that i didn't get a chance to enter to. FYI, later, i want to put a refresh on formulaID too.
so how can i make some kind of Refresh work for both New and existing records??
penwood
View 8 Replies
View Related
Dec 16, 2013
I have a user login code am using for users to login and only access their records in a subform according to their user ID and access level (user or admin). On the main form I have a user name field, the password field and two other fields: one to hold userID and the other to hold access level.
I also want if the person logged in has "Admin" access level the subform should return records from all users. For now what is happening is that even when I assign myself with "Admin" access level, the subform is not returning all records from other users, but my own records only. The original demo where I found the code worked well but I cannot find it and I am totally lost on this.
Here is the code I have so far:
Code:
Private Sub cmdLogin_Click()
Me.Hold_User_ID = Nz(DLookup("User_ID", "T_Users", "Username='" & Me.UserName & "' and pword='" & Me.PWD & "'"), -1)
' check to see if we have a good login
If Me.Hold_User_ID = -1 Then
MsgBox "Invalid username or password."
[code]...
View 9 Replies
View Related
Apr 22, 2013
I have a database with a table with company names, then a relationship to another table that shows that companies' address, but I also want there to be an address 2 and 3 and so on, and some of our companies have multiple sites.So what Im asking is how would you be able to show multiple data, on a sing record.
View 3 Replies
View Related
Jan 27, 2014
I'm not sure what I did, but when I enter data into a form, say it's record 10 and I click record 11 to start a new record. I can then go back to record 10, and it's still has the data I entered in the form. As you know this part is normal.
The problem is when I save and close the form and reopen it, record 10 now has the data from record 9, and the data I had entered for record 10 is moved to record 1, which is in front of my other records. It continues to do this, and I am now at record 4, which should be record 14 and I do not understand why it's doing this. At first I thought it was deleting my new records until I found them in the front of my already created records.
I could just leave it, since all my records are still there, but it's frustrating expecting your last record you enter to be at the end, instead to find it somewhere else.
I'm still learning, so I hope it is just a setting or entry I need to change.
View 1 Replies
View Related
May 30, 2013
I currently have a database that I had to copy over each field from another databse to create the one i have now. When i copy the records from one field over it appears they all move up three spaces for some reason is there a equation i can enter to make them all move down three records in the table?
View 2 Replies
View Related
Jun 16, 2014
How can I get a Query Criteria To Select All Records or specific records in query design section.
I have a table that shows many departments with credit card transactions. I like to run a query to see specific department, or have an option to see all the departments when the query is run.
View 2 Replies
View Related
Mar 4, 2015
I am looking for a query that will return records from a table that have related records in another table. Opposite to the Unmatched Query Wizard.I have two tables: tblSupplier and tblSupplierProducts.The two tables are related by the field "SupplierId".I need the query to only return Suppliers that have Products.
View 3 Replies
View Related
