Report based on multiple unrelated tables

[cosmos...@gmail.com]

In Access 2010, how can I make a report based on multiple unrelated tables? For instance, for each of the last three months, I would like to list all employees hired that month ("Employees" table) and the SKU of every product sold that month ("Sales" table).

Thanks in advance!

[David Hare-Scott]

Make a subreport bound to each table (or query) and put them all on a
container report. The container is not bound to anything and so there would
be no parent-child relationship between the container and each subreport.
You can put headers etc on the main report including the period of reporting
based on the parameter(s) the user enters which is also used to sub-set the
records in each subreport.

David

[Aikistan]

On Wednesday, March 27, 2013 7:37:45 PM UTC-4, David Hare-Scott wrote:
> Make a subreport bound to each table (or query) and put them all on a container report.

David, I'm not familiar with the term "container report." Is this merely a report with subreports or a feature that I'm not familiar with (The MS Visio "containers" feature makes me wonder if it could be the latter)?

Thank you,

Stan

[Access Developer]

In Access terminology, it'd just be a Report that _contains_ additional
Report(s) in a Subreport Control(s). Access does have Container objects, but
no Report object called a Container Report.

--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

"Aikistan" <stan.mi...@gmail.com> wrote in message
news:73410617-be35-449d...@googlegroups.com...

[Aikistan]

On Thursday, March 28, 2013 1:59:06 PM UTC-4, Access Developer wrote:
> In Access terminology, it'd just be a Report that _contains_ additional Report(s) in a Subreport Control(s). Access does have Container objects, but no

Ah, thank you. Both my options were true. That is, a report containing subreports and there actually is a container object that I'd never heard of. :)

Stan

[David Hare-Scott]

I called it that because its role is to contain the subreports, it has no
data.

D

[Phil]

Here is an example of function containing .... what else....Containers

Function GetDescription(ObjName As String, ObjType As Long) As String
' Get descriptions of Forms, Reports. Queries etc

On Error GoTo GetDescriptions_Err

Select Case ObjType
Case -32768 ' Forms
GetDescription cription =
CurrentDb.Containers!Forms.Documents(ObjName).Properties("Description")

Case -32766 ' Macro Scripts
GetDescription cription =
CurrentDb.Containers!Scripts.Documents(ObjName).Properties("Description")

Case -32764 ' Reports
GetDescription cription =
CurrentDb.Containers!Reports.Documents(ObjName).Properties("Description")

Case -32761 ' Modules
GetDescription cription =
CurrentDb.Containers!Modules.Documents(ObjName).Properties("Description")

'Case -32758 ' Admin

'case -32752 ' Access Info

Case 1 ' tables
GetDescription cription =
CurrentDb.TableDefs(ObjName).Properties("Description")

Case 2 ' Databases
GetDescription cription =
CurrentDb.Containers!Databases.Documents(ObjName).Properties("Description")

Case 3 ' Objects
GetDescription cription =
CurrentDb.Containers!Objects.Documents(ObjName).Properties("Description")

Case 5 ' Queries
GetDescription cription =
CurrentDb.QueryDefs(ObjName).Properties("Description")

Case 6 ' Attached tables
GetDescription cription = "Attached " &
CurrentDb.Containers!TableDefs.Documents(ObjName).Properties("Description")

'Case 9 ' SQL

Case Else
GetDescription = "Unavailable Information"

End Select
Exit Function

GetDescriptions_Err:
If If Err = 3270 Or Err = 3265 Then ' Property doesn't exist
Resume Next
Else
MsgBox Err.Description
End If

End Function

Phil

[Aikistan]

On Thursday, March 28, 2013 7:49:13 PM UTC-4, Phil wrote:
> On 28/03/2013 21:23:45, "David Hare-Scott" wrote: I called it that because its role is to contain the subreports, it has no > data.

Phil

Thanks, Phil, that's handy.

Mr. Hare-Scott, I wasn't trying to call you out. I genuinely didn't know if there was a special type of report specifically for containing unrelated tables and which was called a "container report." I was hopeful there was such a thing and that it worked like Visio's containers, since doing the layout for Access reports with subreports is tedious.

Stan

[David Hare-Scott]

Aikistan wrote:
> On Thursday, March 28, 2013 7:49:13 PM UTC-4, Phil wrote:
>> On 28/03/2013 21:23:45, "David Hare-Scott" wrote: I called it that
>> because its role is to contain the subreports, it has no > data.
>
> Phil
>
> Thanks, Phil, that's handy.
>
> Mr. Hare-Scott, I wasn't trying to call you out.

I didn't feel you had. And David would be fine.

I genuinely didn't
> know if there was a special type of report specifically for
> containing unrelated tables and which was called a "container
> report."

That is how I took your comments. I didn't mean to confuse the issue with
an informal use of the term when there is a more technical use of it but I
did it anyway!

David

[SaltyBoat]

On Wednesday, March 27, 2013 3:52:33 PM UTC-7, cosmos...@gmail.com wrote:
> In Access 2010, how can I make a report based on multiple unrelated tables? For instance, for each of the last three months, I would like to list all employees hired that month ("Employees" table) and the SKU of every product sold that month ("Sales" table).
> Thanks in advance!

The subreport method, mentioned elsewhere in this thread is probably your best option.

Another option that work sometimes for reports with unrelated tables is to merge the tables into a single recordset using a "union join".

[David Hare-Scott]

But the tables have different topics and different fields in this case so
that won't work. Union querys are only useful when the fields from each
table align in type and meaning and each can be given a common label.

David