Create Inventory Report base on Query

Dear Experts,

Pardon me if this question already been asked before.

I have this two table as follow :
Sales
SalesID SoldDate Product Customer Qty
S-1 1-Nov-09 Candy Jason 5
S-2 3-Nov-09 Bubble gum Rudi 9
S-3 15-Nov-09 Candy Junior 3

Purchase
PurchaseID Purchase Date Product Supplier Qty
P-1 31-Oct-09 Bubble gum Germany 11
P-2 28-Oct-09 Candy Italy 10
P-3 4-Nov-09 Candy Romania 7

The questions :
1. Does it possible to add prefix in autonumber field in a table like above (P-1, S-2)? Please describe the method.
2. What should I do to create the following query result :
"
Product QtyRemain
Bubble Gum 2
Candy 9
"
The idea is if I input certain product in a combo box, I could get information for the remaing stock.

3. What should I do to create the following report result :
"
Initial Date : 1-Nov-2009
Final Date : 30 Nov-2009

Product : Candy

Date ID Customer/Supplier QtyIn QtyOut Balance
1-Nov-09 Initial Stock 0 0 10

1-Nov-09 S-1 Jason 0 5 5
4-Nov-09 P-3 Romania 7 0 12
15-Nov-09 S-3 Junior 0 3 9

30-Nov-09 Final Stock 0 0 9
"

What kind of query needed?
Is there any formulas or equation for the report?

I saw this kind of report in my office application, unfortunately it use Visual Basic as a final program.

Please help me on this case with a simplify word.
Regards,
Eko

re: Create Inventory Report base on Query

First, create two totals queries; one for total sold, and one for total purchased. Group by Product and Sum on Qty. You can then easily restrict them by date.

re: Create Inventory Report base on Query

Part one of your question about prefixes on autonumbers. You cannot store prefixes but you can get them to display.

If you put the following into the format property of the PurchaseID field then it will display as you want.

"P-"#

Mary