Okay, I can't find this anywhere else

[Guest]

I have a form to enter data for an invoice, how do I tell the form/query to
automatically list every item in the table so that values can be entered.
From what I can tell of the query, a zero value must be entered for the item
to show up on the report properly. Is this possible? Can I have the items
automatically listed?

 

[John W. Vinson]

I have a form to enter data for an invoice, how do I tell the form/query to
automatically list every item in the table so that values can be entered.

HUH!?

So if you have a parts inventory with 33127 parts, you want to see 33127 rows
on the form? That makes no sense at all.

From what I can tell of the query, a zero value must be entered for the item
to show up on the report properly. Is this possible? Can I have the items
automatically listed?

Please describe your database a bit. We cannot see your screen; we have no way
to know how your tables are structured or related. It is certainly NOT
necessary to "enter a zero value" for a record to show up in a report, if the
report and the query underlying it are designed correctly.

Let us know some of the context and we'll try to help. Have you looked at the
Northwind sample database that came with Access?

John W. Vinson [MVP]

 

[Guest]

My invoice includes approx 30 items, every item must be listed every month.
The query that leads to this invoice includes my requisition table which
includes the dates (start and end) the project #, the req # and req ID. The
requisition detail table includes all of the detail (the 30 items) of
potential billing. what the contract value is, how much is being billed, what
was previously billed, balance to finish and the percentage of the work
completed. I guess because of the expressions written to come up with some of
the numbers, unless a zero value is entered items without values do not
appear.

I've looked into the sample database but I haven't found anything that comes
close.
HELP!

 

[John W. Vinson]

My invoice includes approx 30 items, every item must be listed every month.
The query that leads to this invoice includes my requisition table which
includes the dates (start and end) the project #, the req # and req ID. The
requisition detail table includes all of the detail (the 30 items) of
potential billing. what the contract value is, how much is being billed, what
was previously billed, balance to finish and the percentage of the work
completed. I guess because of the expressions written to come up with some of
the numbers, unless a zero value is entered items without values do not
appear.

Please open the query in SQL view and copy and paste the SQL text to a message
here. I suspect that you can use NZ() to convert nulls to zeros in your
calculations (rather than storing bogus zeros in the table), and/or Left Joins
in the queries to include records even if data is absent in some other table.

John W. Vinson [MVP]

 

[Guest]

Okay here it is:

SELECT Requisitions.[Req#], [Requisitions Submitted].Subjob,
Requisitions.[Project#], [Subcontract Status Details].[Cost Code],
[Requisitions Submitted].Trade, ([Subcontract Status Details]!Value) AS
[Original Contract Value], ([Subcontract Status Details]![Change Value]) AS
[Approved Changes], ([Original Contract Value]+[Approved Changes]) AS [Total
Contract Value], IIf(([Req#]=1 Or [Totals Requisitions
703]!SumOfCurrentApp-[CurrentApp]<0),(0),([Totals Requisitions
703]!SumOfCurrentApp)) AS [Prev Req], IIf([Req#]=1,[Prev Req],[Prev
Req]-[CurrentApp]) AS Previous, [Requisitions Submitted].CurrentApp,
IIf([Req#]=1,[Prev Req]+[CurrentApp],[Previous]+[CurrentApp]) AS [To Date],
([Total Contract Value]-[To Date]) AS [Balance to Finish], ([To Date]/[Total
Contract Value]) AS [Percent Complete], [Requisitions
Submitted].RetainageHeld, ([To Date]*[RetainageHeld]) AS Retention,
Requisitions.[PeriodFrom:], Requisitions.[PeriodTo:],
FROM Projects INNER JOIN ([Totals Requisitions 703] INNER JOIN ([Subcontract
Status Details] INNER JOIN (Requisitions INNER JOIN [Requisitions Submitted]
ON Requisitions.REQID = [Requisitions Submitted].ReqID) ON ([Subcontract
Status Details].Subjob = [Requisitions Submitted].Subjob) AND ([Subcontract
Status Details].[Scope of Work] = [Requisitions Submitted].Trade)) ON
([Totals Requisitions 703].[Cost Code] = [Subcontract Status Details].[Cost
Code]) AND ([Totals Requisitions 703].Subjob = [Subcontract Status
Details].Subjob)) ON Projects.[Project#] = [Totals Requisitions
703].[Project#]
WHERE (((Requisitions.[Req#])=[Forms]![Requisition Filter]![Req#]) AND
((Requisitions.[Project#])=[Forms]![Requisition Filter]![Project]))
ORDER BY [Requisitions Submitted].Subjob, [Subcontract Status Details].[Cost
Code];

 

[John W. Vinson]

Okay here it is:

Whoo. And this is probably based on other queries, or on stored derived data?

Just a few suggestions:

- use . rather than ! as a delimiter in tablename.fieldname references.
- If some field containing NULL is used in a calculation, use the NZ()
function to give it a value in the calculation expression (to avoid having to
store zeros in your table). E.g. instead of

([Original Contract Value]+[Approved Changes]) AS [Total Contract Value]

use

([Original Contract Value] + NZ([Approved Changes], 0)) AS [Total Contract
Value]

If there is nothing in the Approved Changes field your expression will yield
NULL as a result; the NZ() function will convert that null to a zero.

Just apply this wherever you have fields which might be NULL and should be
treated as zero if so.

John W. Vinson [MVP]