Invoicing (97/SR2)

Topic

New Reply

I started a little project that seemed so simple, I know it can be done but I’m stumped. I may have mucked it up but I figure it can always be fixed.

I am trying to create a simple, little database to prepare invoices for shipping charges for our customers. We charge for 3 different things, hours, mileage and weight. Not all invoices will have all items, might be only one, two or all three. They want to be able to enter the start and stop time for the hours, and have the system calculate the total. Since that is different from the other 2, I set up a table for hours and then one that contains the mileage and the weight. I also have a Custumer & Order table and an hourly rate table, there are currently 4 different hourly rates but there could be more in the future and a piece rate table, for the mileage and the weight. The rate for the weight changes depending on the total weight.

This all seemed to work very well, I have an Order query that pulls together the customer and the Order information, and then 2 other subqueries that pull together the hours & rates and the pieces & rates. I then setup a master Order Form, with a 2 subforms, one for the hours and one for pieces. I couldn’t figure out how to tie my 2 different subqueries together with the Order query, since there may be some Orders that only have hour charges, (and may have more than one type of hour charge, straight time and overtime, for example) some that may only have pieces (mileage & weight) and only some that have both. When I construct a query, it only wants to show the items where everything ties to the Order #, which is my link. For instance, in my test, I have 33 items in the hourly table and 19 in the Piece table but I only get 43 items in the query. Any items from the hourly table with an OrderID that does not match an item in the Piece table, won’t show up.

Because of this, I couldn’t create a calculated field in the query to get total dollars, so I had to use a calculated control on the subforms and forms to get a total for each Order, which doesn’t get stored anywhere and then when I create Reports, I have to calculate everything all over again, which doesn’t always work right and I’m afraid the information may change, so the report is inaccurate.

Sorry if this is so long. I’m sure I’m missing a step here somewhere. If someone can help me out, I sure appreciate it. Thanks.

Reply | Quote

Replies

Your main problem to me sounds like you need to modify your ‘join’ properties in your query.

At present, it sounds as though your join is an ‘INNER JOIN’ (solid line without any arrows between each table.

An INNER JOIN will only show records where identical values exist in both tables. This is clearly not the case between the customer table and your hours and mileage and weight tables (because sometimes, customer orders don’t have records in one table).

You need to make the join between the Cusomter/Order table and the Hours table so that the query will return ‘All records in the Customers/Order table and those that match in the Hours table’.

You need to make the joing between the Customer/Order table and the Mileagage/Weight table so that the query will return ‘All records in the Customers/Order table and those that match in the Mileage/Weight table’.

To do this, in your query, right click on the each join, select ‘Join Properties’ and then select the appropriate options.

When you have done this, your query should have arrowed joins, moving out from the Customer/Order table, pointing towards the Hours and the Mileage/Weight table.

I would also note that I would have give Hours it’s own table, Mileage it’s own table and Weight it’s own table (not agregated two together). I would have given Customers their own table and Orders a seperate table.

Access comes with an example database called Northwind. This is an inventory and order database. Have a look at it and it might give you some ideas. You may need to learn more about database normalisation too.

Reply | Quote

You may find these articles handy (they are about normalisation)

http://msdn.microsoft.com/library/default….des_02_2oby.asp
http://support.microsoft.com/support/kb/ar…s/Q209/5/34.ASP

There are also some longer and more complex articles on MSDN, have a search around and see what you can find. The MSDN address is:

http://www.msdn.microsoft.com

Cheers

Edited to activate links–Charlotte

Reply | Quote