Matching Two Fields In Two Tables
Nov 13, 2006
In 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
View Replies
ADVERTISEMENT
Jun 16, 2013
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.
View 8 Replies
View Related
Apr 30, 2012
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 Related
Jun 19, 2007
Hi All,
Wondering if you can help I have a table called "example" which has field "a" "b" "c" "d"...
I then create another table called "importtable" with field "a" (imported in from Excel)...
I would then like to create a query which matches any and shows all the records which I imported in from Excel to my current table called "example" I looked at joining via "relationships" using a Select Query but it doesn't quite show what I'm after...
Should add I would like Field "a" in both tables to show but only if the number exists in the "importtable" if not then don't show..
Any tips :confused:
View 1 Replies
View Related
Dec 5, 2012
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 3 Replies
View Related
May 18, 2005
Hello,
I have a few tables containing different exam data for students - and a unique identifier with each. I need to add another unique identifier (which is already specified from another system).
Now, can I use the existing identifier (UPN) to automatically put in the new identifier into a new field (AdNo).
If that makes any sense at all, I will be surprised...thank you!
View 1 Replies
View Related
Jun 26, 2013
I have a series of numbers in 30 columns. I want to identify where either of two numbers appear in the same record regardless of which of the 30 columns they are in.
OTHCPT2OTHCPT3OTHCPT4OTHCPT5OTHCPT6OTHCPT7
00300C1778C1820J0690 J2250 J2270
00300C1778C1820J0690 J2250 J2270
00300C1778C1820J0690 #NULL!#NULL!
2318095978C176788307J0131 J2270
For instance if 23180 and J2270 are in the same record I want to identify them for all of the records.
View 7 Replies
View Related
Mar 21, 2013
For my query I have 3 tables.
TEAMS. This contains TeamID / TeamName and TeamManagerName.
TeamAPPLinks. This is a many to many relationship and contains TeamAppID / AppID and TeamID (TeamID links to Teams.TeamID and AppID links to Applications/AppIS)
Applications. This contains AppID / AppName / Pass/Fail.
So teams can have many applications and applications can have many teams.
I only want to show teams that have all applications that have the Pass/Fail column as PASS. I have managed to do this for FAIL runing a query in a query but obviously its different for PASS as all associated applications have to be a PASS, not just one FAIL for a FAIL.
View 4 Replies
View Related
Aug 14, 2013
How to get the following results using 1 table:
Field1 ID is an auto record ID, field2 ID is actually field1 ID assigned that record, in other words record 1 has a roommate (record 5) assigned to it, record 2 has a roommate (record 4) assigned to it
Table A
Field1 ID Field2 ID
1 5
2 4
3 6
4 2
5 1
6 8
Results records I'm looking to display for would be:
1 5
2 4
3 6
6 8
I only want to display all fields for records, but I don't want to show their matching record, so I want to display record1, but not record 5 because record 1 has record 5 as a roommate, want to display record 2 but not record 4.
View 5 Replies
View Related
Oct 20, 2014
I have created a login form and create user page form, the tblListofUsers has two columns, UserName and Password, on the log in form when a user enters username and password, it should be matched with the tbllistofusers if true then msgbox"Welcome" else Msgbox "wrong username or password", I have written following code but gives error
Private Sub Command5_Click()
If DLookup("UserName", "tblListOfUsers", "UserName = " & Forms![loginpageForm]!User) And DLookup("Password", "tblListOfUsers", "Password = " & Forms![loginpageForm]!passworduser) Then
MsgBox "You welcome"
Else
MsgBox "Wrong username or password"
End If
End Sub
View 8 Replies
View Related
Dec 29, 2011
I'm trying to clean up a database and make sure that I don't make an error in future entrys.
Is it possible to make a "key" that highlights the rows with matching keys. The thing is that a the "key" is Switchname + Switchport, data placed in 2 different columns in the same row.
How do I do this? Is it possible?
View 10 Replies
View Related
May 4, 2007
I 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 Related
Oct 21, 2007
Hi,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 Related
Dec 18, 2004
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
View 2 Replies
View Related
Jun 4, 2014
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?
View 8 Replies
View Related
Aug 30, 2013
The table ProductInfo has an ItemID, and its corresponding Quantity.The table Orders has a PO column, an Item# column, a OrderQuantity column, and the column Item#CurrentStock which holds the Quantity of the selected item, for 3 items.I have created a form that has a ComboBox populated with the ItemID's from ProductInfo, that when selected populate the corresponding Item# in the Orders table.
I would love if the Quantity(stock) from ProductInfo would automatically fill the Item#CurrentStock field on the form when the ItemID is selected from the ComboBox.But all of the fields only relate to Item# from the first ComboBox!?!?I have tried =dlookup,
View 3 Replies
View Related
Mar 25, 2014
I have two databases (files). Each file has a format like the one below.
File 1:
chr start end
chr1 1000 2000
chr2 3000 4000
File 2:
chr start end
chr1 1500 3000
chr2 3750 4000
I want to be able to merge the two records together if the field chr matches between the two files plus if there is an overlap between the start number and end number from each file. For instance the first record from each file would match because the range from 1000 to 2000 of file 1 has numbers consisting of 500 numbers (1500-2000) that are also present and overlap in file 2 (1500-3000). I possibly cannot use < or > since the ranges from each file will vary to different degrees. Perhaps there is a between function that might work...
View 7 Replies
View Related
Dec 10, 2007
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:
View 4 Replies
View Related
Jun 5, 2013
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
Jan 20, 2014
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!
View 3 Replies
View Related
Jun 4, 2015
I'm trying to make it a little further with my new call answering database at work.
Basically it consists of a main form that has a button leading to a call answering form for each business that we represent. it also has a text box that displays the number of the caller when the telephone picks up, the correct call answering screen is also popped by the telephone software and a macro whenever the phone is answered.
I've got code in place that automatically puts the date and time in the correct field and also copy the incoming telephone number from the main form into the caller number field on the call answering form.
I'd like to take this automation a little further now and get the form to autopopulate the caller details if that caller has called before (we get a lot of calls from the same people) so i'd like to make the form search the table it's linked to for the incoming phone number and to fill in the name, email, company etc... for the caller according to the previous record.
The code for the 2 forms i've currently got setup (the switchboard and one call answering screen) are as follows
Switchboard:-
Code:
Option Compare Database
Dim WithEvents MaxxCom As Metro_MaxxCom_CTI_COM_API.CTI
Private Sub cmd_onnet_Click()
DoCmd.OpenForm FormName:="On_Net_Communications"
End Sub
[Code] .....
View 8 Replies
View Related
Aug 25, 2011
I have a subform in which I want to put in a Command Button to open another form.
I used the wizard as per normal, but when you come to matching specific data fields, there is nothing in my left hand column of the sub form. I have checked the record source property and it is bound to the correct table.
I have read that this is an issue with Access 2007? Is this the case or am I missing something obvious here?
View 3 Replies
View Related
Aug 12, 2005
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
View 2 Replies
View Related
Sep 1, 2014
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
View 5 Replies
View Related
Sep 10, 2014
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
View 3 Replies
View Related
Dec 10, 2014
Create form to search multiple fields in table
Return records that match search
Open the record that you want in Form View for editing
View 1 Replies
View Related
