I have two tables "Master List" and "Audit". I want to delete the records from the "Master" that match those in the "Audit". I am using Access 2010 and have used it often but have built very few queries.
View RepliesHi
Apologies if there is a previous post that answers this - I've looked, but can't find anything that works.
I have two tables with identical structures. tblA contains a subset of the records on tblB, with identical values on all fields except ID. I need to remove from tblB all records appearing on tblA. I thought the following would work:
DELETE tblB.* from tblB
INNER JOIN tblA ON tblB.Field1 = tblA.Field1
AND tblB.Field2 = tblA.Field2
AND tblB.Field3 = tblA.Field3...
but I get "Could not delete from specified tables".
What am I doing wrong? Or is there an easier way?
Dave
If a user wishes to delete a record, rather than deleting the record i'd like to change its status to not current for instance.
The reason being that the information in the records may be useful for statistical use.
I added a yes/no field to the table and then added some code to a delete button to change the yes/no.
I can't seem to hide these records from my forms (using filter) but I could be going the wrong way about it.
i have a data base with a primary key but some records have been added from a excel data base and now the data base has duplicate records with different primary keys. I do a Query looking for duplicate records based on a field not the the primary key. There are 315 out of 22000. I can sort the table and then manually delete the dups. The Query does not allow deleting records so finding the culprits in the full Data base is time consuming. How can i delete the duplicate records using a query? Is this function available hidden someplace in the guts of Access? Seems like this would be a frequent problem. The Microsoft forum addresses this problem and the only viable solution is the manual method, when they get to the Query method it looks like they just forgot what the subject was and tell you how to delete one record if you know what the criteria is. If two non Key fields are Identical then i want to delete one of the records.
View 3 Replies View RelatedI have three tables: tblProducts1, tblProducts2 and tblProductSales.tblProducts1Code CostABC 20BVC 35ABC 30tblProducts2Code CostABC 10BVC 55ABC 20tblProductSalesCode RevABC 70BVC 25ABC 20BVC 15DCC 33I want to produce a query that looks like this:Code Rev Cost ProfitABC 90 80 10BVC 40 90 -50DCC 33 0 33How can I do this?Thanks,Jon
View 1 Replies View RelatedHi,I have two tables with these set of data:Table1Filed1 Field21000 A1001 B1002 C1003 D1004 ETable2Filed1 Field21000 A1002 C1003 D1005 F1006 GI need to create 3 Tables with following out put.1. Data that are common to both Table1 & Table22. Data that are in Table1 but do no exist in Table23. Data that are in Table2 but do not exist in Table1 Can anyone help me to find the answer please.CheersBud
View 1 Replies View RelatedIn a database I have two tables, one is linked to an excel sheet (our customers order) and the other is created via a "make-table query" from our business system.
In both tables I have the customers part numbers and neither contain a primary key.
What I need to do is compare the part numbers in both tables to find if a part number is present in the order but not in our business system.
I just cannot figure out how to do this.
Any ideas are greatly appreciated
/twallstr
I have 3 tables that all contain Car registration numbers.
Table 1 contains just Reg numbers. Table 2 contains Registration numbers with an additional 2 columns of data. Table 3 also contains Registration numbers with an additional 2 columns of data.
I need to compare the reg numbers in Table 1 with Tables 2 and 3 and where the same Reg number appears in either of Table 2 or 3 display the results in a new table / query.
ie, Table1 Reg, Table2 data , Table3 data Note there are some Reg numbers that will appear in all 3 tables.
any help appreciated.
Malc
I have two tables, one has two fields:student ID and student name.its kind of like this:
1 Mark
2 Tom
3 Franklin
the other table has three fields: student name, student classes, it goes like this:
Mark calculus
Mark Biology
Tom Statistics
Franklin Calculus
Tom Chemistry
what I want is for the second table to have its related id from the first table so it could be like this:
1 calculus
1 biology
2 statistics
3 calculus
2 chemistry
I cant simply make find and replace because the records are a lot is there another way should I relate the tables of something how will it work?
Im using Access 2000.
Essentially I want to find out which of the entries in my master table have matching entries in my other tables, and list the ones that do.
So if I have an entry in the master table for “productA”, and there are also matching entries for “productA” in tables “SupplierC” and “SupplierD”. I want to perform a query that will output a list showing “SupplierC” and “SupplierD” (I have A & B tables but if there isn’t an entry in them for “ProductA” I don’t want them on the list.)
I intend to use the results to populate the values of a combo box in future so I require the list to be in a single column, rather than across many columns. Does anyone know if this is possible?
My master table is called “OurProductsTable” and the four supplier tables are called “SupplierA”, “SupplierB”, “SupplierC”, and “SupplierD”.
Each Table has a primary key called “ProductID” and I have linked them together on the relationships screen.
I’m not sure if this is the proper method but I also made another field in each of the supplier tables called “CompanyName” and set the default value as the name of each supplier, so if the entry for supplierC matches the master table entry I can return the “CompanyName” value of “SupplierC”.
Here’s what I thought the code should kind of look like but I don’t know how to apply it properly in a query:
Well, I have two tables in an access file one table having customer data base along with the customer name and another table only having the customer name. Now the table having only customer name need not be having the exact customer name in terms of spelling or spaces but both of them will same for e.g. table 1 - Apple and in table 2 - app let. Therefore the system should match this and throw out the results into a different new table as matching results.
View 2 Replies View Related trying to create a query in which I can select a count across two tables with matching postcodes.
Table 1 : Address database with column called 'STRIPCODE' (e.g. ME8, ME9, ME10)
Table 2 : Area Code table; each column has a list of area codes to match to the STRIPCODE column. (e.g. MEDWAY column will have all of the relevant area codes)
I would pretty much want a count of each match as below :
STRIPCODE
MEDWAY 650
BLACKPOOL 563
HULL 352
Have a few hundred areas to match and tired of going through these manually and entering counts!
Hi,
I have two tables of titles (DVDs and CDs). Each table has a price associated with each title in an adjacent column. I would like to match the titles between them and compare prices. Any help would be greatly appreciated. Thanks. EDS
I have two tables, one being an 'Address' file and the other an 'Area' file which is a list columns ranging from 1-400 with groups of district level postcodes. For Example; column 1 will have postcodes such as BN1, BN2, BN3, BN4, BN5 etc. Each column has a separate group of postcodes.
Now, my 'Address' file also has a column within this labelled 'District' in order for me to create a straight join (between column number and District) in a query to count each group individually, but of course, do to this 400 times becomes quite tedious! (to say the least)
I would require the outcome to be as follows :
Area file - Count
1 - 469
2 - 201
3 - 0
4 - 3598
5 - 268
. - 101
. - 8936
. - 563
399 - 287
400 - 41
I have 2 table
Table1 only one column
name
AA
BB
CC
Table2 two column
name number
AA 1
AC 3
AW 8
AA 6
AA 5
DF 4
EE 8
CA 9
CC 7
I want to sum the table2 number when the table2 name match any name in table1
So the answer should be 1+6+5+7
:confused:
I am Developing a simple VBA program for Access for my final year of my Degree and I have come up with a problem that I don’t seem to be able to fix.
I have create tables using SQL
SQL = "CREATE TABLE tblInvoiceLines" & _
"(InvoiceNo LONG, PartNo LONG," & _
"Quantity LONG)"
DoCmd.RunSQL (SQL)
Deleting this with the below code works,
SQL = ""
'This will delete the Invoice Line Table
SQL = "DROP TABLE tblInvoiceLines"
DoCmd.RunSQL (SQL)
But once I alter the table with the below code the Delete command doesn’t work, does anyone know how I can fix this problem?
SQL = "ALTER TABLE tblInvoiceLines " & _
"ADD CONSTRAINT fkInvoiceNo " & _
"FOREIGN KEY NO INDEX (InvoiceNo) REFERENCES " & _
"tblInvoices (InvoiceNo) " & _
"ON UPDATE CASCADE " & _
"ON DELETE CASCADE "
conDatabase.Execute SQL
Thanks
Coops
Hi i have a lot of linked table that i want to delete I have the macro below that deletes one at a time but i have to put in the name is there a way to delete all the ones that end in _cfd_posting as before this name there is a six digit number but these r all different is this possible
Sub delete()
DoCmd.DeleteObject acTable, "060204_cfd_postings"
End Sub
In my program you are allowed to make backups of the current databases to an external file. It also allowes you to get the tables back from one of those files. At least it should.
I thought this wouldn't be a biggy. I thought I'd just delete my current tables and replace them with "docmd.tranferdatabase" acImport.
But the stupid thing wont let me delete its tables because they are connected to each other. Can I somehow delete the link before deleting them?
If so will the links be there again once I restore the tables from the backup?
Or do I need to reconnect them somehow to each other.
Or maybe another approach would be to import them without deleting the original ones. Thus the new ones get the same name of the original ones + "1" or something. Than I delete all records from all my original tables. And refill them with all the records from the new ones (the + "1"-ones) And delete the new ones. And if I would do that, would it be best to use AcImport or AcLink (from docmd.tranferdatabse)
Can I have some pro's oppinion on this plz.
I was wondering if there is a way in selecting all tables and deleting at one go instead of selecting one by one. Perhaps a little bit of programming. Tx in adv
Shah:)
Hi
I am trying to compare data in two different tables and list the data that does not match. Not sure if this is possible. Details below
Table 1
xyz - A1 - Do not use
ABC - A4 - Use
Table 2
123 - A1
The result of the query should show me
A4 (as this does not appear in table 2
I am not too familiar with Null values as there will be many null values that i want to ignore.
Any help will be appreciated.
I am undertaking an analysis of corporate mobile phone data based on data from the phone provider. The base data consists of a number of monthly text files at transaction level for voice calls, data usage, billing/tariff information, international calls/transfers etc. I have set up VBA code to import the text file data into a number of Access tables.I would like to have a way of joining these tables but the only common field is the phone number (a text field with the groups of numbers separated by hyphens).
I confirmed that joining the tables on the phone number text field does not work.My idea was to create an additional table with just one row for each phone number and link that to the other tables by the ID in the new table. I was able to create this table [PhoneNumbers] (by creating a totals query of the phone numbers from the main call transactions table, I then dumped it into Excel and then imported it into a new Access table with an auto-generated ID column).My problem / challenge is how to get the ID column from my [PhoneNumbers] table to appear in each of the other tables so that I can join them effectively. In the Excel-world, I would have used a vlookup function.I even thought of performing this as an interim step in Excel but there are too many records / rows in some of my tables. It seems that the dlookup function is not what Im looking for and even if the IIF function is suitable, I cannot get the syntax to work for me.
I have 2 tables that must be involved in this query. Both have the same fields; one is a temporary table created from an Excel import which will be deleted later, the other is the permanent table.
tblStoreProducts is the permanent table. It has a 2-field Unique index; there can only be 1 of any ProductKey for a StoreKey (there can be multiple ProductKeys within a StoreKey, and multiple StoreKeys for each product, but there can only be 1 entry for each unique combination of ProductKey/StoreKey).
tblImportToStoreProducts is the temporary table. It holds all of the ProductKeys for one StoreKey.
My query needs to identify any ProductKey in the tblStoreProducts that does not exist in the tblImportToStoreProducts for that StoreKey, and then it must set the MaxUnits field to 0.
Basically, the temporary table is this years' inventory for a particular store. If a product was in that store last year but it isn't there this year, the quantity must be set to 0 because the ultimate aim is reporting hazardous materials.
My problem is that I am trying to update a field (called 'Sold' which is a yes/no checkbox column) for a specific record whenever an event is triggered. I have two forms (derived from two tables), one is called frmInventory and the other is called frmSales. In frmSales, I made a combo box called 'cboItemID' that allows the user to select from a list of items from my inventory table. Each selection from the list has 4 properties, the first of which is the 'Item ID' from the inventory table. Lastly, I have a field in both frmSales and frmInventory called 'Sold' as mentioned above. What I want to do is that whenever I check/uncheck the box in the 'Sold' field in frmSales, I want the 'Sold' field in frmInventory to check/uncheck as well, but only in the record with an 'Item ID' that matches the 'Item ID' from the combo box selection. In other words, I want to match the 'Sold' field in frmInventory with the 'Sold' field in frmSales, but for only the record that has the same 'Item ID' primary key as the one I picked from my selection in the combo box from frmSales.
how to reference another table and check whether or not it's 'Item ID' primary key is identical to the one I specified from the combo box, and then take action to update the 'Sold' field if the IDs match.
I have a table tROE with a field listing all dates starting 1/1/10 to date (populated), and three fields for currency exchange rates [USD], [RSD] & [EUR] which are empty and need to be populated. I have another table tROEPartial that has the exchange rates for some of the dates starting 1/1/10 but not all. Their structures are identical. I want to add the exchange rates from tROEPartial to tROE where the dates match, leaving the unmatched fields in tROE blank.
View 3 Replies View RelatedI am trying to make a database for a shop which can do several things:
1) I want to give in what is being bought by the shop to make their products form. This has to be saved in a table named STOCK.
2) I want to be able if I say in a form that from these things I made that product that these things dissapear from the STOCK table and that the final product is added to the STOCK.
3) If the final product is solled I want to remove it from stock.
In the end if I want to see what is still in the stock I will only have the things that are still there because the rest is removed.
How can I do this??
Thanks for your answer
Hi.
I have a cluttered database with too many 'no longer necessary' queries and tables.
Is there a way (other than deleting them one at a time) to delete multiple ones at once, as I can in My Documents, et cetera?
Russ