How to do some serious calculation on a query?

[0200050]

Good evening there!

My question may not sound simple. I've been thinking this situation I'm having with my prog all day long. Well, anyway, let's get onto it.

First, this is how program should function:

"User types two dates to a form"
- A query receives to dates, and determines the right records to show.
"User selects a product for the main records from another form"
- Another query receives the product, and now it knows what product to show to user.
*BLING! A report opens up.
"User brows(watches) data and prints if its correct(should be)"

Let's have a look at now at the report starting point:

This kinda query I have right now:

SparePart   NumberOfSpareParts    FixingDay   Product   

Screw       500                   1.9.2004    Table
Hammer      12                    2.9.2004    Chair
Saw         3                     3.9.2004    Shelf
Hammer      15                    4.9.2004    Chair
Saw         4                     5.9.2004    Shelf
Screw       350                   6.9.2004    Table

This is how it looks out. It is just a basic query, not even hand-made one. Just used wizards.

Maybe no matter how it is made up, but how it works. Looong thinking. You see there those dual things, screw doubled, saw doubled, and hammer so called "doubled" as well.

And because this date thing, FixingDay, is over there, it works as a "little" identifier, right?(It doesnt really calculate the values as those Numbers, am I correct?)

Now, my purpose would be to create a report from this information. Problems occurs in the output. I have one big report, that contains several subreports. Now I'd like to see that information at the one of these subreports like this:

SparePart   NumberOfSpareParts   Product

Screw       850                  Table
Hammer      27                   Chair
Saw         7                    Shelf

I haven't had no luck or skill to create the view like the above one. This one I'd like to put a report. It would be created from the first code block(1. query outlook example).

The problem here is that FixinDay, that is now missing at this one above, but with a purpose.

I use that FixinDay as a Child Link from this small report to the bigger report of mine.

And THIS one may be the problem. You see, I'm trying to calculate values, as above, at my small report, and at the same time, smaller one is connected to bigger one using that FixinDay.

It is essential, because user sorts the information by typing dates on a form(maybe that's already another story).

Now I'm having a subreport, that shows the information like this:

SparePart   NumberOfSpareParts       Product   

Screw       500                      Table
Hammer      12                       Chair
Saw         3                        Shelf
Hammer      15                       Chair
Saw         4                        Shelf
Screw       350                      Table

And now to the question that has keep my day so busy today: "How can I calculate the values at a report or query like I demonstrated above?"

Products have different values, and it depends on what kind of interval user typed at the first screen(user types dates to a form, selects product, clicks, report opens).

I hope someone gets even a minor clear of what I'm trying to implement.

This is all about calculating when still using Dates at the same query. And I'm thinking I'm unable to use Max or Min -values either, because I need(probably?) all the date data.

By the way, If you got my point, do you think this is possible at all?

Every answer and comment to this thread is extremely highly appreciated!

Greetings from 020050

 

[PHV]

Why not using an aggregate query ?
SELECT SparePart,Sum(NumberOfSpareParts) As NumberOfSpareParts,Product
FROM yourQuery1
GROUP BY SparePart,Product

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[0200050]

Yeah, tried to use it before already...

The fact is, When we use the date there with it, There comes too many amounts. And these amounts I'd like to calculate at a query ready for a report.

Sparepart  Amount   Product    FixinDay

Saw         3       Chair      1.9.2004
Saw         4       Chair      2.9.2004
Saw         5       Chair      3.9.2004  

So, this kinda it is..

But you know, I should have to calculate those amounts, and then place them to a report.

This kinda it should SOMEHOW look:


Sparepart  Amount    Product    FixingDay

Saw        12        Chair      ???? But what i'm 
                                goin to do with this one?

I assume very strongly(been tested it) the fixingday should be there, because it connects the subreport to the main one.

Hmm. Wait. How do you think, is this possible to put a working implementation of this somehow?

Best regards,
0200050

 

[m1kee]

Hi,

If you need the FixingDay data what benefit do you get from grouping them query by sparepart?

If the date connects to the sub-report, how do you do it, individual days?

Regards, Mike.

Do or do not, there is no try. - Yoda

 

[0200050]

Hi there,

Yes, I'm using individual days.. User needs to select an interval, so if there is no individual days, this won't work nicely(as I see it).

And the benefit of this all, what I'm driving after... That's ONLY an outlook issue, but let me show:

Product     ScrewDriver
Model       DA-10PI

SpareParts  Amount

Screw       1
Screw       1
Screw       1
Screw       1
Screw       1
Screw       1

Really cool huh? Those Screws should only be summed together...But it seems a way too difficult to me to do, just because it connects to the main report with that Date(FixingDay).

Oh, now I got ya. You meant by sparepart! Yeah, well, I have to show those spareparts on a report, so I guess I need those also there(?)

Has someone ever tried something similar before? If has, I'd really pleased to hear all detail of it. Other suggestions are also at least as much welcome!

Best Regards,
0200050