retrieve data from related table

[paul p]

Hi there,
I'm here to ask another report related question.
I created a report which populates its controls with the data shown in a form. Everything works fine except for the data I got via a related table.
The data compiled in the form, are saved in a table, in which a column values are to be chosen from another table's list of values.
If I refer to the form control called Client, in which the value is say "XXX Spa", the report returns me 1, since the value XXX Spa is the first in the list of the related table's values.
How can I refer to the actual value, instead of the listing order of the value itself? I tried via SELECT function but I just can't find the right expression to achieve the results.
Here some data:
Fatture.Client is the table and the column/control I display, Clients.CompanyName is the table and the column in which are listed the values I take to display in my Form/Report. The relationship is obviously One-To-Many.

Thanks in advance for your advices.

Paul


Submitted via EggHeadCafe - Software Developer Portal of Choice
Dr. Dotnetsky's Cool .Net Tips and Tricks No. 26
http://www.eggheadcafe.com/tutorial...be30-cde5d852348d/dr-dotnetskys-cool-net.aspx

 

[Marshall Barton]

I'm here to ask another report related question.
I created a report which populates its controls with the data shown in a form. Everything works fine except for the data I got via a related table.
The data compiled in the form, are saved in a table, in which a column values are to be chosen from another table's list of values.
If I refer to the form control called Client, in which the value is say "XXX Spa", the report returns me 1, since the value XXX Spa is the first in the list of the related table's values.
How can I refer to the actual value, instead of the listing order of the value itself? I tried via SELECT function but I just can't find the right expression to achieve the results.
Here some data:
Fatture.Client is the table and the column/control I display, Clients.CompanyName is the table and the column in which are listed the values I take to display in my Form/Report. The relationship is obviously One-To-Many.

Before you worry about the report, you should create a query
that retrieves the data you want to see in the report.

In this case, it sounds like the Client field in the Fatture
table is a lookup field. This makes it look like the field
contains the CompanyName value when it actually contains the
client ID value. Note that when you look at a table you are
seeing the result of a query that Access created on the fly.
Because of the looki[ field, this internally generated query
will follow the trail to the Clients table to get the name
and fool you into thinking that the company name is in the
Fatture table when it is not really there. To avoid all
this kind of confusion now and in the future, DO NOT USE
LOOKUP FIELDS in any table. Change them all to simple text
box fields

If you look closely at your data entry form for the Fatture
table, you will see that the combo box for the client uses a
row source query to the Clients table and the ColumnWidths
is set to hide the ID and display the CompanyName. The
combo box is where the lookup actually occurs on the form.

The query for your report needs to include both tables,
linked on the client ID field, so you can add the company
name to the query's field list along with the fields in the
Fatture table.

Once you change the Client field in the Fatture table to use
a text box instead of lookup and get the query to display
the company name, the report problem should go away.

 

[fredpox]

Hi Marsh, and thank you for your support
Actually you hit a nerve of mine: queries.
I'm a bit uncomfortable with queries, and it's a topic on which I have to study more and more.
Anyway, although I do understand the first part of your post, and I realize that lookup are not good for forms, I have some troubles in getting the gist of your penultimate paragraph and which kind of query am I supposed to create and how can I select (in the form) a value between a list with a mere text box
By creating a query which lists me all the values in the Clients table, and selecting the CompanyName field in the row source of my form's control I get the name of the Company correctly displayed in my report, as you said
..Unfortunately I'm not sure if this is what you told me to do :
Actually, as I read your post I firstly tried to create a Union Query which compared and united the Clients.CompanyName of the Clients table with the Fatture.Client value of the Fatture table.
As a second thought I wondered if a mere selection query could have done the trick as well, and it actually did it.
Maybe here we are getting slightly off topic, am I supposed to leave this post/thread and start a new one in the appropriate group

regards
Paul

 

[Marshall Barton]

I'm a bit uncomfortable with queries, and it's a topic on which I have to study more and more..
Anyway, although I do understand the first part of your post, and I realize that lookup are not good for forms, I have some troubles in getting the gist of your penultimate paragraph and which kind of query am I supposed to create and how can I select (in the form) a value between a list with a mere text box.

Don't confuse a "lookup" table or a combo box (on a form
with row source to a "lookup" table) with the evil lookup
FIELD in a table. The latter is the one that is causing the
difficulty, but the others are common useful tools. A
"lookup" table is just a table, often with just a few
fields, that is used to house values that are in a
relationship with other tables.

Viewing a table in sheet view is something that users should
never be allowed to do, so the only purpose of viewing a
table is for you, the programmer, to use when debugging a
query or some code.

By creating a query which lists me all the values in the Clients table,
and selecting the CompanyName field in the row source of my
form's control I get the name of the Company correctly displayed
in my report, as you said.
.Unfortunately I'm not sure if this is what you told me to do :S
Actually, as I read your post I firstly tried to create a Union Query
which compared and united the Clients.CompanyName of the
Clients table with the Fatture.Client value of the Fatture table.

No, no, no. A union query is generally used to make one
(virtual) table from records in several (nearly) identical
tables or from a bunch of fields in a single unnormalized
table

As a second thought I wondered if a mere selection query could
have done the trick as well, and it actually did it.

Yes. A query with multiple tables JOINs the records from
one table to the records in another table. That is called
linking the tables using the primary key in one table (e.g.
CompanyID in Clients table) and a foreign key field in
another table ClientID in the Fatture table. For example:

SELECT Fatture.*, Clients.CompanyName
FROM Fatture INNER JOIN Clients
ON Fatture.ClientID = Clients.CompanyID

