Can you have a report with multiple record sources (1 Viewer)

[tmyers]

In this report I am building, the report is based off of one query (a crosstab that I finally got working) but I would like to add one more for a final row on the report. I have been messing with sub reports and such, but they don't stay "in line" with the rest of the report and how it grows.

How can I add another column to my report that grows with that data? I dont know how the wizard accomplishes this.

Edit: Adding Example
The report is:

Next to column 6, I want to add another column that is controlled by a different source. It would only need to add quantites and not types, as they are link to the same thing so should be identical.

The report/subreport I made is:

Nice and simple. I just cant figure out how to have the query/subreport on the main report and everything stay in line with each other and "grow"

 

[theDBguy]

Just as a quick test, try using Anchoring with your Subreport.

 

[tmyers]

Could you possibly clarify what you mean by Anchoring? Thats not a term (outside of nautical) I am familiar with.

 

[theDBguy]

Could you possibly clarify what you mean by Anchoring? Thats not a term (outside of nautical) I am familiar with.

Sorry, I'm talking about something like this.

Anchoring

The anchoring feature has been available since Access 2007, but I noticed from several questions posted in the forums that some users are st...

thedbguy.blogspot.com

 

[tmyers]

I don't think that would solve my problem. I revised my initial post with a little more detail if that helps. I think I may need a union query, which I have never done and I don't think you can do those with a crosstab query.

 

[theDBguy]

I don't think that would solve my problem. I revised my initial post with a little more detail if that helps. I think I may need a union query, which I have never done and I don't think you can do those with a crosstab query.

Okay, maybe you could try it without using a subreport. Could you create a new query to join your crosstab query with the other data source?

 

[tmyers]

Okay, maybe you could try it without using a subreport. Could you create a new query to join your crosstab query with the other data source?

I was thinking that and am reading up on it now. Can a crosstab be used in a union? I am finding crosstabs while extremely helpful, are also extremely hard to use in other things.

 

[CJ_London]

if you mean 'grow' horizontally then you need a dynamic report with as many columns as you can have and hiding the ones not yet required.

the column header labels would also need to be populated with the correct column heading - but can't tell from your example whether this is a requirement.

With regards using a different table for your 7th column. Without knowing the detail, suggest you create a new query which joins your crosstabl to your other table on the 'type' field

Note that Type is a reserved word and should not be used as a field name

 

[theDBguy]

Can a crosstab be used in a union?

Crosstabs are just like any other source of data. You can use it within another query, if you like.

 

[tmyers]

Note that Type is a reserved word and should not be used as a field name

That I was actually aware of. It is just renamed. The actual field name is TypeName or TypeID depending.

I made a query consisting of the two, but its giving weird results.

It is showing the same type for every sum. Why is that? The actual count for S1 is 60.

 

[theDBguy]

That I was actually aware of. It is just renamed. The actual field name is TypeName or TypeID depending.

I made a query consisting of the two, but its giving weird results. View attachment 85947
It is showing the same type for every sum. Why is that? The actual count for S1 is 60.

Can we see your SQL statement?

 

[tmyers]

Can we see your SQL statement?

SELECT AssignSequenceXtab.*, CountSum.SumOfQuantity, CountSum.JobID
FROM AssignSequenceXtab, CountSum
WHERE (((CountSum.JobID)=[Forms]![JobQuote]![JobID]))


I feel like it should have a GROUP BY, but not sure how to add it.

 

[theDBguy]

SELECT AssignSequenceXtab.*, CountSum.SumOfQuantity, CountSum.JobID
FROM AssignSequenceXtab, CountSum
WHERE (((CountSum.JobID)=[Forms]![JobQuote]![JobID]))


I feel like it should have a GROUP BY, but not sure how to add it.

Ah, you're producing a Cartesian Product with that query. How is the crosstab data related to the CountSum data? You'll need to JOIN the two using that relationship.

 

[tmyers]

Ah, you're producing a Cartesian Product with that query. How is the crosstab data related to the CountSum data? You'll need to JOIN the two using that relationship.

That....that was remarkably simple. So much so I fear my computer may burst into flames. I simply joined them within the query and it cleaned that up..

Excuse me while I go bang my head on the wall for a minute for not thinking of that.

 

[theDBguy]

That....that was remarkably simple. So much so I fear my computer may burst into flames. I simply joined them within the query and it cleaned that up..

Excuse me while I go bang my head on the wall for a minute for not thinking of that.

Happens all the time with most of us. Glad to hear you got it sorted out. Cheers!