I have:
Database A with fax numbers:
Example:
- 1. John. W
- 2. Mark. Y
- 3. Sean .S
I exported to a dbf file to do some mass faxing and realised that Mark's fax number is no long in use. (error report by the fax software)
I can export the error report and save as dbf.
Is there a way to make use of this dbf to update and remove Mark's record from Database A?
I have:
Database A with fax numbers:
Example:
- 1. John. W
- 2. Mark. Y
- 3. Sean .S
I exported to a dbf file to do some mass faxing and realised that Mark's fax number is no long in use. (error report by the fax software)
I can export the error report and save as dbf.
Is there a way to make use of this dbf to update and remove Mark's record from Database A?
hi
i have a form with various fields, on this form i have an archive button that places certain fields in an archive form. however this does not remove the information from the main form. pls see attached
any ideas, as some will know i'm a newbie
Ok well I thought I was doing this right but it appears I have not. I am drawing data from two tables. Both of them related by the person's name. One table I am pulling the unique ID number, the other table contains the name. I have a PO (Purchase Order) field being pulled from the table that holds unique ID. Or lack there of, so I am pulling all the records that contain no PO number. However duplicate unique ID's are being pulled. The way my query is set up now is:
Unique ID Number
Name
Count (Number of Procedures)
Charges
PO Number - Criteria: "Is Null"
I have also right click in the table view area, in the properties I selected Unique values. I have to assume that this would have held back any Dupes from the output. However this is not the case. When I looked in the help files the only thing I could find was:
http://office.microsoft.com/en-us/a...0840151033.aspx
Quote: "If you want to see only unique values in a result set, you can specify that you want to exclude duplicates from the result set.
In the Database window, click Queries under Objects, click the query you want to open, and then click Design on the database window toolbar.
Right-click the background of the Diagram pane, then choose Properties from the shortcut menu.
Select DISTINCT values.
The Query Designer inserts the keyword DISTINCT in front of the list of display columns in the SQL statement.
Note If you use the DISTINCT keyword in Microsoft SQL Server, you cannot modify the data in datasheet view."
The problem with this is my properties box does not offer a Distinct Values option. So my question is. Is there an expression I can place in the Unique ID field that will exclude any duplicate records.
Sorry for the book
Sean
I changed the name of a form referenced in the Autoexec macro from "frmLogin" to "frmLoginPW". However when I Open Access 2010 instead of Form "frmLoginPw" opening I get a message box saying that the system can't find "frmLogin". If I do a search through all the code in the project there is NO reference to "frmLogin" as I changed any code to refer to "frmLoginPW" and recompiled the project.
I can not get rid of this error and if I press the OK button on this error message ( from MS Access not my error coding) the "frmLoginPw" opens and the code in the form works. (Password authentication etc etc.). I deleted the AutoExec macro and remade the macro and referred it to the new form "frmLoginPW", this did not work. I loaded the Accdb file using the Run command and the /Decompile command line parameter to no effect.
I'm having a problem removing duplicate records from a table. It should be easy but I can't suss it.This is an example of the data in my table:
Code:
RefDateStatus
113007111/06/2015Do Not Pay
114454306/07/2015Do Not Pay
115760714/05/2015Do Not Pay
116520705/05/2015Do Not Pay
117670108/05/2015Do Not Pay
118036218/05/2015Do Not Pay
118517015/05/2015Do Not Pay
178734020/07/2015Do Not Pay
182809915/07/2015Do Not Pay
184226010/07/2015Payment Due
184226022/07/2015Payment Query
As you can see, there are 11 records here but the last two records have the same reference number. I need my query to show the first 9 records + the record from the bottom two with the latest date (22/07/2015).
I currentlyt have a search form that searches criteria based on a textbox. After entering data a macro runs, and opens up a query with the results. After that query opens i have macro that runs a duplicate query off of the query initally opened based on the search results. What i would like to have is a macro that removes the duplicate records from the duplicate query and display the remaining records.
View 1 Replies View RelatedIm trying to write a query that shows all the container movements. Yet when I run the query qryFullHistory I get a duplicate value for container Off Island. Ive tried adding some criteria that says that the DateRequested has to be between the ImportDate and ExportDate but that doesnt seem to work. There are duplicate entries for container Off Island in tblContainerDetails as the same container has arrived and left and then returned on another voyage. Yet there is no entry for the second voyage in the tblMEMRContainer.
A brief description of the tables is:
tblMEMR Movement requests details
tblMEMRContainers the containers that were moved on the movement request. There can be more than 1 container for each request.
tblContainerDetails details and dates for the container when it arrived and when it left
There are other tables but these are the 3 that are used in the query.
I have a table with at least 13.000.000 records. There are many duplicates records... For example
ID Name Family mobile car number chassis Register_Year
1 Roy Jalbout 9999 123456/G ASF4546 2005
2 Roy Jalbout 9999 854658/G GRK554JFJD 2009
3 Tony Elishah 1234 854658/G GRK554JFJD 2012
4 Sam Markos 5478 854658/G GRK554JFJD 2014
5 Roy Jalbout 9999 123456/G ASF4546 2005
As you can see ID Number 2,3,4 have the same car but every one bought from another so it's not duplicates
The duplicates here is the ID's number 1,5. So how to remove the duplicates. I remember you i have at least 13.000.000 records. I try to make a query to find duplicates then i make a copy of the original table and than i should make a primary key then append the data from the original table to the copy table, but here i have more than one criteria
When I made a query to find duplicates the result was 680.000 records and every one have a minimum 2 duplicates an maximum 4 duplicates so it's about 2.500.000 duplicates records at least....
I have a dataset of about 70 columns but would like to remove any two rows that sum to zero (based on two columns) if the column "Units" are the same for the same "Bar code" thus to say "for the same 'Units' (for any two lines), check to see if their respective 'Bar Codes’ are the same and if so remove if their amounts sums to zero".
I have the following code which seems to be working but for relatively large data ( about 5k), it does not select all the data that meets the criteria:
Code:
SELECT *
FROM datatable
WHERE ((([datatable].[Unit] & [datatable].[Bar Code] & Abs([datatable].[Amount]))
In (SELECT datatable.Unit&datatable.[Bar Code]&abs(datatable.Amount)
FROM datatable
GROUP BY datatable.Unit&datatable.[Bar Code]&abs(datatable.Amount)
HAVING count(*)>= 2 and sum(datatable.Amount)=0)))
ORDER BY [datatable].Unit & [datatable].[Bar Code] & Abs([datatable].Amount);
identifying the bug in this code that makes it not pull all the data meeting the criteria.
See sample data below:
# Unit bar Code Amount
1. AAB Mac1 2.75
2. AAB Mac1 -2.75
3. AAB Mac1 1.24
4. AAC Mac3 35.00
5. AAC Mac3 -20.00
6. AAC Mac3 20.00
7. AAD Mac3 16.11
8. AAC Mac2 11.00
9. AAC Mac2 -11.00
10 AAC Mac3 12.05
11 AAF Mac3 -12.05
12 AAD Mac3 -16.11
13 AAC Mac4 35.00
I want to select records # 1, 2, 5, 6,7,8,9 and 12 since they are same units having the same bar code and their sums total zero.
I'm creating a job/timesheet database and have a problem...
I have a main timesheets table that includes every job/piece of work thats been done (sometimes multiples for each employee each day). Each record contains a ref to the employee, ref to the contract, ref to the type of work, time taken and date.
There are then various tables that support the main table, which provide actual employee names etc.
I want to produce a query that provides the number of hours undertaken on each day of a particular week for each employee. So I'd have one row for each employee from the employees table, then a field for the sum hours for Monday through Friday.
The way I was going about this was
- to create five query's for the main timesheets table that would limit the entries to the five days in question - Monday-Friday. That worked fine.
- to then create a query that takes the employee name and using each of the five day based queries perform a Sum Total on the time field to give me the total hours worked for each day for each employee. That also worked fine, but the trouble is it removes any null values. So, if I only include the first day there might be 80 employees listed, but when I include the second day as well that goes down to 72 - presumably as 8 employees who entered timesheets for day 1 didn't for day 2. By the time I add all five days, I have almost no employees. I assumed that there would be a query level property to set, but i can't see one.
Also, is there a simpler way to do what I want? If I can get this working I'd like to replicate to breakdown by contract, job type etc in the same way as I have with employees.
i have a form porblem. I have a search form embedded onto my main form its really a listbox that when i click the records in it they display the record.
prob;em is that when i delete the record it still shows in the listbox
I have a database with an import process which normalises incoming data and appends to various tables. No issues with that. I also have a function within that process which counts the number of new entries for a summary popup when the process has completed.This works by querying the staging table, prior to the append, into a recordset and using the .RecordCount to increment the count (multiple files can be imported at once so this effectively provides a running count, per file, to give a total for the whole import)
I thought it was working fine but this morning I noticed that the count which appeared on the popup was 1 greater than the number of actual new records. I checked the source files and noticed that, for whatever reason, there was a duplicate entry in there. So I presume that's why the count was out by 1.
There's no integrity issue in the main tables as the composite primary keys ensure that duplication shouldn't be a problem. Indeed, the record in question, duplicated in the source, appears only once in the main table post-import. So not too worried about that.
However, I need the count in the popup to be accurate (it tells the users how many new entries require further investugation). And what's puzzling me is that I use DISTINCT in the query, which I would have thought should eliminate any potential dupes in the recordset and thus provide the correct count. It seems it doesn't?
Code:
Public lngNewBalancesTBI As Long ' Defined in a separate module...
-------
Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String
[code]....
Why the dupe, which is still present in the staging table, also makes it over to the recordset, even though I'm using DISTINCT?
I'm not good with access at all, i'm basically after removing all workgroup protection from a database file. I have full access to the file, admin passwords etc, i'm just having serious difficulty trying to find a tutorial or any information regarding how it can be removed!
View 2 Replies View RelatedI am looking to create a make table query to eliminate some portions of numbers from a field.
i have 23899999999
I need to parse 899999999 into a new field.
Is there a formular for that. Do I use a LEN formula.
I use the template service call management . In the work order section there is a field called Entered By . I would like to remove it as I do not need it and if you do not enter it the work order will not complete . Is there a way to remove it or make it so you do not have to enter anything there >> I have tryed to remove it but somehow it is connected to some thing else and I get error
THXS for Help
Steve
Hello,
I am totally new at this database stuff. I have been searching the archives for a couple hours and confused on how to accomplish what I need to do. I saw references to using a module but I have not used that feature yet.
I am using Access 2003. I need to remove/delete " - " space dash space, "-" and "&" from a string in one field (DGName). I'm trying to do this with a make table query
DGName
P1000
P1000 - SMLS
P1000-CA (not a type error)
UD000 - C&B
V-NET
and I want it to come out like this
DGName
P1000
P1000SMLS
P1000CA
UD000CB
V-NET
Thanks
Sean
Hi there I am extracting user names from e-mails in an access database.
I built the following command to do it
Left([E-Mails by Source - Test]![E-Mail],InStrRev([E-Mails by Source - Test]![E-Mail],"@"))
where [E-Mail] is the e-mail address.
It works great. But I still get the @ and the end of the name.
Any idea how I can get rid of it (other than using find and replace)?
Thanks for your help.
I posted an earlier question about an Access query export to Excel putting leading apostophe (') before all data.
To remove them I thought I would use Find/Replace but the Apostophe is not recognised!
Is there a way (programmatically) to remove the first character of each data entry in each cell in a column?
thanks in advance
nel
Can anyone please help me, I currently have a series of queries that are called by a macro, these create a number of tables. But when each query runs it provide a prompt stating what it is doing, my question is can these be stopped so that the queries run without a user being present.
Thanks
I have a table that has mutliple records for the same person. I know how to create a query to remove duplicates but i don't know how to create a table to remove duplicates but keep the most recent record. Is this possible?
ie
client status date
123 A 1/1/07
123 C 2/5/06
123 A 9/3/07
results
client status date
123 A 9/3/07
When I ve gone in and deleted a record from my table (new table just testing it) ie. enter number 1 deleted it and than gone in and entered (on the form) another enter the (auto number) goes to 2.
I want to know how to i get it to go back to the previous number.
thanks
I have two tables: “tmptable” and “CC_CODES”
Both tables contain the same data; however the “tmptable” contains updated data. How can I compare both tables and remove all the duplicate in the tmptable, leaving only the updated data.
I will be comparing the fields “IsMandatory” from both tables
I have a field that contains serial numbers. The serial numbers are entered in difference formats. Some will have dashes, spacing and periods separating the numbers (example: 06-65432 or 06 65432 or 06.65432). I am trying to create a search field for this and the different formats makes it difficult. Is there a way to remove all separators so that all parts of the serial number are together?
View 2 Replies View RelatedI am running a query to return records from a table. I want to eliminate records from the query if the values contained in one field are duplicated. I have tried using the DISTINCT keyword but it only works if the entire record is unique or if I was retieving just one field
Any suggestions appreciated
can someone help:
in a form I've created showing products bought, when I add a new new order it still keeps the previous order's data, how can I prevent that from happening?
cheers