Maybe here we are getting slightly off topic, am I supposed to
leave this post/thread and start a new one in the appropriate group?

Normally, I might say yes, but these follow up questions do
not appear to diverge from the original situation, so I
think it's ok.

 

[fredpox]

Hi Marsh

err.. till the last post I told you field name translated for your understanding my problem but maybe it's easier for me to tell you the actual fields I put..sorr

I tried the following SQL statement
SELECT TBL_RegistroFatture.*, TBL_CLIENTI.
FROM TBL_CLIENTI INNER JOIN TBL_RegistroFatture ON TBL_CLIENTI.ID = TBL_RegistroFatture.ID

With this query in the datasheet view I see a table with all the fields of my TBL_RegistroFatture, and all the fields from my TBL_CLIENTI, with just 4 rows
I notice only now that ID 3 in the TBL_RegistroFatture disappeared, is there a chance to get it back in the list? Probably I deleted the record, but is it possible to use ID 3 again no? How can I restore it

If I may, I took some snapshots of my table, and you can find them at this lin
http://img341.yfrog.com/img341/3421/tuttetabelle.jp
In there you can find all of my fields, along with data types

The report now works fine, the row source on the form point to the query, and the value displayed on my form reads the text value, not the indexed one, as I wanted it to
Now I need to figure out how to get the right field value for the address (fields VIA, CAP, CITTÀ in TBL_CLIENTI) according to the selected RagioneSociale value displayed in the NomeDitta control of my report..another SELECT query on the fly
I thought something like this, no syntax problems but it doesn't work either :
SELECT [QueryJOIN]![VIA] WHERE ([QueryJOIN]!RagioneSociale]= [NomeDitta] )

Just one more thought
By reading your example, I thought why would you JOIN the Clients ON Fatture.ClientID=Clients.CompanyID instead of Fatture.ClientID=Clients.CompanyName, but then I realized that it's because of the different kind of data, right
You can't JOIN a text field with a number field
That's why when I have a (empty) table which lookup values from another table, the data type for the field varies to number, right

Sorry if I keep on asking you obvious concept, but I prefer to get it right from the start

Thanks for your patienc

Paul

 

[fredpox]

Hi Marsh
I have an update

I tried several times with different combinations to achieve the results with the query and I came up with these 2 queries, which provides the same 'table' data: one is using INNER JOIN, and the other WHERE
I guess in this very case is pretty much the same thing using one of them

SELECT TBL_RegistroFatture.*, TBL_CLIENTI.
WHERE (((TBL_RegistroFatture.CLIENTE)=[TBL_CLIENTI].[RagioneSociale]))

SELECT TBL_RegistroFatture.*, TBL_CLIENTI.RagioneSociale, TBL_RegistroFatture.CLIENT
FROM TBL_RegistroFatture INNER JOIN TBL_CLIENTI ON TBL_RegistroFatture.CLIENTE = TBL_CLIENTI.RagioneSocial

Anyway, I solved and now I can retrieve all the data for the report controls, but I still have more rows in my 'table', and this results as a 6 page report, with printing 6 different invoices. 6 is the number of all of my records in the TBL_RegistroFatture, so my query displays them all
How can I get only the data displayed on my form, (except for the client's related ones which are not shown there)
Am I supposed to select different sources in my report? i.e.
DATA FATTURA from the form
[Forms]![FRM_RegistroFatture]![DATA]
CLIENTE from the quer
[Query]![QRY_JOIN]![CLIENTE

Or maybe have I to provide another SELECT query on the fly in the report

thanks agai
Paul

 

[Marshall Barton]

Sorry, I missed this follow up question.

I tried several times with different combinations to achieve the results with the query and I came up with these 2 queries, which provides the same 'table' data: one is using INNER JOIN, and the other WHERE.
I guess in this very case is pretty much the same thing using one of them.

SELECT TBL_RegistroFatture.*, TBL_CLIENTI.*
WHERE (((TBL_RegistroFatture.CLIENTE)=[TBL_CLIENTI].[RagioneSociale]))

That's incomplete, but I can imagine what the FROM clause
should be. Most people prefer using the INNER JOIN style
so we don't need to worry about it.

SELECT TBL_RegistroFatture.*, TBL_CLIENTI.RagioneSociale, TBL_RegistroFatture.CLIENTE
FROM TBL_RegistroFatture INNER JOIN TBL_CLIENTI ON TBL_RegistroFatture.CLIENTE = TBL_CLIENTI.RagioneSociale

Anyway, I solved and now I can retrieve all the data for the report controls, but I still have more rows in my 'table', and this results as a 6 page report, with printing 6 different invoices. 6 is the number of all of my records in the TBL_RegistroFatture, so my query displays them all.
How can I get only the data displayed on my form, (except for the client's related ones which are not shown there)?
Am I supposed to select different sources in my report? i.e.:
DATA FATTURA from the form,
[Forms]![FRM_RegistroFatture]![DATA]
CLIENTE from the query
[Query]![QRY_JOIN]![CLIENTE]

You need to filter the report to just the records you want
in the report. This is usually done by vreating a form with
a text/combo boxes for users to enter/select the client and
invoice id and a button to open the report. Assuming the
CLIENTE and invoice number are both numeric type fields, the
code in the button's Click event procedure would look
something like:

Dim stWhere As String
Dim stDoc As String
stDoc = "name of report"
stWhere = "CLIENTE = " & Me.cboCLIENTE _
& " AND invoicenumberfield = " & Me.cboinvoice
DoCmd.OpenReport stDoc, acViewPreview, , stWhere