Report Design

[akemeny]

Hi,

My company is trying to convert from using Excel to Access for our
databases, however we are having trouble running reports. We have multiple
tables created and have reports, but we can not get it to run the way we need
it to. What I need it to do is have a master list of categories (ie:
Discharge Status) so that I can just run one report and have it pull from all
the tables without it including the table information. Is there a way to do
that?

 

[Duane Hookom]

I don't understand what you are asking. It seems you want tables but not
table. My WAG is that you created multiple very similar tables rather than a
single table.

How about providing some details about your tables and what you are
attempting to report?

 

[akemeny]

Well what it actually is... we currently have about 50 spreadsheets on Excel
that we are trying to convert over to Access, but we are only going to do it
if it will make running reports a little easier. As a trial I pulled some of
the data into Access and have been attempting to run reports with that data.
I have 3 tables that were created that I am trying to use. I can't make them
all one table because each table has information regarding a different audit
and I can't mix that information together. So what I'm trying to do is use
those three tables to run a report. I have 4 Column headings that I'm using
(D/C, RAC Date of Decision, RAC Decision, Appeal / Bypass). I would like to
be able to run a report or query of this information so that I can get a
total number of each different RAC decision Broken down by DOD, D/C and A/B,
but not broken down by table.

 

[Duane Hookom]

So, the tables have basically the same columns/fields. If this is the case,
IMHO, the records belong all in one table with a field that identifies the
"source". If you can't or don't want to do this, you can combine the similar
tables using a union query.

SELECT FieldA, FieldB, FieldC, FieldD, "A" as Source
FROM tableA
UNION ALL
SELECT FieldA, FieldB, FieldC, FieldD, "B"
FROM tableB
UNION ALL
SELECT FieldA, FieldB, FieldC, FieldD, "C"
FROM tableC;

Then use the union query as the record source of your report.

 

[NoodNutt]

Akemeny

As Duane has rightfully stated in his post "the records belong all in one
table with a field that identifies the "source"".

When you created your tables that house all the data you moved across from
Excel did you create a PrimaryKey (UniqueIdentifier) for each of the
imported records in each of the individual tables?

My guess is your DB is not rational, and although, this may not give you
problems now, as Duane has provided a short term solution to your problem
(assuming each table is the same), it will, when your DB grows in size and
the differing complexities of what you want from it will stop you in your
tracks!

With all that in mind, this is what your table structure should start out
like.

tblCases
txtCaseNo = PrimaryKey (AutoNumber, No Duplicates) - unless you have a
different unique format of case numbering, that's when you get into another
area of hurt.
txtCaseDate = Date/Time
txtOtherField(s) = Any other relavent info pertaining to specifics of the
case.


tblCaseStatus
txtCaseNo = ForeignKey = Number(Long Integer, Duplicates OK) Unless you want
a one-to-one relationship then you would have (No Duplicates)
txtDC = yourdatatype (do not use / as it is reserved)
txtRACDate = Date/Time
txtRACDecision = Text
txtOutcomeID = Number(Long Integer)

tblOutcome
txtOutcomeID = PrimaryKey(AutoNumber, No Duplicates)
txtOutcome = Text (Appeal & Bypass)

you can import all of your records into one table using eg CaseNo to tie all
your data together.

DB's work faster when you use numerics rather than a lot of text, eg using 1
uses one bit of room as opposed to text which can take up heaps more ei
"Appeal" is 6 bits long, so if you multiply that by 100K records the size
soon bloats from 1Kb to 6Kb, then multiply that by how many fields you have
populated with other text, it soon adds up. This is why you need to use a
different table for your Outcome, you use the UniqueNo that is assigned to
the respones eg 1 = Appeal & 2 = Bypass, you can create a relationship
between them in the Relationship window. Of course you will in all certainty
have to use text in fields, but the less you use the better it will run.


You will then need to generate a query to filter the data as per your needs.
The best way to do this is using SQL, this is not my forte, but your sure to
get all the assistance you need as soon as you rationalise your DB.

Once you have your query setup, you can then customise a report based on
that query to provide your desired outcome.

I'm confident you will heed Both Duane and my advise and think carefully
before proceeding any further with your DB's current state and re-design it
to a more functional, practicle and rational(Logical) one.

HTH
Mark.