Report on un-related tables

mouse88 · Apr 29, 2009

I have six tables which contain the same sort of data but aren't related in any way. The first thing i want to do is have a report that shows all fields of each table on one page.

I would also then like to get the sum for two different columns from all six tables. The columns are called Total and Completed. I then want a report showing the result.

wiklendt · Apr 29, 2009

mouse88 said:
I have six tables which contain the same sort of data but aren't related in any way. The first thing i want to do is have a report that shows all fields of each table on one page.

have you tried to make a separate report for each. then add each of those reports to one unbound report - similar to what you'd do with forms?

mouse88 said:
I would also then like to get the sum for two different columns from all six tables. The columns are called Total and Completed. I then want a report showing the result.

the six individually? or sum them into one all-encompassing number?

mouse88 · Apr 30, 2009

have you tried to make a separate report for each. then add each of those reports to one unbound report - similar to what you'd do with forms?

I have made separate reports for all of my tables. How do I add each of them to an unbound report?

the six individually? or sum them into one all-encompassing number?

Basically I just want one over all total for both columns total and completed

Thanks for the help

wiklendt · May 1, 2009

mouse88 said:
I have made separate reports for all of my tables. How do I add each of them to an unbound report?

Ok, you just create a new report in design view, and NOT have anything in record source. then just add the reports as subreports and NOT assign any child/master fields (just click on finish without putting anything in there)

mouse88 said:
Basically I just want one over all total for both columns total and completed

you may have to post your DB here after you make the unbound report with the subforms... my forte in NOT in summation, but we can work something out

mouse88 · May 4, 2009

Ok i have created the report with all my sub reports. This is the report called total. I have attached my database for you. Any advice on making my totals report look better as it looks very tacky at the moment.

The next thing i wanted to do was to calculate the total for the total and completed colums of all tables and have this displayed as a report with the column names as the titles.

Appreciate the help!

The password for the database is "A5B7C5D8E4"

Dont worry its only test data at the moment

wiklendt · May 5, 2009

mouse88 said:
Ok i have created the report with all my sub reports. This is the report called total. I have attached my database for you. Any advice on making my totals report look better as it looks very tacky at the moment.

The next thing i wanted to do was to calculate the total for the total and completed colums of all tables and have this displayed as a report with the column names as the titles.

Appreciate the help!

The password for the database is "A5B7C5D8E4"

Dont worry its only test data at the moment

i only see 6 tables. do you have a front end that you forgot to upload?

mouse88 · May 5, 2009

No theres no front end just tables and reports as the database is being used my my visual basic application users wont have any direct interaction with the database itself.

wiklendt · May 5, 2009

but that's what i'm saying - i don't see any reports. when i open your database, i only see six tables, nothing else.

mouse88 · May 5, 2009

Sorry my fault i had attached one of my testing databases. Correct one with reports is attached. Password is the same. Thanks

Please could you also advise why the sub reprts look different to the main reports?

wiklendt · May 5, 2009

mouse88 said:
Please could you also advise why the sub reprts look different to the main reports?

yeah, that's a thing where the subs don't display the page header. this would need to go in to the report header, under the titles that you have there.

thanks for the updated db - i'll have a look at it during the day - gotta get ready for work now!

mouse88 · May 5, 2009

I have changed my reports now havent finished them all but managed to make them look a bit more professional. The only one im stuck with no is having a report showing the sum of the Total and Completed columns of all tables.

I have attached updated database.

Appreciate your help.

By the way it was time for me to go to bed when u were going to work, completely different time zone

wiklendt · May 6, 2009

you forgot the attachment ;P

wiklendt · May 6, 2009

ok, so i've had a look at your db.

you need to read up on normalisation.

your tables aren't actually "completely unrelated": if you look, they all have IDENTICAL field names, only different table titles - this should have given you a clue that they are in fact somewhat similar. you could make all your separate tables into ONE table (tblPolicies), with some supporting tables (like tblBroker, tblScreenType).

based on your current table(s) i suggest the following:

tblPolicies
------------
PolicyID (Autonumber, PK)
ListDate (Date, Short Date)
ClientID (Number, FK) ?most policies i'd guess have some sort of client, no?
BrokerID (Number, FK)
ScreenTypeID (Number, FK)
Total (Number)
Completed (Number)
CDRFRdate (Date)
Notes (Text)

(note: i don't know if these are going to be 100% appropriate for you - i've never worked in insurance adn have no idea what CDR is, but it looked like something you repeated a lot, so perhaps it could be out on it's own - if there are a couple of things you'd frequently put into your old notes field, then perhaps they can be in their own table like broker and screen type below)

tblClient
-------------
ClientID (Autonumber, PK)
ClientName (Text)
ClientPhone (Text)
.
.
.etc.. clienty stuff.

tblBrokers
-------------
BrokerID (Autonumber, PK)
Broker (Text)

tblScreenType
--------------
ScreenTypeID (Autonumber, PK)
ScreenType (Text)

once your tables are in order, then your totals will be a breeze to do, and also can be done in just ONE report - and this report can be filtered per sreen type if you need to display the totals for just one.

here are a couple of threads that explain in more detail what i mean by the above. and also to get you started.

http://www.access-programmers.co.uk/forums/showthread.php?t=171031 (post #13)

