Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Adding Null amounts into a Continous form query.

Posted on 2006-11-01
8
241 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

838 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