Introducing myself

[lioninwinter]

Good morning everyone and happy lock-down. I have just joined the forum and am excited to start meeting my fellow Access users. I am a historical researcher and am developing an Access database to help with the analysis of huge amounts of data I use to address demographic and social historic questions. I am a proficient Excel user but my database in Excel has all but collapsed under the weight of the data requirements. So I've moved to Access where my skills are quite basic. I found a post this morning from someone using Vlookup in Excel and it is the sort of problem I am also encountering. My question this morning would be how do I lookup a field in one table with a field from another table or query - the option given with the lookup wizard is a combo or list box with multiple values. I would like to lookup a single value with a "where" clause linked to the primary key of the table in which I want the data to appear. Any thoughts much appreciated. I suspect I have to be more specific so can upload simple file if the question is not clear! Many thanks to anyone who can help

 

[isladogs]

Hello and welcome to AWF.
There is a DLookup function to look up a single value from a table or query
However you would normally create a query joining the two tables with a field that is in both tables e.g. ID fields and use that to obtain the value required.

 

[lioninwinter]

Thank you isladogs - would the join need to be specified in the query? And how does one avoid the need for for a combo/listbox - this might seem elementary but oddly its not mentioned in any of the books or videos I've used

 

[isladogs]

Yes. The join has to be specified in the query. If you only need one value, use a textbox.
If you use a query for your form recordsource, the textbox can just be bound to the required field.

In case it helps, see my website article Query Join Types

 

[Dreamweaver]

Hi welcome aboard

 

[Uncle Gizmo]

Hi and welcome to the forum.

You may or may not be aware that moving from Excel to Access has some hidden problems which can build up and cause you great difficulty at later stages in your development.

I discuss them in my blog here in text and YouTubes...

Excel in Access - Nifty Access

Excel in Access - Problem & Solution! One of the most frequent problems I see is people confusing MS Access with Microsoft Excel. This leads to fundamental design errors. Now, the problem is, this error "A Major Error" does not become apparent straight away. Unfortunately your database will work...

www.niftyaccess.com

 

[lioninwinter]

I have put a small sample together to illustrate. At the moment I am duplicating fields but in the future I will want to develop tables which take values from different sources and also calculate fields - for example the "count" of the marriage number. I realize I could do this with a query but the fields are so often used in my Excel database that they really belong in tables. In the example I have given I want to populate the field Actual_Year in the table baptism with the corresponding value C_Actual_Year in the 'master' table BapX using the CB_P_ID as the linking ID field. Rather than having to populate the field record by record using a combo box, I would like the value to be looked up directly. I have put some code in the Row Source property (SELECT [BapX].[C_Actual_Year] FROM BapX WHERE [Baptisms].[CB_P_ID] = [BapX].[CB_P_ID]; ) but it does not work.

 

[lioninwinter]

Hi and welcome to the forum.

You may or may not be aware that moving from Excel to Access has some hidden problems which can build up and cause you great difficulty at later stages in your development.

I discuss them in my blog here in text and YouTubes...

Excel in Access - Nifty Access

Excel in Access - Problem & Solution! One of the most frequent problems I see is people confusing MS Access with Microsoft Excel. This leads to fundamental design errors. Now, the problem is, this error "A Major Error" does not become apparent straight away. Unfortunately your database will work...

www.niftyaccess.com

Thank you! I will consult in detail.

 

[Uncle Gizmo]

Thank you! I will consult in detail.

You can download any of my sample files for free using this coupon code:- GetALL4Fr33_OrBuyMeA_Coffee

 

[theDBguy]

Hi. Welcome to AWF!

 

[pbaldy]

Welcome aboard. I moved the thread out of the introduction forum since it has technical content.