Question How to 'LOOKUP' multiple fields from the same table (auto-populate)

[angelagh]

Greetings:

I'm building a database and am trying to figure out this...

I have a table with multiple fields that I want to populate into a form,table, etc... just by selecting one item from the source table (lookup or row source).

For example, I want the location, hotel cost, and meal cost to populate in a new location (table, form, etc) just by selecting the location.

HELP!!! :banghead:

 

[plog]

3 approaches:

Don't. You listed a table as a place you want this to occur--you would not do this in a table. Data shouldn't be moved to tables, but linked. If you want to associate data in one record of a table to another record in a different table, you use foreign keys (https://en.wikipedia.org/wiki/Foreign_key)

DLookups. You could make each text input based on a Dlookup (https://www.techonthenet.com/access/functions/domain/dlookup.php). You would need one of these for each piece of data you want to show.

Sub-form/Sub-Report. You could also make a subform/report based on the table you want the data from, then add that subform/report to the main form and link via child and parent fields (https://support.office.com/en-us/ar...any-form-ddf3822f-8aba-49cb-831a-1e74d6f5f06b).

 

[pbaldy]

Another option:

http://www.baldyweb.com/Autofill.htm

 

[angelagh]

Greetings:

thanks for your information -- I'm obviously not describing what I want to do correctly....

OR.... I'm not understanding how to do what you've explained....HELP!!

Let's say I have Table "Fees" and I have a form (or report) called "Total" where I want to be able to make ONE selection in ONE field in "Total" FROM "Fees" and this one selection would autofill SEVERAL fields in "Total" FROM "Fees".

Another way to explain:
"Fees" table has fields city, hotel, meals, incidentals

"Total" report/form has many fields and three of them are city, hotel, meals, incidentals (FROM "Fees") -- but I want to ONLY select the city and have all of these fields autofill from Fees.

**ALSO, I need to store the info in the report/form...the "Fees" is a source table of options...

This has got to be possible.

Thanks,
Angela

 

[plog]

You've lost me through all your tables, forms, reports talk. I have no idea where we are at. Ultimately you want your data to go to tables, correct? Let's just talk about how those are effected and stop the confusion.

With that said, can you post the structure of your tables in question? I'm pretty sure you haven't set them up correctly and want to verify this. A screenshot of your relationships view would work, or simply use this format:

TableNameHere
FieldName1, field type, field description, example data
FieldName2, field type, field description, example data
FieldName3, field type, field description, example data
FieldName4, field type, field description, example data
...

So, a customer table would look like so:

Customers
CustID, autonumber, primary key, 1, 2, 3, etc.
CustFirstName, Text, first name of customer, John, Jack, Jason
CustBirthdate, date, date of customers birth, 2/3/1990
etc.

 

[angelagh]

Here is the table I'm starting with (which I use a macro to import from a spreadsheet that I receive from a different website)

CONUS_Per_Diem
ID, autonum, primary key, 39084, 39085, 39993
State, Short Text, State Name, ALABAMA, ALABAMA, ARIZONA
Location, Short Text, City within State, ABBEVILLE, ABERFOIL, FOREPAUGH
County, Short Text, County City is located in within State, HENRY COUNTY, BULLOCK COUNTY, MARICOPA COUNTY
Season Begin, Rate Start, Short Text 01/01, 01/01, 04/01
Season End, Rate End, Short Text 12/31, 12/31, 05/31
Lodging, Short Text, Hotel Per Diem, 91, 91, 130
Local Meal Rate, Short Text, Local Meal Per Diem, 46, 46, 54
Proportional meal Rate, Short Text, Local Proportional meal Rate, 30, 30, 34
Local Incidental, Short Text, Local Incidental Rate, 5, 5, 5
Maximum_Per_Diem, Short Text, Maximum Per Diem for location, 142, 142, 189
Effective Date, Short Text, Effective date of these per diem rates, 10/01/2016, 10/01/2016, 10/01/2016

-------------
My Goal:
create a form where a user will be drafting trips -- I want the user to be able to select a location field and VIEW State, County, Season Begin, Season End FROM CONUS_Per_Diem.

Upon selecting the option they want -- the following form fields will populate State, Location, Lodging, Local Meal Rate, Incidental, Maximum Per Diem.

Thanks much!

 

[plog]

I would use a 2 form system for this. The first form would have a drop down based on the ID and Location fields of your Conus_Per_Diem table. Beneath that would be a button labeled 'View'. The user would select the location they want and click the button.

Then, you could use a DoCmd.OpenForm to open a form based on the Conus_Per_Diem table. DoCmd.OpenForm (https://msdn.microsoft.com/en-us/library/office/ff820845.aspx) allows you to pass criteria so that you can open that form to just a specific record. You would use the ID field of the drop down in that criteria to open the form to just the record you want.

 

[angelagh]

Hi Plog --

thanks for the help -- but I don't want to view a certain record from CONUS_Per_Diem, I want to pass those values to a different table.

I don't understand how the DoCmd.OpenForm performs the data pass.

 

[plog]

I want to pass those values to a different table.

That's not how databases are to work. You don't move data around. You set up relationships and use foreign keys to link records in one table to records in another.

You have an ID field. What you do is whatever table you want the data from a record in CONUS_Per_Diem to go, you simply put the ID field value, not the value of every field. Then that external table's record is linked to that record in CONUS_Per_Diem.

 

[angelagh]

HI Plog...

perhaps I'm using "pass" too liberally...piled with potential other confusion -- here is my train of thought to this point

My goal:
take the attached workbook and turn it into a Access DB...where trips tab is the FINAL product...

My thoughts so far:
1. separate table for location because I can download this information (location, daily max lodging, daily max per diem, total per diem) from an external site and upload as "CONUS_Per_Diem"

2. separate table for "Fees" (see trips tab, fee; and agrmt tab, fee%)

3. I currently have thought I need a separate table for travelers (government and contractor) and a separate table for event details (fields would be event description, number of days)

4. I have also created a separate table for CONUS_Airfare because I can download a spreadsheet with fares that I have then used a query to get an average airfare that would populate the Trips.Airfare

5. I have another table that represents the Rules tab in the workbook -- manually entered data that would be applicable to all trips regardless of location.

zip'd Access DB is too big to attach -- I can email if interested...

 

[plog]

So the ultimate goal is a report then, not a form to manipulate data. Achieving such a report in Access is possible. But you are going about it wrong with your method that involves copying data to a form.

You need to focus on setting up your tables correctly, building the necessary queries to generate the data to feed the report and then setting up the report to generate the ultimate data you want.

Set up your tables, then set up the relationship tool and take a screen shot of it and post it here.

 

[angelagh]

Hi Plog --
appreciate the help but your last comment has me really confused now...I still don't know how to have data from one table populate multiple fields in something else (regardless if it's to a table, form, report...etc)...based on one select.

the relationship thing has forever confused me...

I've created a few queries to do the calculations but if I'm not pulling the fields from the right tables...that doesn't help

 

[plog]

I still don't know how to have data from one table populate multiple fields in something else (regardless if it's to a table, form, report...etc)...based on one select.

Correct, nor should you. The method you want to employ is incorrect. You don't populate data like that. You establish relationships in your data:


Owners
Owner_ID, Owner_FirstNam, Owner_LastName, Owner_Addr
1, John, Smith, 1234 Main Street
2, Sally, Jones, 714 Aaron Avenue

Pets
Pet_ID, Owner_ID, Pet_Name, Pet_Type
1, 1, Larry, Hamster
2, 1, Curly, Hamster
3, 2, Spot, Dog
4, 2, Gizmo, Cat
5, 1, Moe, Hamster

See the [Owner_ID] field in the Pets table? With that I can tell you all the information about the owner of the pet. I don't copy the full record of the owner into the Pets table, I simply store the Owner_ID in the Pets table and now I have access to all the data about the owner. When I want to bring all that data together I create a query. When I want a listing of all that data, I make a report based on that query and I can display everything I know about a pet. All the while the data isn't stored together, its stored in the appropriate data and brought together via the query.

That is how your tables and database should work. You shouldn't move data around, you use "keys" (those ID numbers) in external tables to link the data.

So set up your tables, then the relationships and then post a screenshot of it here.

 

[angelagh]

Hi Plog --

ok -- I simplified what I'm doing into the table (Trip Details-Copy of)...the fields do not represent all of the data I need to eventually "display" but I hope it lays the foundation for multiple trips...

where the user will eventually only enter the information for the table 'Event Details' and Travelers then would select information based on preloaded information in the CONUS_Per_Diem, Rules table and various queries that are performing calculations.

THANK YOU SO MUCH for helping me!

 

[plog]

Those tables just don't seem right. I question why you put certain things in certain tables.

I would think all the Travelers data should go into Trip Details. Why is it in its own table?

How records do you have in the Rules table?


Can you upload some sample data from those tables?

 

[angelagh]

Hi Plog --

I don't have any sample data in the DB yet -- it would be the same information that was in the spreadsheet...I can't figure out the data flow to understand where to enter the data and how to display it all (because the Trip Details table isn't all the information that needs to be displayed).

I'm trying to automate a majority of the data entry that was in the spreadsheet I sent previously.

My thinking on the traveler table is that is the "unique" information for every trip (along with the table Event Details -- this will vary greatly and can't be pre-established; whereas, the information in all of the other tables is information that will be entered one time (manual or via macro) and will be used for all the trips.

 

[angelagh]

Hi Plog --

I believe I figured it out -- attached a new relationship diagram for your comment...

 

[plog]

I think that looks better relationship wise. Still would need some sample data in all the tables to verify though, but it makes more sense from a high level perspective.

However, before you go further, I'm going to nitpick your table/field names:

1. You should only use alphanumeric characters in names. It just makes coding harder down the line (queries and VBA code). That means remove all special characters (#, %, /, etc.) as well as spaces. So instead of [Duration (days)] make it [DurationDays], instead of [Event Details] make it [EventDetails].

2. Prefix your [ID] fields. You have 4 fields in that document in 4 different tables named [ID]. I would do what you did with [TripID]--prefix it with a descriptor. So, rename [Fee Table].[ID] to [Fees].[FeeID] and do the same to the other 3.

3. Date is a reserved word and shouldn't be a field name. Instead, prefix it for what its for (e.g. EventDate). Also, I would make the datatype of that field and actual Date/Time.

But again, from a table/relationship standpoint it looks good. Sample data would help me verify that though.