Help with table set up and reports

[gscma1]

hi, i am creating a database which should if it works correctly create a cashflow forecast.

i have two tables tblDevapp and tblCostCodes i need to draw information from both of these tables to create a report.

the whole database contains about 7 tables
Jobs; DevApp Budget; DevApp Actuals; CostCode Budget; CostCode Actuals; Valuation Id; Sales

initially we do a budget, the information in table DevApp Budget and CostCodes Budget are combined to give the appraisal.

I am struggling though as when i input the actual figures, i can combine the DevApp Actuals and CostCodes Actuals to get a total but I cannot use these two tables to make a report which shows the monthly totals.

I know this is quite vague so if any can tell me what information they require i would be happy to provide you with more. I'm just very confused.

Thanks

 

[Golom]

The implication of "monthly totals" is that, somewhere, there is a Date/Time stamp that identifies when it all happened.

So where is (do you have) such a Date/Time field?

Cumulating by month isn't terribly difficult but we would need to see which fields are in these tables ... or at least the ones that are necessary to relate the tables, identify the months and compute the totals.

 

[gscma1]

The monthly totals are actually valuations which take place each month. these valuations are fine, i have a report which combines the fields from both DevAppActuals and CostCodeActuals.

these valuations have five sections. I want a report which shows all valuations by month and which just has the totals for each section and the total valuation, then i would like a cumulative total. I have got my report to do this however, for some reason it lists the same month twice, and one lot is the data from CosCodeActuals table and the other is from DevApp Actuals. - I want them to be together because combined they make the actual valuation.

Do i have to change my primary/foreign keys? should i have month and job id's in both tables?

very confused. many thanks.

 

[lespaul]

can you give some example data from the tables and your expected results from that data? It's much easier to "see" what you want to do looking at data.

Thanks,

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[gscma1]

DevAppActuals
DAId
MonthId
JobId
Finance Fees
Legal Fees
Interest
Planning Fees

CostCodeActuals
CCAId
DAId
Brickwork Shell
Foundations
Footings
Excavation
Groundfloor Slab

Job
Job Id
Job Name

These are the tables that I want to be linked, the information in DevAppActuals has to remain seperate than that in CostCodeActuals.

 

[lespaul]

ok, that helps explain your table structure....what does the data in those tables look like? What do you want the results to look like?

See Thread701-1279071 to see how much easier it is and to see a good example of "showing" us what you need.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[gscma1]

DevAppActuals
DAId : 1
MonthId : 1
JobId : 1
Finance Fees : £4000
Legal Fees : £90
Interest : £0
Planning Fees : £300

CostCodeActuals
CCAId : 1
DAId : 1
Feasibility Study : £30
Brickwork Shell : £4000
Foundations : £3000
Footings : £5000
Excavation : £5000
Groundfloor Slab : £0

Job
Job Id : 1
Job Name : Example Job

Results:
to appear in sections : the fields from each table creat the different headings, for example, Finance Fees; Legal Fees and Feasibility Study make up the 'Preliminaries' section.

Job Name : test
Month Id : 1
Preliminaries : £*****
Substructure : £*****
Superstructure : £*****
Services : £*****
Contingencies : £*****
Total : £******
Cumulative : £******

Month Id : 2
Preliminaries : £*****
Substructure : £*****
Superstructure : £*****
Services : £*****
Contingencies : £*****
Total : £******
Cumulative : £******

etc. etc....

 

[lespaul]

Here's a start:

SELECT JobName, MonthID, SUM([Finance Fees] + [Legal Fees] + [Feasability Study] As Preliminaries
FROM Job J
INNER JOIN DevAppActuals D ON J.JobID = D.JobID
INNER JOIN CostCodeActuals C ON D.DAId = C.DAId
GROUP BY JobName, MonthID


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi