Query of Sum of One field with different criteria

[hrdpgajjar]

Hi all,
I have attached here my sample database and a sample report in pdf format.
I need to create a query of Sum of "Area" field with the different criteria as under,

Dealer Wise sum of "Area" where,

Column no-1 : Sum of area where "WO Date' is blank (WO not received)
Column no-2 : Sum of area where "Material Supplied Date" is not blank (Material Supplied cases)
Column no-3 : Sum of area where "WO Date" is not blank (WO Received)
Column no-4 : Sum of area where "WO Date" is not blank and "TPA Date" is blank (WO Received and TPA Date pending)


I need this types of multiple combinations in my query to generate a report (I have also attached PDF Report copy for reference)

help is appreciated,

Thanks

- Hardip

 

[GaP42]

What query have you attempted using the data? Have you used the Query Design feature? The application of the criteria in Query Design would suggest 4 separate queries and corresponding reports based upon those queries.
The database as supplied shows no relationship defined between the tables - presumably it is based upon "Dealer Code". Have you tried this in your query?
@CJ_London suggested a database structure for your problem space in this post: How-to-make-a-form-to-enter-multiple-entries-in-table-with-same-form. - was that adopted/adapted to yours? The sample appears to be a sample generated as output from something like this, not the actual database upon which the required query would be built.

 

[CJ_London]

@CJ_London suggested a database structure for your problem space in this post

Don’t think that was me

 

[GaP42]

Apologies CJ - @LarryE was who I should have referred to

Column no-1 : Sum of area where "WO Date' is blank (WO not received)

You should at least start with a query to return these records - guessing but something like:

(use is null / is not null in criteria as needed)

That is then used as the basis for a totals query:

Repeat the above for each of the other criteria: each as a select query and then create a totals query based on each select query.

 

[LarryE]

Apologies CJ - @LarryE was who I should have referred to


You should at least start with a query to return these records - guessing but something like:

View attachment 117408
(use is null / is not null in criteria as needed)

That is then used as the basis for a totals query:

View attachment 117407
Repeat the above for each of the other criteria: each as a select query and then create a totals query based on each select query.

Don't blame me. The OP went back to keeping everything in one table. I gave him a normalized relational model, but he chose not to use it.

 

[DHookom]

I don't care much for your table structures and possible relationships with duplicate columns. Your report PDF doesn't seem to match the fields and data. However, you can create a column with the sum of Area and no WO Date with an expression like:

NoWODate: Abs(IsNull([WO Date]))*[Area]

The expression inside the Abs() is evaluated as either true/-1 or false 0. Multiply the Abs() * Area to get your numbers.

 

[GaP42]

Don't blame me. The OP went back to keeping everything in one table. I gave him a normalized relational model, but he chose not to use it.

Aaaegh! - the second part was meant to be directed to the OP.

 

[LarryE]

Aaaegh! - the second part was meant to be directed to the OP.

I know...just givin ya a hard time....

 

[hrdpgajjar]

Don't blame me. The OP went back to keeping everything in one table. I gave him a normalized relational model, but he chose not to use it.

I have started working on data normalization suggested by you, but during this i need to solve this problem too. So posted old data here.

 

[mike60smart]

I have started working on data normalization suggested by you, but during this i need to solve this problem too. So posted old data here.

One table for your process is pointless. Why did you not continue with the example of the normalised tables given?

 

[Mark_]

I have started working on data normalization suggested by you, but during this i need to solve this problem too. So posted old data here.

Normalize your data first.
Until you fix your data, any solution provided will be very temporary. Any solution will also depend on data you have not fixed, so may give wrong results.

AFTER you normalize your data, review it to make sure you don't have bad data. THEN you can generate accurate numbers.

 

[hrdpgajjar]

I don't care much for your table structures and possible relationships with duplicate columns. Your report PDF doesn't seem to match the fields and data. However, you can create a column with the sum of Area and no WO Date with an expression like:

NoWODate: Abs(IsNull([WO Date]))*[Area]

The expression inside the Abs() is evaluated as either true/-1 or false 0. Multiply the Abs() * Area to get your numbers.

This works most...Thanks

 

[LarryE]

This works most...Thanks

Are you multiplying a date and an area value number?

 

[DHookom]

Are you multiplying a date and an area value number?

As per Post #6, the Area is multiplied by either 1 or 0. The WO Date is used in an expression that returns true or false.

 

[hrdpgajjar]

Are you multiplying a date and an area value number?

Yes with Abs()