Using Dlookup To Cross Reference Between Two Tables
Aug 13, 2012
I'm new to access and need to modify an existing database to get a report I want to create. I'm an Excel user and could accomplish what I want to do in seconds using Excel's Lookup functions, so I'm trying to do the same with Access Dlookup.
I have two tables in my database that tracks components in a hardware product. The first table is just a list of hardware units shipped. The second table is a list of various components (and versions thereof) used in each unit. The two tables are linked by a UNIT_ID field. I need to create a report showing each unit and what the various versions of the components are. My difficulty is that the component table is does not have a dedicated field for each component and each unit can be comprised of a different quantity of components. So the tables look something like this:
UNITS TABLE:
ID Customer
1 Joe
2 Bob
3 Bill
COMPONENTS TABLE:
ID Component Revision
1 Main Board A
1 Blue Board B
2 Main Board B
2 Blue Board B
3 Main Board A
3 Red Board C
I want to produce a report that looks like
ID Customer Main Board Blue Board Red Board
1 Joe A B --
2 Bob B B --
3 Bill A -- C
I setup queries to get revisions of each Board and am trying to compile into the full report, but can't figure it out. Right now I'm trying to use Dlookup to lookup data from one of the queries and insert into a larger table. I can lookup a single value fine, but can't figure out how to get Dlookup to give me the Blue Board & Red Board values for each of the system ID numbers in a master query (which would be a snap in Excel).
View Replies
ADVERTISEMENT
May 28, 2006
I have 3 fields in a form all have binding to the same table.
the problem i am facing is as follows
when i enter two fields in the form the third should calculate on its own.
Lets say the three fields are names field1, field2, and field3
scenario A
I enter field1 and field2. field3 should calculate on its own by adding field1 & field2
scenario B
I enter field2 and field3. field1 should calculate in its own by subtracting field2 from field3.
any time by changing any of the field1 or field3 the other one should change based on field2.
if field2 is changed then field3 should change.
i hope i have explained the problem in detail..
please help
thanks
aseem
View 4 Replies
View Related
May 16, 2013
I have a DLOOKUP which is used to auto populate a field on my Income Commitment Form based on a field on my Expenditure Commitment Form.It works perfectly when you enter a new commitment.Currently you enter a new Expenditure Commitment, save it but don't close it, then from the Expenditure Commitment Form there is is a button to enter a related Income Commitment. You save the Income commitment then close it, then close the expenditure form and you are on the Client screen with both these forms now showing as subforms, both have record selectors.
My issue is, that sometimes the Expenditure Subjective can change, so from the client form you use the record selector to open the Expenditure Commitment and select the correct Subjective, you then save it and close it. You should then click on the Income Record selector which opens the income commitment, but when you re-click on the Combo42 button, it doesn't reselect the Income subjective and I think it is because the Expenditure Commitment which is what it is looking up isn't open. My issue is how do I get the DLOOKUP to work and refer to the Commitment Form if it is a new commitment, or the Subform if I have gone back in through the record selector.Here is the code I have.
Private Sub Combo42_AfterUpdate()
If (Me.Combo42 = 1) Then
Me.IncomeSubjective = DLookup("[Subjective]", "tblIncomeSubjective", "[ExpSubjective]='" & Forms![frmCommitments]![cmbSubjective] & "'")
Me.OtherIncomeConfirmed.Enabled = False
Me.OtherConfirmedBy.Enabled = False
[code]....
View 1 Replies
View Related
Oct 15, 2015
I'm working on my first report and am trying to get my textbox, tboJob to show a field value and not the ID. I have tried the following Dlookup in my control source with no luck.
Code:
=DLookUp("[JobName]","Jobs","ID=" & [tboJob])
JobName is the field I want to show from the table Jobs.
View 7 Replies
View Related
Sep 5, 2014
I am trying to create a cross join or Cartesian product TABLE, not a query.
I am creating a training database. For each and employee and each training event, I want to know - is this event required, who approved it, and when was it completed. The table I envision looks like this:
Code:
EMPLOYEE_ID EVENT_ID REQUIRED APPROVER EVENT_DATE
1 1 Y WPD 9/5/14
1 2 N
I currently have 39 employees and 473 events - 39*473 = 18447 records
I was able to make a cross join query and use make table, but whenever I add a new employee or event, if I update the make table query I will lose all my existing data.
View 3 Replies
View Related
Apr 2, 2013
I have 4 queries in which data needs to be connected from the date and shown as a single date showing each sections entry in a row and a cumulative total is maintained as the balance .
See the attached image ...
View 7 Replies
View Related
Jul 4, 2013
I have broken up by master database, table, into several tables. They all share the same ID values since they came from the master table. So each table that I split off has a unique, matching, identifier, ID, in both the master table and the new table. Plus a previous matching identifier listed immediately below.
Example: Permit the master or main table and Builder a supporting table. There are five tables that I split off and all have an identifier similar to the two listed below.
Permit->ID
Permit->BLD
Builder->ID
Builder->BLD
All my tables have the same ID. But each table also has a identification numbers such as builder BLD, Subdivision SB and inspector INS>
Permit has the BLD, SB and INS
Builder has only one occurrence for each builder in the Permit table. As in the past it was identified by BLD number and now also ID. the use of BLD is from when I used visual dBase. The big difference here is that I have two ways to identify one is ID any other is by BLD, etc. My first thought is to identified by the BLD. I don't know why, but I guess I fear using a single "ID" to identify my various split off tables unique value or BLD.
View 1 Replies
View Related
Jul 30, 2014
I'm trying to do a lookup between two tables, whereas table A includes the product ID and table B includes the price. Is it possible to create a field in table A with a DLookup function on the product ID to get the product price from table B?
Whenever I try to type in the DLookup function, it does not calculate.
View 2 Replies
View Related
Dec 5, 2013
When entering a lookup reference to a table field it pop up message saying, "you can't modify the table 1 this time as it is in use by other person or process" i have closed all forms and table reports but the problem is same to rectify ? also when going to see the realationship then there shows no relation ship developed even after the lookup reference from one table filed to another table field.
View 1 Replies
View Related
Apr 10, 2013
I have two tables, Table 1 and Table 2 and two related forms, Frm1 and Frm2. I want to be able to update the data on Table1 from Frm2, that is, if I change the data on Frm2, the corresponding fields in Table1 will update to the same data. I know that if both tables are open I can reference the fields on Frm1 and Frm2 with an IF statement: If Active = True And Forms!Frm1a.DoNotchange = False Then..And I know on the update query I can reference a control on a form by Forms!Formname.Control. Wll works fine.What I would like to know is can I reference a table field in the same way. For example, I have tried to use the IF Statement If Active = True And Tables!Table1.DoNotchange = False Then..Is there a way to reference the Table instead of the Form in both the VBA and the query? The problem seems to be that I have the two bound controls [Active] on Frm2 and [DoNotChange] on Frm1. (Which I need).
View 5 Replies
View Related
Sep 30, 2014
use an account reference from one field in a table on a second table and add a number after it... e.g.
Table 1 Table 2
PEFA PEFA/001
PEFA PEFA/002
PEFA PEFA/003
PEFA PEFA/004
and so on...
View 2 Replies
View Related
Feb 3, 2015
Basically I have this formula in excel and i'm trying to recreate it in access but i can't quite figure it out.
****a***********b**********c*********d*********e** ******f
3**Car***Cost Center****start****combining*****end*****miles
4**001*****aaa********021****=(A4&" "&C4)***030
5**002*****bbb********030****=(A5&" "&C5)***035
6**002*****aaa********035****=(A6&" "&C6)***040
f4: =IF(COUNTIF($D4:$D600,D4)=0,(E4-C4),(E4-C4)/COUNTIF($D4:$D600,D4))
f5: =IF(COUNTIF($D5:$D601,D5)=0,(E5-C5),(E5-C5)/COUNTIF($D5:$D601,D5))
f6: =IF(COUNTIF($D5:$D601,D5)=0,(E5-C5),(E5-C5)/COUNTIF($D5:$D601,D5))
So basically: If a car is using multiple cost centers for a trip it divides the total mileage among the cost center for said trip provided that the user enters the same start and end mileage on all the cost centers used. And if it doesn't have multiple cost centers it provides the appropriate amount.
=IF(COUNTIF($D4:$D600,D4)
=IF(COUNTIF($D4:$D600,D5)
=IF(COUNTIF($D4:$D600,D6)
and so on
Any way to reference a column in access while referencing a specific point within that column.
View 2 Replies
View Related
Apr 24, 2015
I want the user to be able to see the source/reference of (many of) the different data points in my database, so I need to store sources/references in the database itself. I want to have all references in one single/common table (list of references). Each reference may be the source of many data points, in different columns in a given table, and in different tables. Is this possible, and can it be done in an elegant way?
View 2 Replies
View Related
Oct 6, 2014
How can i generate automatic reference numbers that can serve as primary key.
I want the prefix to include year. Eg Tr/yyy/incremental number...
View 1 Replies
View Related
Nov 27, 2013
In the attached Word document I have a screen shot showing the fields for the "Membership" table and "Class table." I am working exclusively with tables, no forms.
In the Membership table, I will enter a numeric value in the Class field. In the Tuition field, I want to use a DLOOKUP, which would look to the class column next to it, take that value as a lookup number, then go to the Class table (second screen shot) and locate that lookup number in the ClassID field, find the corresponding value in the Tuition column, then drop that result in the tuition field of the membership table.
Example:
In the Membership table, I enter 7 in the Class field. The tuition field should automatically return $160, because in the Class table, the classID 7 corresponds to a price of $160.
I tried setting up the Tuition field in the membership table as a calculated field, then I entered a DLOOKUP in the expression box and received an error.
Is it possible to do this type of Lookup?
View 4 Replies
View Related
Oct 19, 2007
I have never some across this before but my database keeps throwing a "Too many tables open error" which has taken me a day to get to the bottom of. I believe that the use of a dlookup in one of the queries not cleaning up after itself.
The issue I have now is that even importing the tables into a new database causes this problem as i guess I'm inheriting the same table id issue.
Does anyone know how to clear the table IDs that the dlookup left open ?
Secondly, could anyone suggest a more robust method of looking up one value from another table. Basically I need to look up the USD to GBP rate (one value) for every record in an other table. I cant use a join (trade ccy->fxrate ccy)as there is properties to link on. Ive tried the elookup function I found on the net but performance is still an issue.
Any help is appreciated
View 6 Replies
View Related
Apr 9, 2014
This is my code.
=DLookUp("[ItemDescription]","MasterData","[BarcodeID] = " & [Forms]![frmMasterData]![Barcode])
I have a from called "TakeOn". The from is based on a table, called "tblTakeOn" with fields ID, Barcode, ItemDescription and SerialNumber.
In the form "Take On" is is a combobox named barcode, that looks up the bacode values from a table called "MasterData".
The "MasterData" table has only 3 fields, ID, Barcode and ItemDescription.
I want to in the "TakeOn" Form to, in the ItemDescription Field, display the ItemDescription from the ItemDescription value in the 'MasterData" table.
Access displays a circular reference error message.
View 1 Replies
View Related
Apr 13, 2014
i already make this sql for dlookup table for may insert button, all going good, but when i'm going to make double command with different msg box for different criteria, it going fail
1. This my Working code:
If Me.txtidborang.Tag & "" = "" = (DLookup("NoGerankod", "HutangKeseluruhan", "NoGerankod='" & Me.txtnogeran & "'")) Then
MsgBox "Grant Number Invalid", vbOKOnly
Me.[cbostatuspembayaran] = "Geran Negatif"
CurrentDb.Execute "INSERT INTO Januari (bla..bla..bla..)
End if
2. This my not working code:
If Me.txtidborang.Tag & "" = "" = (DLookup("NoGerankod", "HutangKeseluruhan", "NoGerankod='" & Me.txtnogeran & "'")) Then
MsgBox "Grant Number Invalid", vbOKOnly
Me.[cbostatuspembayaran] = "Geran Negatif"
If Me.txtidborang.Tag & "" = "" = (DLookup("NoMatrikkod", "HutangKeseluruhan", "NoMatrikkod='" & Me.txtukmper & "'")) Then
MsgBox "This Student still have debt ", vbOKOnly
Me.[cbostatuspembayaran] = "Geran Aktif"
CurrentDb.Execute "INSERT INTO Januari (bla..bla..bla..)
End if
End if
Q: How can i combine two dlookup together for different msg box?
View 6 Replies
View Related
May 26, 2015
The image below describes the scope of the issue.
The function is called upon in the onLoad event of frmMain.
View 3 Replies
View Related
May 11, 2007
I am trying to make cross tab ..to merge the two value of the same column.A B AIRA B GROUNDA C SEAi am trying to make..A B AIR & GROUNDA C SEA..any idea?........let me know, i really need this query to solve the problem..thanks guys..
View 2 Replies
View Related
Mar 31, 2006
I want to obtain the value of a third variable using the first one as row heading and the second as column heading. I can't get this using a cross table query.
Is there another way of doing it???
Thank you
View 2 Replies
View Related
Jan 13, 2006
I've recently been having a few problems with getting a cross tab query to work. I'm currently using MS Access 2000.
I've created a query that I know want to use for the basis of my cross-tab.
I'm only after having it produce a tab of Part Type by Month using a count of all Clients.
When I use the wizard it sorts everything out fine, but when it runs I keep getting the same error message:
"The Microsfot Jet database engine does not recognize "[Start Date]" as a valid field name or expression"
In my main query I have it set so I can choose a date range using the criteria "Between [Start Date] and [End Date]" Is there a way of getting a cross tab to work using a user defined date range. I've also got fields that I have changed the column names on, EG. ClientName to Client Name (Client Name: ClientName) it is also having issues with these.
Any help would be very appreciated.
View 3 Replies
View Related
Apr 11, 2006
Dear all,
I have a small problem.....
I hope u 2 help me solving it.....
I cross tab query, with this column:
PIVOT Format([Date],"Short Date")
But as we all know that Format function returns variant(strin), So how can we reorder that column by date...
Sorry for bothering you....
Thanks & Regards
View 6 Replies
View Related
Jun 19, 2007
G'day,
I am looking to create a cross-tab query that sums multiple columns together.
A simplified version of the data is as follows
ID Date Column2 Column3 Value
123 2007/05/15 T 1 30.00
123 2007/05/15 T 2 50.00
123 2007/05/15 T 3 15.00
123 2007/05/15 T 4 10.00
123 2007/05/30 T 1 60.00
123 2007/05/30 T 2 25.00
123 2007/05/30 T 3 15.00
123 2007/05/30 T 4 16.00
146...
The desired output is as follows
ID column1(Sum of T1) column2(Sum of T2+T3) column3(Sum of T4)
123 90.00 105.00 26.00
146...
Where T# is a concatenation of column2 and column3.
Currently using the cross-tab queries I am able to get the following output:
ID T1 T2 T3 T4
123 90.00 65.00 40.00 26.00
146...
But how can I add T2 and T3 together?
Any help would be greatly appreciated keeping in mind that I am new to Access. Thanks!
View 4 Replies
View Related
Nov 5, 2007
I can't get the data to display as I want it. I haven't done a lot of Crosstab queries, so I need someone to clarify the field settings....
I have Pallet ID's which contain Multiple UNIT ID's
so this...
PALLET-1UNITID-1
PALLET-1UNITID-2
PALLET-1UNITID-3
PALLET-1UNITID-4
PALLET-1UNITID-5
PALLET-1UNITID-6
PALLET-1UNITID-7
PALLET-1UNITID-8
PALLET-1UNITID-9
PALLET-1UNITID-10
should look like....
PALLET
PALLET-1UNITID-1UNITID-2UNITID-3UNITID-4UNITID-5UNITID-6UNITID-7UNITID-8UNITID-9UNITID-10
the unit ID's must be on the same row as the pallet record, it can be a string in one cell
View 4 Replies
View Related
Dec 22, 2005
Hi,
I have a small problem with a cross tab query. The query works perfectly when i run it and displays the data in the way i would like it to, i want it to be on a form though and when i try and do this i get problems.
I get either a message saying i have possibly chosen a query with no output fields or a microsoft doesnt understand the name of my query.
I have looked at 'cross tab querys' and 'cross tab query forms' in the search facility and have tried some of the suggestions which people have given to similar problems to mine. For example basing another query off my cross tab query and using that for the form, but it just does brings the same problems.
I would appreciate any help, thank you.
View 1 Replies
View Related