CODEWITHSUNDEEP
ms-accessreport
Rory
Rory

Reputation: 336

Generate MS Access report that includes multiple tables/queries

Im using Access 2007 to import statistics (Excel) from multiple units within a department. Although they share similar fields, each Unit has it's own table because they also have unit specific data/fields that needs to be tracked.

Table:UnitA -(Fields:Date,Cars, Boats, Tags, Guns, Missing Persons, etc.)

Table:UnitB -(Fields:Date,Cars, Boats, Tags, Guns, Bench Warrants, etc.)

Table:UnitC -(Fields:Date, Cars, Boats, Tags, Guns, Fingerprints, etc.)

I need to create a summary report that queries a START DATE and END DATE and then produces a report that will display:

Sums of each of the individual fields from each record ie Sum([Fingerprints]) Sums of each of the shared fields from each record ie Sum([UnitA!Cars]) + Sum([UnitB!Cars]) etc.

I can create a report for each individual unit without any problems.

My problem begins when I ceate three Unit reports and try to use subreports. Access will prompt me for new Start Dates and End Dates.

Does anyone have a better solution? Should I create a "prompt" form that includes a Start Date and End Date field that can be accessed from within each query?

Upvotes: 0

Views: 11158

Answers (2)

XIVSolutions
XIVSolutions

Reputation: 4502

Even better - Import the shared unit data into a units table, then import the statistical data into a details table:

CREATE TABLE Unit (
    UnitID CONSTRAINT PK_unit PRIMARY KEY,
    UnitNo VarChar,
    OtherUnitInfo )

CREATE TABLE DetailCategory (
    CategoryID CONSTRAINT PK_DetailCategory PRIMARY KEY,
    Category Varchar ) -- example rows: 'Guns' 'Missing Persons' etc

CREATE TABLE Unit_DetailCategory (
    UnitID FOREIGN KEY (UnitID) REFERENCES Unit (UnitID),
    CategoryID FOREIGN KEY (CategoryID REFERENCES DetailCategory (CategoryID),
    Qty Long )

Then, you can use TRANSFORM/PIVOT, something like this, to grab your summed values:

TRANSFORM Sum(Unit_DetailCategory.Qty) AS SumOfQty
SELECT 'Sum of Unit Values' AS FunctionName
FROM DetailCategory 
RIGHT JOIN (Unit INNER JOIN Unit_DetailCategory 
    ON Unit.UnitID = Unit_DetailCategory.UnitID) 
    ON DetailCategory.CategoryID = Unit_DetailCategory.CategoryID
GROUP BY 'Sum of Unit Values'
PIVOT DetailCategory.Category

Note - my syntax may be flawed in the CREATE statements, and you may have to play with the TRANSFORM/PIVOT to get precisely what you want. But this works on my machine.

Upvotes: 2

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112342

You can create a single query by using unions

SELECT Date, Cars, Boats, Tags, Guns,
   [Missing Persons], Null AS [Bench Warrants], Null AS Fingerprints FROM UnitA
UNION ALL
SELECT Date, Cars, Boats, Tags, Guns,
   Null,              [Bench Warrants],         Null                 FROM UnitB
UNION ALL
SELECTG Date, Cars, Boats, Tags, Guns,
   Null,              Null,                     Fingerprints         FROM UnitC

You must have the same number of fields in each SELECT. The field names of the first SELECT will be used for the result.

However, I suggest another DB design. Place all the commom fields in a common table and place the unit-specific fields in other tables that you join in a 1-to-1 relation

CREATE TABLE Unit (
    UnitID  CONSTRAINT PK_unit PRIMARY KEY,
    Date datetime,
    Cars long,
    Boats long,
    Tags long,
    Guns long
)

CREATE TABLE UnitA (
    UnitID  CONSTRAINT PK_unitA PRIMARY KEY,
    [Missing Persons] Long
)
ALTER TABLE UnitA
   ADD CONSTRAINT FK_unitA_unit
   FOREIGN KEY (UnitID) REFERENCES Unit (UnitID)


CREATE TABLE UnitB (
    UnitID  CONSTRAINT PK_unitB PRIMARY KEY,
    [Bench Warrants] Long
)
ALTER TABLE UnitB
   ADD CONSTRAINT FK_unitB_unit
   FOREIGN KEY (UnitID) REFERENCES Unit (UnitID)


CREATE TABLE UnitC (
    UnitID  CONSTRAINT PK_unitC PRIMARY KEY,
    Fingerprints Long
)
ALTER TABLE UnitC
   ADD CONSTRAINT FK_unitC_unit
   FOREIGN KEY (UnitID) REFERENCES Unit (UnitID)

Now you can create a query with left joins.

An even easier design is to create just one table, and to allow empty fields

CREATE TABLE Unit (
    UnitID  CONSTRAINT PK_unit PRIMARY KEY,
    Date datetime,
    Cars long,
    Boats long,
    Tags long,
    Guns long,
    [Missing Persons] Long,
    [Bench Warrants] Long,
    Fingerprints Long
)

Instead of importing the excel tables directly, just link the excel tables and then append their content to the common table with customized append queries.

Upvotes: 1

Related Questions