Preventing a report from being blank when one field is null

[briggal1]

Hi

I wonder if anyone can help.

I have a report in Access 2000, which works perfectly and displays all the information until I select a period where one, or more, of the fields is blank.

Example 1:

I run the report and select data from 05/04/06 to 04/04/08 and get the report no problem.

Example 2:

I run the report and select data from 05/04/07 to 04/04/08 and the whole report in blank.

I think I have traced the problem to the fact that the "Subscripti on" field is blank because I have not paid my yearly subscription yet.

To get around this I entered a record for "Subscripti on" and now the report works OK in example 2.

Unfortunately I now need to run the report for a certain three-month period but again the report is blank because "Subscripti on" and maybe some other fields do not have any records for the period I am interested in.

I am presuming that there is something simple that I need to do but don't know what.

I would appreciate it if someone could tell me how to deal with this.

Thanks in anticipation

briggal1

[Jim Doherty]

Hi

I wonder if anyone can help.

I have a report in Access 2000, which works perfectly and displays all the information until I select a period where one, or more, of the fields is blank.

Example 1:

I run the report and select data from 05/04/06 to 04/04/08 and get the report no problem.

Example 2:

I run the report and select data from 05/04/07 to 04/04/08 and the whole report in blank.

I think I have traced the problem to the fact that the "Subscripti on" field is blank because I have not paid my yearly subscription yet.

To get around this I entered a record for "Subscripti on" and now the report works OK in example 2.

Unfortunately I now need to run the report for a certain three-month period but again the report is blank because "Subscripti on" and maybe some other fields do not have any records for the period I am interested in.

I am presuming that there is something simple that I need to do but don't know what.

I would appreciate it if someone could tell me how to deal with this.

Thanks in anticipation

briggal1

Instinct tells me that you may have an INNER JOIN between tables which should be a LEFT join in your SQL but this is a guess without having the structure. Post your SQL statement

Regards

Jim

[briggal1]

Instinct tells me that you may have an INNER JOIN between tables which should be a LEFT join in your SQL but this is a guess without having the structure. Post your SQL statement

Regards

Jim

Thanks Jim

I don't have any joins. The query under the report just bring together 13 other queries, the SQL is:

SELECT qryIncomeFromEb ay.SumOfAmount, qryIncomeFromMa rkets.SumOfAmou nt, qryIncomrFromMi sc.SumOfAmount, qryIncomeFromIn ternet.SumOfAmo unt, qryOutletExpend iture.SumOfCost , qryVehicleCosts ByPeriod.SumOfP rice, qrySubscription sCostsByPeriod. SumOfPrice, qryEbayPayPalFe es.SumOfPrice, qryStockCostByP eriod.SumOfPric e, qrySundriesCost sByPeriod.SumOf Price, qryTotalPostage ByPeriod.TotalP ostage, qryTelephoneByP eriod.SumOfPric e, qryAdvertisinfB yPeriod.SumOfPr ice
FROM qryIncomeFromEb ay, qryIncomeFromMa rkets, qryIncomrFromMi sc, qryOutletExpend iture, qryVehicleCosts ByPeriod, qrySubscription sCostsByPeriod, qryEbayPayPalFe es, qryStockCostByP eriod, qrySundriesCost sByPeriod, qryTotalPostage ByPeriod, qryTelephoneByP eriod, qryAdvertisinfB yPeriod, qryIncomeFromIn ternet;

Each of the underlying queries are basically all the same as the one below:

SELECT qryMoneyOutByPe riod.Category, Sum(qryMoneyOut ByPeriod.Price) AS SumOfPrice
FROM qryMoneyOutByPe riod
GROUP BY qryMoneyOutByPe riod.Category
HAVING (((qryMoneyOutB yPeriod.Categor y)="Stock"));

And they all start with this 1 of 3 queries where you can enter the start and end dates:

SELECT tblMoneyOut.Mon eyOutID, tblMoneyOut.Dat e, tblMoneyOut.Eba y, tblMoneyOut.Cat egory, tblMoneyOut.Sup plier, tblMoneyOut.Ite mNumber, tblMoneyOut.Tit le, tblMoneyOut.Pri ce, tblMoneyOut.Pos tage, tblMoneyOut.Pay mentMethod, tblMoneyOut.Col lected, tblMoneyOut.Com ment
FROM tblMoneyOut
WHERE (((tblMoneyOut. Date) Between [Start of currect tax year (05/04/??)] And [End of current tax year (04/04/??)]))
ORDER BY tblMoneyOut.Mon eyOutID;

