Can't get this right :-(

[RossG]

I am having problems with what I think should be an easy query...can someone offer advice please?

I have a database logging faults in numerous markets of our operation. Details of each fault includes the date and a yes/no field for each market indicating if it was affected by the fault. By the nature of our operation a fault may affect more than one market.

What I need to do is generate weekly and monthly reports on the number of faults affecting each market on a daily basis, ie sum the total of 'yes' fields for each market on each day.

I can happily run a query to sum just one market....but try to add other markets and it falls apart.I think this is because not every market has a fault every day. There may be days where there are no faults at all (we like that kind of day )

Could anyone post ideas on how to build this query ??

 

[LdEckersley]

How I would go about this is to first of all do a simple select query, add all fields to the query and then in the Yes/No column put criteria Yes, this will give you all the markets that have yes to being affected.

Create another query using the Query above for the fields and add the totals (View - Totals) put group by Market, Group by Date, and in the Yes/No column select Count.

This will give you the number of Yes's for each market on each date,

 

[crosmill]

In the query builder are you putting the filter criteria on a different line, ie making it an OR query.

So
line 1 Date = x market1 = yes
line 2 Date = x market2 = yes

the SQL would be similar to WHERE Date = "x" AND market1 = "yes" OR Date = "x" AND market2 = "yes"

hope this helps

 

[The_Doc_Man]

Well,....

Technically (and forgive me for the implied criticism) this is not a good structure.

You need two tables to do this right.

Table 1 - define the markets, including perhaps a market ID.

Table 2 - define the faults based on market ID and the date and possible an autonumber field as the prime key. (You didn't say whether more than one fault could occur for the same market in a single day.) Plus any details about the fault being reported.

Now, your report is based on a JOIN of the market table to the fault table where the Market ID fields are equal. You can use this query as the basis for tons of reports. And the report wizard allows you to do groupings and summary counts as simple check-boxes in the query builders. So you can do a break on each market if you wish.

This can also give you an opportunity to do a standard crosstab query based on the aforementioned JOIN query, since your options are now Market, date, and count of faults. Three fields, which is the minimum requirement for crosstabs. Crosstab queries also allow your grouping by date to be by week or month or day or year or calendar quarter, so you should be able to just select the grouping interval from a drop-down box while the crosstab wizard is at work.

You don't CHECK anything when a fault comes in. You just select the market ID, maybe even from a combo box. The date can be defaulted. If there is a fault description, you can just enter it. The form for this is simple enough to be built from a form wizard plus a little bit of constructive lying. Let the wizard build the fault table form. Then enter design mode. Delete the text box with the market information in it. Create a new combo box and let the combo box wizard build it for you.