Calculate special items (1 Viewer)

[Marziya]

Hi everyone
I have a invoice form that I enter the summary of cost and the item category that is spent for and also the date of the invoice.
I want to calculate that in each date and for which category how much money spend?
at first I wanted to do this through query but the there are multiple situation that may be occur.
I don't know, how is it possible?!
this is my invoice structure:
InvoiceNo
InvoiceDate
Customer
ServiceCategory ("Service";"Stuff";"Both Option")
Cost
Is there anybody help me on this case?
thanks in advance
Marziya

 

[Uncle Gizmo]

Remove all confidential data and post a copy of your dB.

 

[Marziya]

Remove all confidential data and post a copy of your dB.

I hardly explain what is my mean because my native language is not english and my db info isn't in english by the way I upload it here my database.

 

[Pat Hartman]

Select InvoiceDate, ServiceCategory, Sum(Cost) as TotCost
From YourTable
Group by InvoiceDate, ServiceCategory;

The query needs to select ONLY the fields you want to group by and the fields you want to aggregate.


Here are two more variations to help you to understand how to create total queries yourself. You can start with a select query and then press the Sigma button to convert it to a Totals query. That adds group by to each selected field. You then go through and change Group by to Sum or Max or Avg or whatever you want. Just remember, the Group by fields can ONLY be the fields you want to summarize by. for example, if you include the InvoiceNumber, you will get NO summarization at all.

Select Customer, ServiceCategory, Sum(Cost) as TotCost
From YourTable
Group by Customer, ServiceCategory;

Select InvoiceDate, ServiceCategory, Max(Cost) as MaxCost
From YourTable
Group by Invoice, ServiceCategory;

 

[mike60smart]

I hardly explain what is my mean because my native language is not english and my db info isn't in english by the way I upload it here my database.

Hi

I only took a quick look at your database but there are numerous things wrong.

The Main problem is the structure of your tblProjectInfo

You have fields SubRepairer1 through to 9 , Phone1 through to 9, TypeCol1 through to 9

This is known as a Repeating Group.

The fields should be Records in a Related Table

None of your tables have an Autonumber Primary Key with Long Integer as the DataType.

You are using Hard Coded Lists of Values at table level.

 

[Uncle Gizmo]

You have fields SubRepairer1 through to 9 , Phone1 through to 9, TypeCol1 through to 9

From viewing Mike's comment, I believe you could make good use of my Normalisation Tool. The Tool will take the information from the fields and put them in a table. Full instructions on my website here:-

Normalization Tool - Nifty Access

Normalization Tool Introducing the "Transpose Tool" or "Database Normalization Tool," your go-to solution for seamlessly converting spreadsheet-structured data into MS Access-structured data. This transformation not only equips you to construct exceptionally powerful MS Access databases, but is...

www.niftyaccess.com

A free version of the "Normalisation Tool" is available to Access World Forum (AWF) members.
To get it, send me a private message within this forum and I will send you a coupon code to get a free download....

In the video below I demonstrate how to transpose data in the form of Fields representing a year into to a table containing the year and other relevant information. Before the transposition of the data, it would have been practically impossible to build the report.

Transpose YEAR:-

 

[Marziya]

Hi

I only took a quick look at your database but there are numerous things wrong.

The Main problem is the structure of your tblProjectInfo

You have fields SubRepairer1 through to 9 , Phone1 through to 9, TypeCol1 through to 9

This is known as a Repeating Group.

The fields should be Records in a Related Table

None of your tables have an Autonumber Primary Key with Long Integer as the DataType.

You are using Hard Coded Lists of Values at table level.

Actually I am new to access and I am not really professional in setting data in correct category.
Is it possible to say how should I sort data that works good?

 

[Marziya]

From viewing Mike's comment, I believe you could make good use of my Normalisation Tool. The Tool will take the information from the fields and put them in a table. Full instructions on my website here:-

A free version of the "Normalisation Tool" is available to Access World Forum (AWF) members.
To get it, send me a private message within this forum and I will send you a coupon code to get a free download....

