Help with multi table lookups report needed

[gjt211]

Hi,


I am new here and don't really know how to achieve what I want to do. I will give some background on what I have and what I would like and hope someone here can provide me some guidance.
My Access experience is very limited, so please don't shoot me down if I ask the wrong questions, use the wrong terms, or seem stupid. Thanks in advance.

I have an Access 2007 database that has about 10 tables.
There are two main tables (I only use the term 'main' as they are the tables that store all the site information);
The first one contains data for site locations and several lookup fields to populate the 'site data table' information (such as location, site class etc).
The second main table contains data for companies and several lookup fields to populate the 'companies' information (such as location, phone number, etc).
The second table (companies) also has a multi-select lookup to the first main table (site data table) so I can mark what companies are related to the site data.

I have created a report for the 'site data table' that works well, however I would like to add to each report record all the 'companies' that have been assigned/related to that specific site.
I don't even know how to ask a question in google on how to do this. I am not sure where to start.

Could anyone please provide me some help to get started?

Kind regards

[ssanfu]

Hi and welcome to the forum........

Lookup FIELDS are generally considered Bad. From my reading, most (but not all) experienced programmers avoid the use of lookup FIELDS.

Read these:
The Ten Commandments of Access
http://access.mvps.org/access/tencommandments.htm
Expecially see #2 & #3


The Evils of Lookup Fields in Tables
http://access.mvps.org/access/lookupfields.htm


I NEVER use Lookup FIELDS nor Multi-Value Fields. Search the forum and you will find (most) experienced programmers do not use Multi-Value Fields.


Having said that, here are two links about using Multi-Value Fields:
http://www.utteraccess.com/forum/Mul...-t1984616.html

https://support.office.microsoft.com...rs=en-US&ad=US
(I know it is long, but it is a MS link)


I googled:
"ms access 2010 multi value fields"


This might help; Allen Browne provides code to concatenate values from related records:
http://allenbrowne.com/func-concat.html

Good luck with your project

[gjt211]

Hi Steve, and thanks for your reply. It is greatly appreciated.
I have read a little before about people saying not to use lookup fields, but that leaves me a little lost.

I was always told not to store the same data twice. It is not good practice. So I create a table, lets say city names, and populate that with text data containing the cities I want in my list. How do I go about seeing those in my main table that only stores the ID? I may have more than one table the needs to use the city names.

Sorry if I seem not too bright, but isn't the whole idea to create relational databases? Isn't the lookup field type just creating a relationship?

Once again, thanks for your kind words of wisdom.

[June7]

The advice is to not use lookups in table - build combo and list boxes on form.

Build queries that join tables so related info is available.

Your report RecordSource can be a query that joins those tables or can be report/subreport arrangement.