The first 3 queries are Money-in, Money-out & Outlets. These just bring in all fields from the underlying tables but allow you to select a date range.

The next layer of queries picks out certain data such as outlet expenditure, postage, stock costs etc.

The final query just has brings together all the previous queries so they can all be displayed on the same report.

Does this help?

Thanks

Allan

[Jim Doherty]

Thanks Jim

I don't have any joins. The query under the report just bring together 13 other queries, the SQL is:

SELECT qryIncomeFromEb ay.SumOfAmount, qryIncomeFromMa rkets.SumOfAmou nt, qryIncomrFromMi sc.SumOfAmount, qryIncomeFromIn ternet.SumOfAmo unt, qryOutletExpend iture.SumOfCost , qryVehicleCosts ByPeriod.SumOfP rice, qrySubscription sCostsByPeriod. SumOfPrice, qryEbayPayPalFe es.SumOfPrice, qryStockCostByP eriod.SumOfPric e, qrySundriesCost sByPeriod.SumOf Price, qryTotalPostage ByPeriod.TotalP ostage, qryTelephoneByP eriod.SumOfPric e, qryAdvertisinfB yPeriod.SumOfPr ice
FROM qryIncomeFromEb ay, qryIncomeFromMa rkets, qryIncomrFromMi sc, qryOutletExpend iture, qryVehicleCosts ByPeriod, qrySubscription sCostsByPeriod, qryEbayPayPalFe es, qryStockCostByP eriod, qrySundriesCost sByPeriod, qryTotalPostage ByPeriod, qryTelephoneByP eriod, qryAdvertisinfB yPeriod, qryIncomeFromIn ternet;

Each of the underlying queries are basically all the same as the one below:

SELECT qryMoneyOutByPe riod.Category, Sum(qryMoneyOut ByPeriod.Price) AS SumOfPrice
FROM qryMoneyOutByPe riod
GROUP BY qryMoneyOutByPe riod.Category
HAVING (((qryMoneyOutB yPeriod.Categor y)="Stock"));

And they all start with this 1 of 3 queries where you can enter the start and end dates:

SELECT tblMoneyOut.Mon eyOutID, tblMoneyOut.Dat e, tblMoneyOut.Eba y, tblMoneyOut.Cat egory, tblMoneyOut.Sup plier, tblMoneyOut.Ite mNumber, tblMoneyOut.Tit le, tblMoneyOut.Pri ce, tblMoneyOut.Pos tage, tblMoneyOut.Pay mentMethod, tblMoneyOut.Col lected, tblMoneyOut.Com ment
FROM tblMoneyOut
WHERE (((tblMoneyOut. Date) Between [Start of currect tax year (05/04/??)] And [End of current tax year (04/04/??)]))
ORDER BY tblMoneyOut.Mon eyOutID;

The first 3 queries are Money-in, Money-out & Outlets. These just bring in all fields from the underlying tables but allow you to select a date range.

The next layer of queries picks out certain data such as outlet expenditure, postage, stock costs etc.

The final query just has brings together all the previous queries so they can all be displayed on the same report.

Does this help?

Thanks

Allan

Of sorts.. its going to take me a short while to replicate that this end recreating tables and the queries. Like Arnie once said...I,ll be back

Regards

Jim

[Jim Doherty]

Thanks Jim

I don't have any joins. The query under the report just bring together 13 other queries, the SQL is:

SELECT qryIncomeFromEb ay.SumOfAmount, qryIncomeFromMa rkets.SumOfAmou nt, qryIncomrFromMi sc.SumOfAmount, qryIncomeFromIn ternet.SumOfAmo unt, qryOutletExpend iture.SumOfCost , qryVehicleCosts ByPeriod.SumOfP rice, qrySubscription sCostsByPeriod. SumOfPrice, qryEbayPayPalFe es.SumOfPrice, qryStockCostByP eriod.SumOfPric e, qrySundriesCost sByPeriod.SumOf Price, qryTotalPostage ByPeriod.TotalP ostage, qryTelephoneByP eriod.SumOfPric e, qryAdvertisinfB yPeriod.SumOfPr ice
FROM qryIncomeFromEb ay, qryIncomeFromMa rkets, qryIncomrFromMi sc, qryOutletExpend iture, qryVehicleCosts ByPeriod, qrySubscription sCostsByPeriod, qryEbayPayPalFe es, qryStockCostByP eriod, qrySundriesCost sByPeriod, qryTotalPostage ByPeriod, qryTelephoneByP eriod, qryAdvertisinfB yPeriod, qryIncomeFromIn ternet;

