Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Adding Null amounts into a Continous form query.

Posted on 2006-11-01
8
Medium Priority
?
259 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
[X]
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
  • 4
  • 3
8 Comments
 
LVL 66

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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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 375 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 Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

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…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

730 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