Query for totals

[Jill Waters via AccessMonster.com]

Hi.
I have an order form with 2 subforms - one for products type a and one for
products type b. Type a and b have different backend tables.
I don't always have boths of products on each order so when I try to do a
standard form that has a break down like:
order # totala totalb grand total

i only get results for the orders that have a and b.
I can't combine the tables because of other parts of the application.
is there any way to do a query that will give me all order numbers and plug
in the totals for a and b where they exist?
a look up or something. i'm trying to print that report out.

thanks

 

[Marshall Barton]

I have an order form with 2 subforms - one for products type a and one for
products type b. Type a and b have different backend tables.
I don't always have boths of products on each order so when I try to do a
standard form that has a break down like:
order # totala totalb grand total

i only get results for the orders that have a and b.
I can't combine the tables because of other parts of the application.
is there any way to do a query that will give me all order numbers and plug
in the totals for a and b where they exist?
a look up or something. i'm trying to print that report out.

I don't think there is a query that merges two unrelated
sets of records in the way you want.

For your report, you should do the same kind of thing you
did for the form, use two subreports. The main report would
have the Order#, etc and each subreport would have the type
data. The Order# would be the Link Master/Child properties
for bot subreports (just like the form/subforms).

Once you get that part working, come on back if you need
help getting the grand total on the main report.

 

[Jill Waters via AccessMonster.com]

thank you.
i'll try that. i was trying to create a summary of the orders. it didn't
occur to me to do subreports.
i'll be back for that extra assistance.

 

[Jill Waters via AccessMonster.com]

the other thing i tried to do was create a record for both typea and typeb
for every order number but i couldn't get that to work either.
if i had a record for every order # my other query would work.
it wouldn't matter if it was just zeros but it won't save it unless i go
into the form and tab thru. that's time consuming every time.
even when i tried tabbing thru with zeros it wouldn't save. the only time
it would save was if i made an entry and then changed it to zero.

any ideas here?

 

[Marshall Barton]

the other thing i tried to do was create a record for both typea and typeb
for every order number but i couldn't get that to work either.
if i had a record for every order # my other query would work.
it wouldn't matter if it was just zeros but it won't save it unless i go
into the form and tab thru. that's time consuming every time.
even when i tried tabbing thru with zeros it wouldn't save. the only time
it would save was if i made an entry and then changed it to zero.

That's a waste of time. The two types are unrelated, so
don't try to fudge things to make some kind of artificial
alignment.

This is analogous to having a table of club members and two
"sub" tables. One for each member's children and another
for their phone numbers. It just doesn't make any sense to
try to have as many children records as phone records.

 

[Jill Waters via AccessMonster.com]

Well, they're not completely unrelated. I believe you on the waste of time -
and space.
Is it just that I won't be able to do a report that looks like:

orderid customer address shipping_cost producta productb total
1 abc xxx $5.00 $10.00 $0.00 $15.00
2 def xxx $25.00 $ 0.00 $9.00 $34.00
3 abc xxx $5.00 $25.00 $0.00 $25.00
4 abc xxx $15.00 $10.00 $5.00 $30.00

 

[Jill Waters via AccessMonster.com]

Marshall,
the subreport is working perfectly.
i don't know what i did wrong before.
i'm trying to figure out those totals now.
got the 2 sub reports and they only display a value when they have one!
thanks so much!!

 

[Marshall Barton]

the subreport is working perfectly.
i don't know what i did wrong before.
i'm trying to figure out those totals now.
got the 2 sub reports and they only display a value when they have one!

A main report text box can retrieve a value from subreport's
text box in the subreport's Footer (or Header) section by
using this kind of reference:

=subreport.REPORT.textbox

but if the subreport might not have any data (and therefore
no value in the footer text box, then use:

=IIf(subreport.REPORT.HasData, subreport.REPORT.textbox, 0)

So, your total text box on the main report would use
something like:

=Shipping + IIf(subreport1.REPORT.HasData,
subreport1.REPORT.textbox1, 0) +
IIf(subreport2.REPORT.HasData, subreport2.REPORT.textbox2,
0)

 

[Jill Waters via AccessMonster.com]

Can this work in the detail?
I have it totalling when there is data. But as you guessed, if there is
none - #error.
I'm trying to create
A [subrpt] B [subrpt] C [total of A+B if they both have values]
A [subrpt] B [subrpt] C [total of A+B if they both have values]
A [subrpt] B [subrpt] C [total of A+B if they both have values]

 

[Jill Waters via AccessMonster.com]

this works perfectly.
thank you so much!
you've made my entire month!