Multiple Rows in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have 2 queries.

My first query shows all jobs done in a time period. 39000 rows
My second query has jobs numbers with appliance types.

I want to show the 39000 rows with an appliance, but when I combine them
together it jumps to 54000 rows because in query 2 there are multiple
appliance per job number. How can I only show 1 Appliance per job when they
are joined together in my query.

I want to show all jobs done 39000 with an appliance next to them?

Thanks,
Jez
 
Dear Jez:

There are two steps to do this.

First, decide how, when there is more than one appliance, which one you want
it to display. Do you want to show the Appliance that comes first
alphabetically? Won't work if there are 2 applicance spelled identically.
It's not unique in that case. Is there a Date column in the Appliances
table? You could choose the earliest, or latest date. Same potential
problem: what if there are two on the same date.

The best way to do this is by design. Don't let two appliances be
associated with the same job on the same date (if that's reasonable). Don't
let the same Appliance be associated with a job more than once (again, if
that's reasonable).

Or, you could ask yourself, what is the good of showing one applicance, and
not all of them? How does that make sense? Maybe show how many applicances
are associated with that job, not which applicances they are.

What you cannot do is expect the query to arbitrarily choose one for you and
show only that one. Arbitrary is not so possible.

The second step will be to code it. That can be done after these details
are cleared up.

Tom Ellison
 
My guess is the business model would not support these kinds of rules. Would
it not just be a matter of making it a Totals query and for the Appliance
field, Use First or Max instead of Group By?
 
Back
Top