Calculation problem in Access!

[Schmals]

hello everyone...

got a really quick question... i have 2 queries that i need to put into a report. the report wouldn't let me use both queries, so i combined both queires into 1. i have a few columns, 1 i have specified product #'s (10 out of 20) and the other i need sales $. what would be the rule i would need to use so that it would show me the product code when the sales $ are zero

sales prod#
5555 2
7585 3
0 6

thanks for all your help!
schmals

 

[Golom]

Probably a LEFT JOIN but there's no real way to know without seeing the SQL.

 

[Schmals]

isn't there a rule that would let you see the value 0??
schmals

 

[Golom]

To Schmals

I don't think that the issue is displaying zero. I think that it is displaying products for which there are no sales records. It's not really clear from the above problem statement exactly which one is giving the difficulty.

 

[Schmals]

thanks for all your help golom,

the problem is that if there is a zero in the sales column, that product is not being displayed on the line. (ex. if i sold 10 out of 13 products, only 10 would show up in the query not 13). right now there is a 0 if there were no sales.. any ideas???

schmals

 

[lespaul]

Schmals,

We are making an assumption that you have normalized tables and probably set up like:

tblProducts
ProductID
ProductName

tblSales
SaleID
ProductID
Amount

You would have something like:

Select tblProducts.ProductID, tblSales.Amount from tblSales inner join tblProducts on tblSales.ProductID = tblProduct.ProductID

By having the inner join the query says: Give me all the records from tblSales and only those that match from tblProduct.

If you want to include products that don't have a sale you would need to do this:

Select tblProducts.ProductID, tblSales.Amount from tblProducts LEFT JOIN tblSales on tblProduct.ProductID = tblSales.ProductID

This query says give me all the products and where there is a sale include it too.

If you will give us some more information about your table structure and your existing query as asked by Golom earlier, we can probably come up with exactly what you are looking for. But we're just shooting in the dark right now.



Leslie

 

[Golom]

lespaul's answer is exactly right.

Just to clarify your thinking (as far as databases and SQL are concerned) when there are no sales records then the sales are NOT zero; they ARE NULL. That is the sales are unknown.

By inference from the real world of course, the lack of a sales record is the same thing as zero sales. To the database however, "zero sales" and "no sales records" are not the same things.

 

[Schmals]

leslie & golom,
thanks for all the help. here is the scoop. i am using 4 tables for my sales query and 4 tables for my inventory query. i tried connecting all the tables together in one big query but that was a mess.. so i thought i might be able to do it in a report....

i have a few different objectives:
1) to identify what products were sold (or not sold) during
a period of time (1 week)*** here is where the problem
occurs, it shows where we had sold products, but if
there was no movement there would be a zero which i
can't make appear****
2) what is the average sell price the product was sold for
3) what is the current inventory on hand

i did create a table with just the products codes that i am looking for, so that was seperate and linked it on the sales side first. right now i am just lost.. leslie, when you mentioned LEFT JOIN, i am not familiar with that? do i have to actually type "LEFT JOIN" somewhere??
thanks again for all your help!!

schmals

 

[lespaul]

Here's two options:

1. List all of your tables and relevant field names (for both sales and inventory) and we may be able to come up with a single query to achieve your goals.

2. One what to set up a left join:
In your query, change from design view to SQL view and type the correct SQL.





Leslie

 

[Schmals]

hey leslie & golom.. here is all my info... hope this makes sense...


Sales Query

Tb1: Franchise products (these are the #’s I need)
*Product #
Description

Tb2: Master Product list
*Product#
Description
Weight weight X cases=ttl lbs. (5*10= 50 lbs)
Cases
Brand

Tb3: Actual Products Sold
*Customer #
Document #
Sales in Dollars
Weight in lbs Sold
*Product#

Tb4: Order Info.
*Customer#
Document#
Year
Month
Day

I have put a * where I have linked the tables…


Inventory Query

Tb1: Franchise products
(these are the #’s I need)
*Product #
Description

Tb2: Master Product list
*Product#
Description
Weight weight X cases=ttl lbs. (5*10= 50 lbs)
Cases
Brand

Tb3: Inventory History
*Lot#
Date of Purchase -this is all the history of each product

Tb4: Lot Info.
*Lot#
*Product # -this is where we track how the inventory is shipped/sold


I have put a * where I have linked the tables…

basically what i usually do is do a part a and part b to the table. part a contains just the raw data. in part b i use the sum function and put in equations for the sales dollars and calculating weight in cases, etc. i figured that once i had these two queries correct ( sales & inventory) i would be able to merge them in a report. but no luck... so i had the brilliant idea of having both sales part b and inventory part b in one query, then doing the report... it said it was too complicated.. that is about all the info i have... any other suggestions would be greatly appreciated!!!

thanks again,
schmals