Matching Values From 2 Tables
May 4, 2007
I have three tables: tblProducts1, tblProducts2 and tblProductSales.
tblProducts1
Code Cost
ABC 20
BVC 35
ABC 30
tblProducts2
Code Cost
ABC 10
BVC 55
ABC 20
tblProductSales
Code Rev
ABC 70
BVC 25
ABC 20
BVC 15
DCC 33
I want to produce a query that looks like this:
Code Rev Cost Profit
ABC 90 80 10
BVC 40 90 -50
DCC 33 0 33
How can I do this?
Thanks,
Jon
View Replies
ADVERTISEMENT
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
Nov 2, 2007
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.
View 4 Replies
View Related
Feb 1, 2007
Dear all:
I can't seem to figure this one out on my own; I'm trying to create a query that counts the number of values in fields across a row that match certain criteria. For example, the data I'm querying looks like:
ID 01-1 01-2 02-1 02-2 03-1 03-2 04-1 04-2
1 85 76 68 72 67 63 74 73
2 32 34 70 65 67 80 68 70
and I need to find out, for example, how many numbers for each id are greater than or equal to 70 but less than 80. I've tried using DCount, but it gives me the number overall that match the criteria instead of the number per row (so I'm sure I'm doing it wrong). I've tried using a crosstab query, but I can't suss out how they actually work when you've got more than one column of data that you need to work with unless I put ID as column headers and the other fields as row headers... that would make my query about 1500 columns wide and 10 rows long, though, which just seems wrong... ?
In a slightly different approach, I've done another query on the data that makes it look like:
ID Number First Second
1 01 85 76
1 02 68 72
1 03 67 63
1 04 74 73
2 01 32 34
2 02 70 65
2 03 67 80
2 04 68 70
But again, I keep ending up with silly answers (like the count for the entire set of data, rather than just per ID). I'm *sure* I'm just not quite getting how DCount works... and it's possible I'm trying to use it for something for which it wasn't designed. Any alternative examples would be most helpful.
I'm not sure how to compress the data any further. I could, technically, get it down to two columns (ID and <number to query>), but the only way I can see of doing it is to create a temporary table for the first column and append the second column's data to it, which I'd have to do every time any of the numbers changed.
I would prefer to use the first approach, as that query is useful in other calculations and the second is a bit of a faff (though not too bad, just a union query). Anyway, I'm just stuck and instead of continually banging my head against the wall, I thought I'd ask you kind folk for help. :)
-Jen
View 7 Replies
View Related
Oct 23, 2013
I'm looking for a way to grab values from one table that match a criteria, and append them/update them to another, but only grab one value once. Example:
Part ID ---- Profile Profile ID ---- Locations
Part1 - Profile 1 Profile 1 ---- Location 1
Part2 - Profile 1 Profile 1 ---- Location 2
Part3 - Profile 1 Profile 1 ---- Location 3
I have a database of parts that fit in certain profile sizes. Each profile size has multiple open locations. I need a query to grab a location, assign it to a part, grab a new location, assign it to the next part, etc, without assigning duplicate locations to different parts.
Right now, I'm doing this by using the First/Last aggregate functions, marking them as used, and looping it again and again until all have unique locations. Is there a better way to do this?
View 8 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 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
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
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 2 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
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
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
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
Oct 18, 2013
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.
View 1 Replies
View Related
May 22, 2014
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.
View 1 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
Sep 16, 2013
I have with my database. It's holds cost data including purchase made in foreign currencies which need to be converted to GBP using the correct exchange rate so a variety of reporting & stats can be performed.I have a table called Costs within which there are 2 fields
Purchase Currency
Exchange Rate
I also have another table called Exchange Rates 13/14 within which there are 2 fields
Currency
Exchange Rate
When a value is entered in the Purchase Currency field on the Costs table (this is a look up field linked to Exchange Rates 13/14 so it shows the listed currency in drop down) I need the database to automatically populate the Exchange Rate column in Costs i.e. match the value in the Purchase Currency field to the Currency field in Exchange Rate 13/14 and populate with corresponding Exchange Rate from Exchange Rate 13/14.I have tried the following and none work:
SQL Tried
1.
UPDATE Costs
SET ExchangeRate = [Exchange Rates 13/14].[Exchange Rate]
WHERE Costs.[Purchase Currency] LIKE [Exchange Rates 13/14].Currency
2.
UPDATE
Costs
SET
ExchangeRate = [Exchange Rate]
FROM
[Exchange Rates 13/14]
INNER JOIN
[Exchange Rates 13/14]
ON
Costs.[Purchase Currency] = [Exchange Rates 13/14].Currency
3.
SELECT [Exchange Rate]
FROM [Exchange Rates 13/14]
WHERE Costs.[Purchase Currency] LIKE [Exchange Rates 13/14].Currency
I know it is possible to have a drop down for Purchase Currency which shows 2 columns (both Currency and Exchange Rate) you can then use the exchange rate figure for a calculated field. The problem I have is that I am importing data into the costs table from excel. In Excel I can only have 1 value in the Purchase Currency column on the upload template. If I just have Euro in this column the database does not match it to the Euro in the Purchase Currency drop down and also store the correct exchange rate.
Or is the alternative to put this into the calculation of GBP Unit Cost where this somehow matches the Purchase Currency in the Costs table to the Currency field in Exchange Rates 13/14 tables and uses the appropriate exchange rate from Exchange Rates 13/14 to calculate GBP Unit Cost in Costs table.
View 4 Replies
View Related
Feb 2, 2015
I have two queries:
1. a sum of payments by individuals
2. a sum of charges of individuals.
I want to combine these queries to create a report that shows the sums for each person of the charges and payments and calculate the balance. However, it is only showing me those individuals that have both a charge sum and a payment sum, while some individuals will only have a charge sum. How do I get those individuals to show and show with a zero payment balance if they are in my charge sum query but not my payment sum query and vice versa (some may have made a prepayment even though they have not been charged yet).
View 5 Replies
View Related
Aug 29, 2014
I'm using Access 2010. I need to calculate a score based on values selected in a table by looking up corresponding values in other tables. I have a "Project" form to create new entries into the Project table (see Table 1). When I create a new project record, I will select values for the Payback and Need fields by selecting options from a list. The Payback list is pointed at Table 2 and the Need list is pointed at Table 3. In the below example, I created the "ABC" project and selected "1 year" for the Payback field and "Repair" for the Need field. Pretty simple.
Now that I have the "ABC" project loaded to my Project table, I'd like to create a report that will show a "score" for this project. The score should be calculated as follows: Payback Impact + Need Impact. In this example, the score should be 30 (Payback Impact of 20 + Need Impact of 10).
View 5 Replies
View Related
Feb 7, 2014
i have one table in which ID is Primary ID with Different Values
Like
ID NAME PAN
1 A X
1 B Y
1 A X
2 C Z
2 C G
3 D U
it shows that ID 1 having 2 Name (A& B,with PAN, X & Y ,respectively).how can i get this that ID having More than 1 Value like 1 and how can i select only these records ID which having more than 1 value and how can i update values for 1 ID.
View 8 Replies
View Related
Dec 10, 2014
Currently, we have a table which contains a field for each type of training that can be taken (FUT1, FUT2, ...). Once an Instructor has taken one seminar, the table is populated with the date taken, via their employee number (the key) through an update form.
Now, the company wants the paper training reports to turn into a digital part of this database. As envisioned, the form and associated table will have combo boxes with pre-determined training types (FUT1, FUT2, ...). The date would be typed in.
The problem I see is getting the dates and training type from the future table to populate the existing table in the correct field for the correct Instructor.
In addition, there exists a report which prints out the training dates for each Instructor at each school. This is will still be in use.
View 7 Replies
View Related
Sep 26, 2006
Hi all....
I have been beating my head for 2 days on this. Any help will be GREAT!
I have 2 tables: CLIENTS and RELATION
NAMES has the client_ID, first_name and last_name along with other information for clients. RELATION is how the clients fit with each other. RELATION has relation_ID, Relations_description and up to three fields where we can enter the ID of clients from the client table called Rel1_ID, Rel2_ID, and Red3_ID. Now all the three rel_IDs come from CLIENTS and stores only the client_ID. There are multiple relationships.
How can I display the names and not the IDs for the clients?
I am able to display only one client name. I need this for form as well as report. I can do dlookup for form but don't know for reports. I am thinking that if I have it in the query, then I can use that for the form as well as reports.
I am able to show the the name for one client by running the following qry. But I am not able to modify that to show the other 2 names.
SELECT RELATION.Team_Name, RELATION.relations_Description, RELATION.rel1_ID, RELATION.rel2_ID, RELATION.rel3_ID], [client.f_name] & " " & [Client.l_name] AS Client_Name
FROM RELATION, CLIENTS
WHERE ((([relation.rel1_id])=[client.client_id]));
View 3 Replies
View Related
