Massive data collection

[Guest]

I need to bring a lot of financial data from several different tables where
they all have the same job number. I really have no idea where to start. Join
query won't work because the tables have all different fields. I have made a
report with subreports to display the totals, but not sure how to show the
totals of these reports in the same report so that I can subtract amounts
where needed.

Example: I have various change order amounts, addendum amounts, and a
contract amount with fields such as Material, Labor, Equipment, etc. Need to
add these all together and then subtract from this the bid amounts for each
field.

Any ideas would be greatly appreciated as I have been stuck on this for
several days.

 

[John Vinson]

I need to bring a lot of financial data from several different tables where
they all have the same job number. I really have no idea where to start. Join
query won't work because the tables have all different fields. I have made a
report with subreports to display the totals, but not sure how to show the
totals of these reports in the same report so that I can subtract amounts
where needed.

On the contrary: join queries are EXACTLY how you should do this. If
you assume that two tables must have identical fields to create a join
query, that assumption is simply wrong - joins are designed to join
*different* tables.

Since I have absolutely no idea about the structure of your table or
anything more than what you posted on what you're trying to
accomplish, I can't give a specific answer - but if you're dismissing
Queries, the basic essential tool of any Access application, out of
hand, you're looking in the wrong direction.

Example: I have various change order amounts, addendum amounts, and a
contract amount with fields such as Material, Labor, Equipment, etc. Need to
add these all together and then subtract from this the bid amounts for each
field.

You may need to create Totals queries on each of these various amounts
and then create a Join query joining the totals queries to your main
table... but again, that's a guess since I can't see your structure.

John W. Vinson[MVP]

 

[Guest]

I wasn't dismissing queries - love them! I will try the Totals query idea.

Thanks!

 

[Guest]

I still need help on this issue, please. I've created a crosstab query to
show the amount for each individual supplier which is then cross referenced
to show if the item purchased from the supplier was Gear, Fire Alarm, etc. I
have also added an "Other" supplier name to show amounts for meals, fuel,
etc. Problem is that when the query is ran - it's adding up the amounts of
meals wrong. Here's the SQL:

SELECT SpecialsCrosstabQuery.JobNumber, PurchaseOrder_Crosstab.OrderNo,
Sum(PurchaseOrder_Crosstab.CED) AS SumOfCED,
Sum(PurchaseOrder_Crosstab.[Kriz-Davis]) AS [SumOfKriz-Davis],
Sum(PurchaseOrder_Crosstab.American) AS SumOfAmerican,
Sum(PurchaseOrder_Crosstab.Stanion) AS SumOfStanion,
Sum(PurchaseOrder_Crosstab.Western) AS SumOfWestern,
Sum(PurchaseOrder_Crosstab.Other) AS SumOfOther,
PurchaseOrder_Crosstab.SupplierNotes, SpecialsCrosstabQuery.Fixtures,
SpecialsCrosstabQuery.Gear, SpecialsCrosstabQuery.FireAlarm,
SpecialsCrosstabQuery.Intercom
FROM PurchaseOrder_Crosstab INNER JOIN SpecialsCrosstabQuery ON
(PurchaseOrder_Crosstab.JobNumber = SpecialsCrosstabQuery.JobNumber) AND
(PurchaseOrder_Crosstab.OrderNo = SpecialsCrosstabQuery.OrderNo)
GROUP BY SpecialsCrosstabQuery.JobNumber, PurchaseOrder_Crosstab.OrderNo,
PurchaseOrder_Crosstab.SupplierNotes, SpecialsCrosstabQuery.Fixtures,
SpecialsCrosstabQuery.Gear, SpecialsCrosstabQuery.FireAlarm,
SpecialsCrosstabQuery.Intercom;


Thank you!