http://www.access-programmers.co.uk/forums/showthread.php?t=171171 (may help you further from the first post)

one very special thing you need to do in your db is create forms for data entry.

tables are for data STORAGE, forms for data ENTRY and DISPLAY, and reports for DISPLAY and PRINT. stick to that kindof order and you'll be right as rain troughout your project

mouse88 · May 6, 2009

Ok thats kinda confused me slightly maybe if i explain what my application does this this will make things easier.

Basically i haven't vreated any forms as none of my data entry is done in access it is all done from my vb.net application.

Basically the system is to keep track of windscreen claims for an insurance company. These claims come in roughly once or twice a week on lists. Some of these lists are divided by the broker but some just come as one bulk list hence some of the tables containing a broker name and some being left blank.

The system basically allows a user to add a new list to the database, delete a list, modify a list, display the total number of claims outstanding and then i also want the ability to view and print reports which will be run off at the end of each week.

The reports i wanted are:

Total claims and total outstanding claims for each windscreen company (each table in my db design) on seperate reports
Totals claims and total outstanding claims for each windscreen company on one report
Total claims and total outstanding claims for all lists in all tables

The only report i am unable to do is the one which shows the total claims and total outstanding claims for all lists in all tables.

If theres anything else youd like to know then just let me know

wiklendt · May 7, 2009

mouse88 said:
The reports i wanted are:

Total claims and total outstanding claims for each windscreen company (each table in my db design) on seperate reports

Totals claims and total outstanding claims for each windscreen company on one report

Total claims and total outstanding claims for all lists in all tables

ok, so you're still going to need to normalize your data... i don't know anything about vb.net and nothing about your current setup (how far along is this project - would it be a massive disaster to alter the tables?)

your companies should have their own table tblCompanies, rather than each company have its own table. then, one table to collect the data you've been collecting, with one field where you assign a company.

let me give you an analogy of collecting a client address. you would EXPECT a table like this:

Code:

tblClient
------------
ClientID
ClientName
StreetNo
StreetName
CityID (which will draw an existing list of cities to choose from

then a table for the cities, to prevent repeating:

Code:

tblCities
-------------
CityID
CityName

what YOU currently HAVE is this:

Code:

tblSydney
--------------
ClientID
ClientName
StreetNo
StreetName

tblMelbourne
--------------
ClientID
ClientName
StreetNo
StreetName

tblNewYork
--------------
ClientID
ClientName
StreetNo
StreetName

this is BAD becuase what if you want to add a new city? or what if you want to add a new data field? (meaning you'd have to change EVERY table you make). what if you want to search for a client, but can't remember what city they're in? what if you want to search for all people with the surname "Smith", would you prefer to do one search in one table, or a convoluted mess of queries across multiple tables?

with the first example, if you want to add a new city, you just go add it to the cities table, and it's available for you to use to add or change data in the client table. you search one field in one table.

when you want to make a report for all cities, that's easy done because you can group your records by city, and have sums for cities and then an overall sum across the board. you can even filter that report to only show you your chosen city.

mouse88 · May 7, 2009

Ok i think i understand you know. My project is actually near completion now apart from displaying the reports and the help file but i will be revising it at some point after its been released and tested so when revising i will play about with my tables and normalise them.

If i understand you correctly i would need something like this:

tblWindscreenCompany
----------------------
CompanyID
CompanyName

tblBroker
---------
BrokerID
BrokerName

tblClaims
---------
Index (Primary Key)
CompanyID
BrokerID
ListDate
TotalClaims
ClaimsCompleted

Is this correct?

wiklendt · May 7, 2009

from what information i have of your project, that looks pretty good.

i would call my index in the claims table "ClaimID", rather than "Index" - it's more descriptive

mouse88 · May 9, 2009

Ok cool, if i was to deign the data entry forms in access, when it comes to the windscreen company and broker would i load the contents of the windscreen company and broker tables into comboboxes?

And also in the database itself would it then just display the windscreen company id and Broker id rather than the actual names themselves?

Thanks

wiklendt · May 11, 2009

mouse88 said:
Ok cool, if i was to deign the data entry forms in access, when it comes to the windscreen company and broker would i load the contents of the windscreen company and broker tables into comboboxes?

And also in the database itself would it then just display the windscreen company id and Broker id rather than the actual names themselves?

Thanks

the tables would store just the ID, but that's not a problem because users aren't meant to have access to the tables. they have access to forms and queries - in a query you can easily join two tables and have the broker name displayed instead of the ID (but access knows which name belons with which ID because of the Pk/Fk relationship).

forms also - a combobox can be user friendly and display the broker name, but store the ID in the table. that's why people set their 'column widths' to, say "0cm; 4cm" - the 0cm has 'no length' and therefore not visible to the user, and the 4cm column of that combo would have the name, which IS visible to the user.

Report on un-related tables

Registered User.

i recommend chocolate

Registered User.

i recommend chocolate

Registered User.

Attachments

i recommend chocolate

Registered User.

i recommend chocolate

Attachments

Registered User.

Attachments

i recommend chocolate

Registered User.

i recommend chocolate

i recommend chocolate

Registered User.

i recommend chocolate

Registered User.

i recommend chocolate

Registered User.

i recommend chocolate

Similar threads

Users who are viewing this thread