Each of the underlying queries are basically all the same as the one below:

SELECT qryMoneyOutByPe riod.Category, Sum(qryMoneyOut ByPeriod.Price) AS SumOfPrice
FROM qryMoneyOutByPe riod
GROUP BY qryMoneyOutByPe riod.Category
HAVING (((qryMoneyOutB yPeriod.Categor y)="Stock"));

And they all start with this 1 of 3 queries where you can enter the start and end dates:

SELECT tblMoneyOut.Mon eyOutID, tblMoneyOut.Dat e, tblMoneyOut.Eba y, tblMoneyOut.Cat egory, tblMoneyOut.Sup plier, tblMoneyOut.Ite mNumber, tblMoneyOut.Tit le, tblMoneyOut.Pri ce, tblMoneyOut.Pos tage, tblMoneyOut.Pay mentMethod, tblMoneyOut.Col lected, tblMoneyOut.Com ment
FROM tblMoneyOut
WHERE (((tblMoneyOut. Date) Between [Start of currect tax year (05/04/??)] And [End of current tax year (04/04/??)]))
ORDER BY tblMoneyOut.Mon eyOutID;

The first 3 queries are Money-in, Money-out & Outlets. These just bring in all fields from the underlying tables but allow you to select a date range.

The next layer of queries picks out certain data such as outlet expenditure, postage, stock costs etc.

The final query just has brings together all the previous queries so they can all be displayed on the same report.

Does this help?

Thanks

Allan

Allan,

Ok!... you say TABLE(S) but the SQL post only relates to a source table of tblMoneyOut. I am working blind other than the info you provided.

I can visualise from the SQL your overall structure but it leaves me guessing the formal structure of your underlying queries particularly wth regard to the HAVING clause for instance ....Ebay is referred to .... I am guessing thats part of your having clause in the specific underlying query mentioned as it is a column in your table.

So.... the Ebay query in itself will be subtley different from the others in what it has to return.

The word 'basically' can mean my end the difference between you having correctly set up queries designed to properly return data or not. I'm sure you can understand me on that one :)

I originally thought that your problem might be related to perhaps an INNER JOIN in your SQL when it should be a LEFT JOIN? well the same fear is there and its kind of related in that having looked at your query syntax there is NO JOINS at all!... when you should have a LEFT JOIN

Now this...... is perfectly fine provided you have values that exist for each and every sub query contained within the overall query you will have a dataset returned summing each of the elements referred to.

But.....that is not what you want query wise!

If you have data for the period for Markets lets say and none for Ebay then just because Ebay has none you would still want the data back for Markets!

You have already identified this yourself in that you believe the actual issue will be with your Subscription records? (or lack of them) in other words no data for subscriptions for the period results in no data returned overall.

The problem is fundamentally concerned with the NO JOINs in your FROM clause section of the SQL in that, how shall we put it hmmm...the three musketeers principle "all for one and one for all?" (all must have data or no data at all) apply.... if you get me.

Your answer is to LEFT join with the original table which in the example is tblMoneyOut.

Save your overarching query as something else and work on that (until you get the syntax right and tested through) as what I am going to tell you is this....

