Build Crosstab query from existing query

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I have a query that displays Balance Due for veterinary doctors. The query
produces this:

Vet Name Pet Type Last Visit Balance Due
Peterson bird 11/5/2099 $0.00
Peterson dog 11/5/2008 $101.00
Kelly cat 11/5/2009 $65.00
Kelly dog 7/1/2008 $19.00
Kelly iguana 11/12/2008 $44.00
Kelly dog 7/21/2008 $0.00
Scott snake 11/29/2006 $0.00
Scott bird 11/5/2099 $62.00

I need to write a crosstab query to re-arrange the data like this:

Vet Name Pet Type Last Visit Balance Due Balance Due Balance Due BalanceDue
Peterson bird 11/5/2099 $0.00
Peterson dog 11/5/2008 $101.00
Kelly cat 11/5/2009 $65.00
Kelly dog 7/1/2008 $19.00
Kelly iguana 11/12/2008 $44.00
Kelly dog 7/21/2008 $0.00
Scott snake 11/29/2006 $0.00
Scott bird 11/5/2099 $62.00

I am a beginner at this and any help would be appreciated.

Thanks,
 
How can you deal with multiple columns having the same name? In the desired
output, you have [Balance Due] four times. And why four times?

Vanderghast, Access MVP
 
Based on your data a crosstab query will not produce an output like --
Vet Name Pet Type Last Visit Balance Due Balance Due Balance Due BalanceDue

You show 4 outputs named 'Balance Due' and Access cannot do this. At best it
can produce Balance Due 1 Balance Due 2 Balance Due 3 Balance Due 4 for
your. You can add additional output field in your present query to do the
same.

But you have no data for more than one Balance Due. What would you use to
distinguish between them?
 
In the original table, the Balance Due is one column. I have been asked to
produce a query that will put Balance Due in the heading and the spread the
Balance out in separate columns by Vet Name.

Is that possible?
--
Mike


vanderghast said:
How can you deal with multiple columns having the same name? In the desired
output, you have [Balance Due] four times. And why four times?

Vanderghast, Access MVP


Mike said:
I have a query that displays Balance Due for veterinary doctors. The query
produces this:

Vet Name Pet Type Last Visit Balance Due
Peterson bird 11/5/2099 $0.00
Peterson dog 11/5/2008 $101.00
Kelly cat 11/5/2009 $65.00
Kelly dog 7/1/2008 $19.00
Kelly iguana 11/12/2008 $44.00
Kelly dog 7/21/2008 $0.00
Scott snake 11/29/2006 $0.00
Scott bird 11/5/2099 $62.00

I need to write a crosstab query to re-arrange the data like this:

Vet Name Pet Type Last Visit Balance Due Balance Due Balance Due
BalanceDue
Peterson bird 11/5/2099 $0.00
Peterson dog 11/5/2008 $101.00
Kelly cat 11/5/2009 $65.00
Kelly dog 7/1/2008 $19.00
Kelly iguana 11/12/2008 $44.00
Kelly dog 7/21/2008 $0.00
Scott snake 11/29/2006 $0.00
Scott bird 11/5/2099 $62.00

I am a beginner at this and any help would be appreciated.

Thanks,
Click to expand...

.
Click to expand...
 
This maybe one of those requirements I will need to fight back on. The
original query gives the user the information they need but they would like
to have a different format of the output. Let try what you suggested.


Thanks,
 
Maybe you want this --
Balance Due 1: [Balance Due] - (([Balance Due] \4) *3)
Balance Due 2: [Balance Due] \4
Balance Due 3: [Balance Due] \4
Balance Due 4: [Balance Due] \4
This splits balance into four payments with first being the largest and rest
at even dollars.

--
Build a little, test a little.


Mike said:
In the original table, the Balance Due is one column. I have been asked to
produce a query that will put Balance Due in the heading and the spread the
Balance out in separate columns by Vet Name.

Is that possible?
--
Mike


vanderghast said:
How can you deal with multiple columns having the same name? In the desired
output, you have [Balance Due] four times. And why four times?

Vanderghast, Access MVP


Mike said:
I have a query that displays Balance Due for veterinary doctors. The query
produces this:

Vet Name Pet Type Last Visit Balance Due
Peterson bird 11/5/2099 $0.00
Peterson dog 11/5/2008 $101.00
Kelly cat 11/5/2009 $65.00
Kelly dog 7/1/2008 $19.00
Kelly iguana 11/12/2008 $44.00
Kelly dog 7/21/2008 $0.00
Scott snake 11/29/2006 $0.00
Scott bird 11/5/2099 $62.00

I need to write a crosstab query to re-arrange the data like this:

Vet Name Pet Type Last Visit Balance Due Balance Due Balance Due
BalanceDue
Peterson bird 11/5/2099 $0.00
Peterson dog 11/5/2008 $101.00
Kelly cat 11/5/2009 $65.00
Kelly dog 7/1/2008 $19.00
Kelly iguana 11/12/2008 $44.00
Kelly dog 7/21/2008 $0.00
Scott snake 11/29/2006 $0.00
Scott bird 11/5/2099 $62.00

I am a beginner at this and any help would be appreciated.

Thanks,
Click to expand...

.
Click to expand...
Click to expand...
 
Back
Top