UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Combining Multiple Tables to Create a Report, Office 2003    
 
   
1123csh
post Sep 2 2010, 01:15 PM
Post #1

UtterAccess Veteran
Posts: 306
From: Columbus, Ohio



Hello Everyone,

I will start off by showing you my table structure.

tbl-Baseinventory

PK-InventoryID-AutoNumber
FMNumber-Text
Description-Text

tbl-FuelUseage

PK-UsageID-AutoNumber
InventoryID-FK
FuelDate
Mileage
Hours

tbl-WorkOrderTbl

PK-WorkOrderNumber-AutoNumber
UsageID-FK

tbl-ShopFeeOutgoing

PK-ShopFeeID-AutoNumber
WorkOrderNumber-FK
ShopFeeCost-Currency
FeeType-FK

tbl-ShopFeeTable

PK-FeeTypes-Text

tbl-WorkOrdersDetailTbl

PK-WorkOrderDetailID-AutoNumber
WorkOrderNumber-FK
Parts-Text-FK
PartsUsedQuanitity-Number

tbl-MechanicsLaborHourstbl

PK-AutoNumber-AutoNumber
FK-WorkOrderNumber
FK-MechanicID
LaborHours-Number


So want I want to be able to do is create a report that will show all of the expenses per InventoryID. I already have created reports that show the expense per InventoryID for each one of the tables but I can't combine them into one large report so that I can look at all expenses together. Currently I'm printing off a report for ShopFees, PartsUsed and MechanicLaborHours all sepeerate and combining the expenses on a calculator then inputting that info into a spreadsheet.

I know there is a way to make one report show all of this info together!

Thanks for all the Help!!!!!!!!!!!!!!!!!!

P.S. Let me know if an attachment of the mdb would help. I know there is a lot here. Thanks Again.



Go to the top of the page
 
+
fkegley
post Sep 2 2010, 01:25 PM
Post #2

UtterAccess VIP
Posts: 23,623
From: Mississippi



You should be able to develop a query that joins the involved tables together via the PKs and FKs in each record of each table. Then base the report on the query.

If you have created relationships between the tables using the Relationships window, and not turned off AutoJoin, then as soon as you put two related tables in the query design, Access will join the tables for you. Once you have all the fields you need for the report, you can then add calculations using those fields to the query and/or report.

BaseInventory table can be joined to FuelUsage table via the InventoryID values in each record of each table, etc.
Go to the top of the page
 
+
1123csh
post Sep 2 2010, 01:42 PM
Post #3

UtterAccess Veteran
Posts: 306
From: Columbus, Ohio



Thanks for your quick reply fkegley!


If I combine all of these tables together in a query then how do I make it so that the query will not filter my results if lets say, one of my InventoryID's did not have a part used on it but did have MechanicsLaborHours etc. In this situation I can't get the query to show the records for the Laborhours.

Also if I have multiple parts then the query will repeat the record for the MechanicsLaborHours for every Part that is used.

Is there a way I can stop this from happening?

Thanks again!
Go to the top of the page
 
+
fkegley
post Sep 2 2010, 02:27 PM
Post #4

UtterAccess VIP
Posts: 23,623
From: Mississippi



You may need an Outer Join between two or more of the tables. An Outer Join fetches ALL from TableA and corresponding only from TableB. You can change the relationship type in the design view of the query. Just double-click the relationship line between the tables and the Join properties box should appear. Pick the one that you need.

Be aware that you generally cannot mix different join types in the same query. You can though develop QueryA to do the Outer Join between TableA and TableB, then use QueryA in another query in an Equi-Join with TableC.

If you are using this query in a report, you are aren't you?, then you can use the Hide Duplicates properties of the relevant controls to hide the duplicates. Or even group on the values that are duplicated in the report's recordset.
Go to the top of the page
 
+
1123csh
post Sep 2 2010, 02:56 PM
Post #5

UtterAccess Veteran
Posts: 306
From: Columbus, Ohio



Thanks again,

I'm going to be working on this tonight and hopefully you can help me some more tomorrow if I can't figure it out.


Thank you very much!!
Go to the top of the page
 
+
1123csh
post Sep 3 2010, 09:38 AM
Post #6

UtterAccess Veteran
Posts: 306
From: Columbus, Ohio



Well I spent a lot of time trying to get a query that would work and seem to still be strugling with keeping duplicate data out of the query. I have attached a copy of the mdb and I am hoping you could take a look or someone and possibly tell me how I can work around this issue.

If you open up the query called Query2 you will see all of the tables that I's trying to include in this hopefully soon to be report.

Thanks once again and this website is certainly my lifeline for Access.
Attached File(s)
Attached File  Copy_of_Fleet_Maintenance.zip ( 182.67K ) Number of downloads: 3
 
Go to the top of the page
 
+
mike60smart
post Sep 4 2010, 12:14 PM
Post #7

UtterAccess VIP
Posts: 9,544
From: Dunbar,Scotland



Hi

See the ammended query2

Also the rptParts based on query 2

You do however have a few problems in the structure of your Db

You have used Lookups in the Table fields

You have applied Primary Keys to Text data types - it is recommended that every table has its own Autonumber Primary Key
and these should be linked to a Number Foreign Key in the related table

Attached File  Copy_of_Fleet_Maintenance.zip ( 154.65K ) Number of downloads: 3
Go to the top of the page
 
+
1123csh
post Sep 4 2010, 01:50 PM
Post #8

UtterAccess Veteran
Posts: 306
From: Columbus, Ohio



Thanks a lot mike60smart!

I really appreciatte your help!

I do have a question though after looking over the amended query and report I found duplicate data for work order # 110. So that concerns me a bit that some of the other data could be duplicated as well. If you would'nt mind taking another look for me. I will toy with it some more to see if I can figure it out also thanks.

As far as my table structure I noticed that to. Its funny when I look back at databases I made a few years ago and am now able to find some things that I did wrong and was'nt able to catch back then! I really apreciate you pointing it things out to me though.

Thanks Again!
Go to the top of the page
 
+
mike60smart
post Sep 4 2010, 02:52 PM
Post #9

UtterAccess VIP
Posts: 9,544
From: Dunbar,Scotland



Hi

See the ammended Db

Not knowing what you are expecting as an output I have changed the query to only include those tables which contain the
fields you had showing.

It is grouped by MechanicID and sorted by Work Order ID

Please clarify if this is the output expected??

Attached File  Copy_of_Fleet_Maintenance.zip ( 153.38K ) Number of downloads: 8
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 16th April 2014 - 05:17 PM