Print all - even if result is Zero

[Guest]

Help, I'm stumped.

Basically, I need the report to look like the form that the user's currently
use. They enter the correct info on the line for the part.

I would like to run/print a daily report that lists all of the parts, even
if the part number has no value entered for that day.

Will each of the part numbers need to be a Field?

Thanks for your help, Glenda

 

[Allen Browne]

Hi Glenda. You *definately* do not want a field for each part. To solve this
problem, you will need to set up a good relational database, and then design
a query with an outer join. This answer is rather long, but it will solve
not only this problem but lots of others you have not come across yet. I
hope you stick with it and get it solved.

Presumably these are parts that are used or sold in some kind of
transactions. I'll assume you are using parts as part of services on
vehicles or something. You will need at least these tables:

Part table (one record for each part.) Fields:
PartID AutoNumber primary key
PartNum Text the part number.
PartName Text description of the part.
...

Service table (one record for each service):
ServiceID Autonumber primary key
ServiceDate Date/Time Date this service was completed.
...

ServiceDetail table (one record for each part used in a service):
ServiceDetailID AutoNumber primary key
ServiceID Number. Which service this part was used in.
PartID Number. Which part was used in this service.
Quantity Number. How many of this part used in this
service.

In the Relationships window (Tools menu), you will create relationships from
ServiceDetail table back to the other 2.

The interface will be a main form for the Service, with a subform for the
parts used in the service (one per row.)

Once you have those tables in place, you can create the query:
1. Create a query, and add the Service and ServiceDetail tables.

2. Depress the Total icon on the toolbar.
Access adds a Total row to the grid.

3. Drag the ServiceDate field into the grid.
Accept Group By in the Total row under this field.

4. Drag the PartID field into the grid.
Accept Group By under this field.

5. Drag the Quantity field into the grid.
In the Total row under this field, choose: Sum

6. Save the query with the name (say) qryDailyPartCount. Close.

7. Create another query, using the Part table and qryDailyPartCount as
source tables.

8. In the upper pane of query design, you should see a line joining PartID
in both tables. If not, drag PartID from one table onto the other.

9. Double-click the line joining the 2 tables.
Access pops up a dialog offering 3 choices.
Choose the one that says:
All records from Part, and any matches from qryDailyPartCount.

10. Drag these fields into the output grid for your query:
- PartID from the Part table;
- SumOfQuantity from qryDailyPartCount;
- ServiceDate from qryDailyPartCount.

11. In the Criteria row under the service date, enter the date range you
want, plus the Null condition, e.g.:
IsNull Or Between #1/1/2006# And #1/31/2006#

At step 9, you created an outer join. That, in combination with the
inclusion of nulls at step 11 ensure all parts show in the results.

More info about outer joins and the nulls issue in this article:
The Query Lost My Records!
at:
http://allenbrowne.com/casu-02.html

 

[John Vinson]

Help, I'm stumped.

Basically, I need the report to look like the form that the user's currently
use. They enter the correct info on the line for the part.

I would like to run/print a daily report that lists all of the parts, even
if the part number has no value entered for that day.

A Left Join query will probably work here.

Will each of the part numbers need to be a Field?

Absolutely NOT!!! Storing data in fieldnames is simply *wrong*.

Do note that you are making the assumption that we know your table
structures, what your form looks like, and (thereby) what you want the
report to look like. We cannot see your computer, and you have not
posted (that I have seen) any information that would help us
understand the basis of your design. Care to give us a hand here?

John W. Vinson[MVP]

 

[Guest]

Thanks John, I will try Allen's solution and then add to this if necessary.

 

[Guest]

Thanks Allen, I will try this, you are correct I've never used an outer join,
I'm looking forward to it.

 

[Guest]

I set up the data base tables as instructed. Now I have questions about
inputting the data. I have tried a number of methods. A basic question -- I
do not understand the relationship between Service.ServiceID and
ServiceDetail.ServiceID. Is it necessary to enter the ServiceID for the
ServiceDetail? For the example you supplied, can you provide a form?

Thank you, Glenda

 

[Allen Browne]

The interface will consist of a main form bound to the Service table, with a
subform bound to the ServiceDetail subform.

When you enter a record in the subform, Access will automatically copy the
value of ServiceID in the main form into the subform, so you don't have to
enter that. Just enter the PartID and Quantity in the subform and Quantity.
And then enter another row in the subform if you used more than one part in
the service.

The PartID in the subform will probably be a combo box, which gets its
RowSource from the Part table. If the Quantity is usually 1, you could set
the Default value of the text box to 1. Now the user just picks a part in
the drop-down list, and presses the Tab key twice to jump to the next line
and pick another part. Data entry is really quick.

If you need an example of how to do a form and subform, open the Northwind
sample database, and open the Orders form in design view. It has a subform
for entering the line items of the order. To see how the tables fit
together, choose Relationships form the Tools menu. In their case, Products,
Orders and Order Details are similar to your Part, Service, and
ServiceDetail tables.

 

[Guest]

Thank you so much. I will try this today or tomorrow. Your help is greatly
appreciated.