Master Query

[mithani]

Dear all,

I have a table "Project Master Code", contains: Project code, Cost Code, Description, Budget Amount, Total Purchase, Left to Complete.

For each project, we enter all above field except Total Purchase. Total Purchase to be taken from Transaction table.

What I want is that, I want to creat a query based on Project Master Code table. I will call all field from Project Master Code and in Total Purchase column I want all Purchase from Transaction table.on based of Project Code & Cost Code.

thanks

mithani

 

[neileg]

You shouldn't be storing this. Just calculate it in a query.

 

[mithani]

Dear neileg,

You are right, thats what I am looking for. How would I summraize transcations and enter in master quiery. Let's say I made query based on Project Master code and coloum Total purchase need to be calculated from transcation table.

Would you please give me a clue how to do it.

Thanks

mithani

 

[mithani]

Hi neileg,

See the attached shot. At the end is a report which I need.

thanks for your help.

mithani

 

[neileg]

Without all your structure I can't be certain, but I supect you need to create a totals query based on your transaction table. Then join this query in another query with the data from your project table. Don't try to do all the sorting and grouping in the query, use the report options to do this.

 

[mithani]

Thank Neileg,

Lets assume I have table Master Code Cost with Expense code & totalpurchase fields. I am running a query based on master code cost and transaction(for totapurchase) table. Now what I am looking for is, when I run query, should get one line of each code cost. like

Code Cost, Purchase
1001, $15,546.00
1002, $25,769.00

While creating query, how can I sum total purchase from transaction table and show in totalpurchase column.

Thanks a lot

mithani

 

[neileg]

Please give me the structure of your tables. You aren't making things very clear.

 

[mithani]

Hi Neileg,

Please see attached four tables :

tblInvoiceParent & tblInvoiceChild (Puchase transactions)
Project (name of projects)
Project_cost (project budget detail)

I have made one query "master Query".

What I need in my Master Query,

All detail from table "Project_cost"
All purchase should be summraized(all purchase should be sum as per EXPENSES_CODE & PROJECT CODE) in totalpurchase column in master query.

Please have a look and let me know if anything is not clear.

Thanks

mithani

 

[mithani]

hi neileg,

Please help me with my query.

Thanks a lot

mithani

 

[neileg]

Sorry, I don't work with A2007. Can you set out your structure, or repost in A2003 format.

 

[gemma-the-husky]

create a query from your table, then change it to a totals query.Maybe a sigma icon, otherwise use your help

then play with changing groupby, and sum, ans count and things like that to see the effect it has.

then join the totals query to your main table ...
or prepare a subform from it and link it to a form prepared from the main table .....

 

[mithani]

Thanks neileg

Please see attached database in 2003 format.

mithani

 

[mithani]

Thank femma,

working great!

Thanks neileg.

mithani

 

[mithani]

sorry, i mean thanks gemma

 

[neileg]

Happy to help.