The FROM clause of the overarching query (the one that brings everything together should have the source tables included in the top grey section of the Access query grid display window.

You then need to JOIN a line between the source table and its related query and then double click the line to bring up the JOIN properties box. You then need to select the option that says...

"Include All Records from tblMoneyOut and only those records from (whatever sub query you are working with ie qryIncomefrom Ebay) where the join fields are equal"

The net effect is this... the SQL examines and returns all records from the source table irrespective of whether there is a defining record within the related query and thus returns something even if it is a blank value (this way it becomes a displayable 'cell' if you like within your tabular dataset. If you are not sure on this then experiment with LEFT joins, INNER JOINS and RIGHT JOINS or research SQL help on it. To go into it within this thread would require another chapter of war and peace.

Here is a typical example of what you should be seeing in the SQL window

 
FROM (tblMoneyOut LEFT JOIN qryIncomeFromEbay ON tblMoneyOut.Category = qryIncomeFromEbay.Category) LEFT JOIN qryIncomeFromMarkets ON tblMoneyOut.Category = qryIncomeFromMarkets.Category
 

I cannot complete it for you! because it really demands that I see your entire structure. Remember I can only go on what you have provided.


I hope this helps you

Regards

Jim :)

[briggal1]

Allan,

Ok!... you say TABLE(S) but the SQL post only relates to a source table of tblMoneyOut. I am working blind other than the info you provided.

I can visualise from the SQL your overall structure but it leaves me guessing the formal structure of your underlying queries particularly wth regard to the HAVING clause for instance ....Ebay is referred to .... I am guessing thats part of your having clause in the specific underlying query mentioned as it is a column in your table.

So.... the Ebay query in itself will be subtley different from the others in what it has to return.

The word 'basically' can mean my end the difference between you having correctly set up queries designed to properly return data or not. I'm sure you can understand me on that one :)

I originally thought that your problem might be related to perhaps an INNER JOIN in your SQL when it should be a LEFT JOIN? well the same fear is there and its kind of related in that having looked at your query syntax there is NO JOINS at all!... when you should have a LEFT JOIN

Now this...... is perfectly fine provided you have values that exist for each and every sub query contained within the overall query you will have a dataset returned summing each of the elements referred to.

But.....that is not what you want query wise!

If you have data for the period for Markets lets say and none for Ebay then just because Ebay has none you would still want the data back for Markets!

You have already identified this yourself in that you believe the actual issue will be with your Subscription records? (or lack of them) in other words no data for subscriptions for the period results in no data returned overall.

The problem is fundamentally concerned with the NO JOINs in your FROM clause section of the SQL in that, how shall we put it hmmm...the three musketeers principle "all for one and one for all?" (all must have data or no data at all) apply.... if you get me.

Your answer is to LEFT join with the original table which in the example is tblMoneyOut.

Save your overarching query as something else and work on that (until you get the syntax right and tested through) as what I am going to tell you is this....

The FROM clause of the overarching query (the one that brings everything together should have the source tables included in the top grey section of the Access query grid display window.

You then need to JOIN a line between the source table and its related query and then double click the line to bring up the JOIN properties box. You then need to select the option that says...

"Include All Records from tblMoneyOut and only those records from (whatever sub query you are working with ie qryIncomefrom Ebay) where the join fields are equal"

The net effect is this... the SQL examines and returns all records from the source table irrespective of whether there is a defining record within the related query and thus returns something even if it is a blank value (this way it becomes a displayable 'cell' if you like within your tabular dataset. If you are not sure on this then experiment with LEFT joins, INNER JOINS and RIGHT JOINS or research SQL help on it. To go into it within this thread would require another chapter of war and peace.

Here is a typical example of what you should be seeing in the SQL window

 
FROM (tblMoneyOut LEFT JOIN qryIncomeFromEbay ON tblMoneyOut.Category = qryIncomeFromEbay.Category) LEFT JOIN qryIncomeFromMarkets ON tblMoneyOut.Category = qryIncomeFromMarkets.Category
 

I cannot complete it for you! because it really demands that I see your entire structure. Remember I can only go on what you have provided.


I hope this helps you

Regards

Jim :)

Jim

Thanks for that, I have tried what you suggested but I still get no results or it won't even run the query because the joins are wrong.

Maybe a little more explanation is called for.

My database consists of two tables, tblMoneyIn & tblMoneyOut.

tblMoneyIn records the money in from different sources such as markets, ebay, website etc.

tblMoneyOut records all money out such as stock purchases, vehicle expenses, sundries, subscriptions, etc.

The report I am producing is my Profit & Loss report and each category needs displaying separately as a total for the period. I then use calculated fields to total Money in and money out and the differnece between them.

I am doing this by first creating 2 queries each pulling all data from it's respective table within a given date range, see example:

qryMoneyOutByPe riod

...SELECT tblMoneyOut.MoneyOutID, tblMoneyOut.Date, tblMoneyOut.Ebay, tblMoneyOut.Category, tblMoneyOut.Supplier, tblMoneyOut.ItemNumber, tblMoneyOut.Title, tblMoneyOut.Price, tblMoneyOut.Postage, tblMoneyOut.PaymentMethod, tblMoneyOut.Collected, tblMoneyOut.Comment
FROM tblMoneyOut
WHERE (((tblMoneyOut.Date) Between [Start of currect tax year (05/04/??)] And [End of current tax year (04/04/??)]))
ORDER BY tblMoneyOut.MoneyOutID;...

Next I have created a number of queries to SUM each of the categories, see example of subscriptions by period:

qrySubscription sCostsByPeriod

...SELECT qryMoneyOutByPeriod.Category, Sum(qryMoneyOutByPeriod.Price) AS SumOfPrice
FROM qryMoneyOutByPeriod
GROUP BY qryMoneyOutByPeriod.Category
HAVING (((qryMoneyOutByPeriod.Category)="Subscription"));...

There are 13 of these queries in all.

I then use another query to bring all these 13 together, see example:

qryProfitAndLos sByPeriod
[CODE}...SELECT qryIncomeFromEb ay.SumOfAmount, qryIncomeFromMa rkets.SumOfAmou nt, qryIncomrFromMi sc.SumOfAmount, qryIncomeFromIn ternet.SumOfAmo unt, qryOutletExpend iture.SumOfCost , qryVehicleCosts ByPeriod.SumOfP rice, qrySubscription sCostsByPeriod. SumOfPrice, qryEbayPayPalFe es.SumOfPrice, qryStockCostByP eriod.SumOfPric e, qrySundriesCost sByPeriod.SumOf Price, qryTotalPostage ByPeriod.TotalP ostage, qryTelephoneByP eriod.SumOfPric e, qryAdvertisinfB yPeriod.SumOfPr ice
FROM qryIncomeFromEb ay, qryIncomeFromMa rkets, qryIncomrFromMi sc, qryOutletExpend iture, qryVehicleCosts ByPeriod, qrySubscription sCostsByPeriod, qryEbayPayPalFe es, qryStockCostByP eriod, qrySundriesCost sByPeriod, qryTotalPostage ByPeriod, qryTelephoneByP eriod, qryAdvertisinfB yPeriod, qryIncomeFromIn ternet;...[/code]

I then use the above query as the source for the report.

Does this help?

Thanks Allan

[Jim Doherty]

Jim

Thanks for that, I have tried what you suggested but I still get no results or it won't even run the query because the joins are wrong.

Maybe a little more explanation is called for.

My database consists of two tables, tblMoneyIn & tblMoneyOut.

tblMoneyIn records the money in from different sources such as markets, ebay, website etc.

tblMoneyOut records all money out such as stock purchases, vehicle expenses, sundries, subscriptions, etc.

The report I am producing is my Profit & Loss report and each category needs displaying separately as a total for the period. I then use calculated fields to total Money in and money out and the differnece between them.

I am doing this by first creating 2 queries each pulling all data from it's respective table within a given date range, see example:

qryMoneyOutByPe riod

...SELECT tblMoneyOut.MoneyOutID, tblMoneyOut.Date, tblMoneyOut.Ebay, tblMoneyOut.Category, tblMoneyOut.Supplier, tblMoneyOut.ItemNumber, tblMoneyOut.Title, tblMoneyOut.Price, tblMoneyOut.Postage, tblMoneyOut.PaymentMethod, tblMoneyOut.Collected, tblMoneyOut.Comment
FROM tblMoneyOut
WHERE (((tblMoneyOut.Date) Between [Start of currect tax year (05/04/??)] And [End of current tax year (04/04/??)]))
ORDER BY tblMoneyOut.MoneyOutID;...

Next I have created a number of queries to SUM each of the categories, see example of subscriptions by period:

qrySubscription sCostsByPeriod

...SELECT qryMoneyOutByPeriod.Category, Sum(qryMoneyOutByPeriod.Price) AS SumOfPrice
FROM qryMoneyOutByPeriod
GROUP BY qryMoneyOutByPeriod.Category
HAVING (((qryMoneyOutByPeriod.Category)="Subscription"));...

There are 13 of these queries in all.

I then use another query to bring all these 13 together, see example:

qryProfitAndLos sByPeriod
[CODE}...SELECT qryIncomeFromEb ay.SumOfAmount, qryIncomeFromMa rkets.SumOfAmou nt, qryIncomrFromMi sc.SumOfAmount, qryIncomeFromIn ternet.SumOfAmo unt, qryOutletExpend iture.SumOfCost , qryVehicleCosts ByPeriod.SumOfP rice, qrySubscription sCostsByPeriod. SumOfPrice, qryEbayPayPalFe es.SumOfPrice, qryStockCostByP eriod.SumOfPric e, qrySundriesCost sByPeriod.SumOf Price, qryTotalPostage ByPeriod.TotalP ostage, qryTelephoneByP eriod.SumOfPric e, qryAdvertisinfB yPeriod.SumOfPr ice
FROM qryIncomeFromEb ay, qryIncomeFromMa rkets, qryIncomrFromMi sc, qryOutletExpend iture, qryVehicleCosts ByPeriod, qrySubscription sCostsByPeriod, qryEbayPayPalFe es, qryStockCostByP eriod, qrySundriesCost sByPeriod, qryTotalPostage ByPeriod, qryTelephoneByP eriod, qryAdvertisinfB yPeriod, qryIncomeFromIn ternet;...[/code]

I then use the above query as the source for the report.

Does this help?

Thanks Allan

Alan,

Check your PM's please

Jim