I am having some problem with a query. I have 3 tables - A, B and C. A is the main table. There is a 1-M relationship between A & B and a 1-1 relationship between A & C. I want to select all the fields from all the 3 tables so that I can create a Report. My SQL query goes somewhat like this:

SELECT A.CodeNo, A.District, A.Agency, A.Address, A.Nature, A.RegNo, A.RegPlace, A.RegDate, A.RegCert, A.MoA, A.Audit, A.Contact, A.Pin, A.Phone, A.PastExp, B.Year, B.Grant, B.UtilCert, B.Report, B.AccState, A.Awareness AS A_Awareness, A.Action AS A_Action, C.Awareness AS C_Awareness, C.Action AS C_Action, C.Total, C.Recomm, C.ActionRecomm,C.TotalRecomm, FROM (A INNER JOIN C ON A.CodeNo = C.CodeNo) INNER JOIN B ON A.CodeNo = B.CodeNo;

For a single record in table A there may be many records in table B. The above query repeats all the fields other than that in table B. ie., If for a record in A, there are 3 records in B, then the answer of the above query is 3 records with difference only in the B fields.

What is that I am missing? I tried some WHERE clauses, but then the query does not run. Any help is highly appreciated.

Thanks in advance for your time!

This is normal behaviour, you are requesting the database to give you the information from tables a, b and c and that is what it is doing.

If table b contains 3 seperate unique records, then the single records from tables a and c will replicate down along with the unique records of table b.

You can hide the "extra" data in the report(s) tho...

Thanks mailman for the reply.

But I still have a problem. I am creating a report from this query. I want all the records in table B which are connected to a record in table A to be shown in a single report. Now if there are 3 connected records then I am getting 3 reports with difference only in B fields value.

You have asked me to hide the extra data in report. But I am not quite sure how?

Please help and thanks in advance for your time.

You can make sections in the report, this section you can show the data from table A & C, then in a sub-section you can show (only) the data for Table B
You would get something like

Record 1 table A
Table B 1 - 1
Table B 1 - 2
Table B 1 - 3
Record 2 table A
Table B 2 - 1
Table B 2 - 2
etc.

Thanks for the reply Mailman. It worked. But I do have one more doubt. I have 3 related records in table B for a record in table A. The subreport is showing all the 3 data if given in columnar format. But when I tried in tabular format the field names are repeating. I want the data as

Year 2005 2006 2007
Amount 1000 2000 3000
report yes no yes

Now I am getting the data as

Year 2005
Amount 1000
Report yes

year 2006
Amount 2000
report no

Could you please give your suggestion on this? I tried some section hide, but may be my concept on that is wrong b'coz it is not working.

Thanks in advance for your time

Try a crosstab query

Thanks Rich, I tried the crosstab query, but I get the answer in column format which I don't want. As I wrote earlier I want it in a tabular format something like

Year 2005 2006 2007
Amount 1000 2000 3000
report yes no yes

Thanks in advance!

It is somewhat possible, i.e. using a crosstab.

But if that is not what you are looking for... then it is going to be increadably hard (mind you not impossible) to get this to work.

Thanks Mailman. The format I want in my report is simple, isn't it? I can get the details one under the other without the column heading being repeated if I go for tabular layout, but if I select column layout the column headings are repeating. Why is it so? Isn't there any way to get what I want?

Thanks in advance

Unfortunatly there is no easy way to "transpose" your complete table in the report... Or not that I know of...

You could code something to export the table/data/query you want as a report into excel, then transpose your data there and show the excel sheet as the report...