Solved

Adding Null amounts into a Continous form query.

Posted on 2006-11-01
8
232 Views
Last Modified: 2008-03-17
Hello, I require to show a Continous form, that will show a Period Date  Product Code Customer Code and a Value.

This will originally come form a Table of Actual Figures that hold these fields.

Thing is I need to show a Full Years Data, while not all products will have been bought each Month.

Ok so the actual table looks like 200604 TS123 1020344 45.00
                                              200605 TS123 1020344 75.00
                                              200607 TS123 1020344 85.00

I need the form to show this but to Fill in the missing value info with Zero's so Like this:
200604 TS123 1020344 45.00
200605 TS123 1020344 75.00
200607 TS123 1020344 0.00
200607 TS123 1020344 85.00


What is the best method to try?
0
Comment
Question by:macphie
  • 4
  • 3
8 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 17850045
Instead of YourValue as the control source of the textbox, use =Nz([YourValue ],0)
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 17850101
You are actually trying to infer a [Period Date] field that does not exist in the table?
You would perhaps have to create a table with all possible period dates, and tie it to your ActualFigures table.

SELECT a.PeriodDate, b.productCode, b.CustomerCode, Sum(IIf([a].[PeriodDate]=[b].[PeriodDate],[Value],0)) AS Expr1
FROM DateTable AS a, ActualFigures AS b
GROUP BY a.PeriodDate, b.productCode, b.CustomerCode;
And this becomes the form's record source
0
 
LVL 1

Author Comment

by:macphie
ID: 17850257
Yes I'm looking to infer a Period Date field that doesn't exist in the table...

I have created the other table and linked these, but as it's a continuous form the i Get 2 dates and not Grouped.

So I get
200604 TS123 1020344 0.00
200604 TS123 1020344 45.00
200605 TS123 1020344 0.00
200605 TS123 1020344 75.00
200606 TS123 1020344 0.00
200607 TS123 1020344 0.00
200607 TS123 1020344 85.00

and I cannot seem to get the Union table to group this.


0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 26

Expert Comment

by:jerryb30
ID: 17850312
Please post your SQL linking the two tables.
0
 
LVL 1

Author Comment

by:macphie
ID: 17850440
SELECT Period, [Customer Number], [Delivery Point], [Product Code], Quantity, Value
FROM Qry_g2005_Values
UNION SELECT Period, [Customer Number], [Delivery Point], [Product Code], Quantity, Value
FROM tble_Months
GROUP BY  Period, [Customer Number], [Delivery Point], [Product Code], Quantity, Value;


As requested.
0
 
LVL 26

Accepted Solution

by:
jerryb30 earned 125 total points
ID: 17850552
My concept is that tbl_Months only has one field, all possible Period values.
That being the case, what you want is:

SELECT tbl_months.Period, qry_g2005_values.productCode, qry_g2005_values.CustomerNumber, qry_g2005_values.[Delivery Point], qry_g2005_values.quantity, Sum(IIf([tbl_months].[Period]=[qry_g2005_values].[Period],[qry_g2005_values].[Value],0)) AS VVALUE
FROM tabl_month,qry_g2005_values
GROUP BY atbl_Months.Period, qry_g2005_values.productCode, qry_g2005_values.CustomerNumber, qry_g2005_values.[Delivery Point], qry_g2005_values.quantity;

Not a union.
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 17851312
Did the sql work for you?
0
 
LVL 1

Author Comment

by:macphie
ID: 17856406
Thanks Jerryb, that did the job, should've known that as well I fell in the trap of "it's more difficult than I think".

Thanks again for that.
Mike
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question