In the video below I demonstrate how to transpose data in the form of Fields representing a year into to a table containing the year and other relevant information. Before the transposition of the data, it would have have been practically impossible to build the report.

thank you

 

[Marziya]

Hi

I only took a quick look at your database but there are numerous things wrong.

The Main problem is the structure of your tblProjectInfo

You have fields SubRepairer1 through to 9 , Phone1 through to 9, TypeCol1 through to 9

This is known as a Repeating Group.

The fields should be Records in a Related Table

None of your tables have an Autonumber Primary Key with Long Integer as the DataType.

You are using Hard Coded Lists of Values at table level.

in every project that we may have we have several repairer that may be differ within projects.
we should save their record that's why I categorize like this.
maybe in one project we have 3 repairer and the other 5 or 6 .
I put 9 possibilities in my database.

 

[mike60smart]

Can you explain in more detail your daily buisiness process

 

[Marziya]

Can you explain in more detail your daily buisiness process

I want to design a database for a company that is in heavy machine overhaul and repairing.
they want to keep and save the data of :

• cost of every parts of machine that they spend in category like cost of 1.motive force system 2.hydrualic system and etc.
• machine that their guaranty is started. and management of guaranty period like how much we paid during this time? why we paid? and etc.
• the delay that may be occur during project. why is it happened? when is it happened? the delay caused by who?
• for every project we must have some field that keep and save the information that is vital. I mean we want to save our lesson learned from every project.
• about the cost that I said before , we want to save the invoices that we receive from our repairer our for equipment that we buy.
• there should be a place that store information of contracts that we have with others.
• and at last we have some photo from before and after of repairing.

this is the whole structure that we want to build.

 

[mike60smart]

Hi

So you want to record details as follows:-

1. for each piece of equipment that you purchase.
2. duration of Guarantee
3. who carried out any repairs on the equipment together with details of delays in repairing.
4. cost of all repairs
5. Contract details

Can you please explain a Contract in detail.

 

[Marziya]

Hi

So you want to record details as follows:-

1. for each piece of equipment that you purchase.
2. duration of Guarantee
3. who carried out any repairs on the equipment together with details of delays in repairing.
4. cost of all repairs
5. Contract details

Can you please explain a Contract in detail.

Hi

yes I want to record almost all you have mentioned.

the items that record in the contract are :
duration of repairing
the cost of repairing
the parts that we want to repair
the guarantee service that we give them about duration of guarantee that if some parts don't work properly we service them without pay any money.

 

[mike60smart]

But what does a Contract mean? Please explain

 

[Marziya]

But what does a Contract mean? Please explain

Actually I did not understand what you mean.

 

[mike60smart]

You said in your list of requirements the following:-

• there should be a place that store information of contracts that we have with others.

Can you explain??

Also, when you say a Project, are you dealing with Projects where you purchase specific equipment and the maintenance of that equipment.

I need a more detailed explanation of your business.

 

[Marziya]

You said in your list of requirements the following:-

• there should be a place that store information of contracts that we have with others.

Can you explain??

Also, when you say a Project, are you dealing with Projects where you purchase specific equipment and the maintenance of that equipment.

I need a more detailed explanation of your business.

OK let me explain what I understand

we have some mechanics that repair engine and and gearbox also and some technician that works in hydraulic parts but for the other parts of machine we don't have any specialist that work so we outsource those parts to other mechanics that aren't in our team.

our project is about overhaul of heavy machine that doesn't work properly and those that aged.
we repair some parts by our team members and some parts we outsource.
and we have one contract with employer
and some contracts with mechanics that aren't in our team.

 

[mike60smart]

So do these Heavy Machines belong to your Company or do they belong to specific Customers.

 

[Marziya]

So do these Heavy Machines belong to your Company or do they belong to specific Customers

they belong to specific customers.

 

[mike60smart]

Does the Guarantee apply to specific parts that you fit?