Reports

[Guest]

I had posted this message earlier, but didn't receive an answer, thought I
would try again. I have a Purchase Order database, within this data base I
have a form where you can input material that will be charged against a
paticular purchase order.

I am putting together a report that will show all purchase order balances
and the material charged against them. Where I am running into a problem is
when there is no material charged against a purchase order it does not show
up in report. The minute I charge material against it, boom it pops into
the report. If there is no material charged to the purchase order I
would like it to just populate the encumbered amount, so that it does indeed
reflect that purchase order in the report. I have tried "Null", but I
don't think that is the answer because back in the receiving ticket table
nothing exists because we have not purchased anything on the purchase order.
Any help would be greatly appreciated.

 

[Carma via AccessMonster.com]

I had posted this message earlier, but didn't receive an answer, thought I
would try again. I have a Purchase Order database, within this data base I
have a form where you can input material that will be charged against a
paticular purchase order.

I am putting together a report that will show all purchase order balances
and the material charged against them. Where I am running into a problem is
when there is no material charged against a purchase order it does not show
up in report. The minute I charge material against it, boom it pops into
the report. If there is no material charged to the purchase order I
would like it to just populate the encumbered amount, so that it does indeed
reflect that purchase order in the report. I have tried "Null", but I
don't think that is the answer because back in the receiving ticket table
nothing exists because we have not purchased anything on the purchase order.
Any help would be greatly appreciated.

Ok I'm assuming that your report is based upon a query where a table with the
PO #'s are linked with a table containing materials charged against the PO.
I'm also assuming that your join properties are 'only include rows where
joined fields are equal' This is what is normally selected, but if you
change it to 'include all rows from tblPO, and only those from tblmatertial
where the joined fields are equal.' should solve your problem. By doing
this all PO's will appear on report even if nothing has been charged against
them in your dbase. Hope this helps! Let me know if my assumptions are
wrong or if it doesn't work.

 

[Guest]

Thanks for the quick response, You are absolutely correct on your assumption.
I now have #2 marked in the join properties, include all records for
tblpurchaseorder and only those records from tblticketno where the joined
fields are equal......but will that work if I don't have a receiving ticket
in the tblticketno table because I have no data charged against that purchase
order? I am sure there is a way to make this happen---Access how "wowed"
me so far, but I am starting to think I should just create two reports, 1 for
purchase orders that have no material charged and 1 for purchase orders that
have material charged, any suggestions?

 

[Guest]

I received another error when I went to close the query I received this
message: The SQL statement could not be executed because it contains
ambiguous outer joins. To force one of the joins to be performed first,
create a separate query that performs the first join and then include that
query in your SQL statement. Thanks

 

[Guest]

Third time is the charm. I started all over, first did a query between the
tblpurchaseorder and tblticketno, choose #2 in the join properties, ran that
qry and it brought in the puchase orders with no material charged against it,
and then join two queries together and BINGO it worked. Thanks for getting
me thinking in the right direction.

 

[Carma via AccessMonster.com]

Hi Melinda,

Well I'm glad you finally got it to work and that I was some help at least.
Its often hard to perfectly understand the problem described without the
dbase in front of you. Good luck with the rest of it.

Third time is the charm. I started all over, first did a query between the
tblpurchaseorder and tblticketno, choose #2 in the join properties, ran that
qry and it brought in the puchase orders with no material charged against it,
and then join two queries together and BINGO it worked. Thanks for getting
me thinking in the right direction.

I had posted this message earlier, but didn't receive an answer, thought I
would try again. I have a Purchase Order database, within this data base I

[quoted text clipped - 11 lines]

nothing exists because we have not purchased anything on the purchase order.
Any help would be greatly appreciated.

 

[Carma via AccessMonster.com]

This is because you must have more than two tables in your query and one or
more of those tables were not joined to the other ones the same way as the
others.

I received another error when I went to close the query I received this
message: The SQL statement could not be executed because it contains
ambiguous outer joins. To force one of the joins to be performed first,
create a separate query that performs the first join and then include that
query in your SQL statement. Thanks
[quoted text clipped - 21 lines]

them in your dbase. Hope this helps! Let me know if my assumptions are
wrong or if it doesn't work.