Joining column data with tabular data in a query

[juicybeasty]

I have a database in which i have to record data relating to companies, with a page for each new company.

One of the pieces of data is key individuals who work for the company, and since this is a repeating field, i have set up a separate table called tbl KeyStaffand linked it to the company table with a one to many relationship.

The main form is columnar and the KeyStaff form is tabular.

There is also a proposal subform also linked to the main form with a one to many relationship, allowing me to record data for several proposals for each company.

The problem i am encountering is in setting up a query requiring the following fields: CoName (from tblCompany), Key Individuals (from tblKeyStaff), Amount (from tblProposal).

I need to know how to set it up so it only comes up with one line for the company and the amount, but as many as necessary for the key individuals, in order to produce a tabular reporting fitting on one page.

I suspect it is to do with joins and probably the DISTINCT keyword, but don't know how to take it further.

Any help appreciated. Thanks

 

[namliam]

How do you invision this? make an example

Company a
Person 1
Person 2
Proposal..

Regards

 

[juicybeasty]

Something like this:

Company Name Key Individuals Proposal amount

[Co 1 Name] fred, joe, mary £500,000
[Co 2 Name] mark, judith £100,000

etc

 

[namliam]

I think Mile-O-Phile posted some solution for this not to long ago, but i cannot seem to find it. Try searching the forum (on his name maybe) or wait untill he comes around to posting the link for ya...

Regards

 

[juicybeasty]

Thanks, I've been searching using 'join' which is coming up with a lot of stuff which i've been browsing through looking for inspiration!

Will try that now.

 

[Rich]

Article ID: Q141624

.

 

[juicybeasty]

thick question: how do you search by article ID?

 

[Mile-O]

Go to Microsoft Knowledge Base and search for the article in question.

 

[juicybeasty]

Ah, that's why I couldn't find it. Thanks v much!

 

[neileg]

Of course if you could live with this layout, it's a simple report with grouping

[Co 1 Name] £500,000
fred
joe
mary

[Co 2 Name] £100,000
mark
judith

 

[Pat Hartman]

Reports should be built the same way that you build forms when you want to work with data in the 1-side table and the many-side table. Use a main report with 1 or 2 subreports.

 

[juicybeasty]

Thanks a lot - the combination of Q141624 and Pat's advise to use subreports gave me exactly what i wanted.

I have another problem now though. I need to sum the amount in the proposal subreport and show this figure on the main report.

I have followed previous advise on the forum and put a total =sum(Amount) in the report footer of the boardproposal subreport. The figure is correct here.

But then i try and reference this in the report footer of the main report (boardmain) using this syntax: =Reports![boardmain]![boardproposal].Report![Total] - and it doesnt show the same figure as the TOTAL on the subreport, but instead the amount in the LAST RECORD in the (tabular) subreport.

Any ideas?

(I have got round it by adding a further subreport based on a select query which sums the amount, and this works, but i don't think you're supposed to do it this way in an ideal world are you?!)

 

[Fizzio]

But then i try and reference this in the report footer of the main report (boardmain) using this syntax: =Reports![boardmain]![boardproposal].Report![Total] - and it doesnt show the same figure as the TOTAL on the subreport, but instead the amount in the LAST RECORD in the (tabular) subreport.

Make sure that you are referencing the correct control and that the control has a different name to the fields on the report ie rename the control txtTotal if you have a field called Total in the recordsource.

 

[juicybeasty]

Thanks.

I tried naming the field on the subreport something completely arbitrary - txtTotalAmountProposal235 - and it's still doing the same thing.

So I don't think that's it.

Is it something to do with the fact that the subreport (boardproposal) is in list (table) form, and it is only taking the last Amount value in the list rather than all of them?