Problem setting up my table

[KimVong]

I am trying to set up some table, but don't know how to start. This is what I want to do

product, A,B,C,S (that's all I have)
sold = the amount sold for each product

and on my table I only want to have the month and year of product is sold.

and I'm trying to create a report that will alow me to list all the product that was sold on 3/03.
please anyone give me some hint of how to set up such table

thanks

 

[randysmid]

Hi,
Here is most of what you need. I am not sure how to set up a new date format that only has month and year, but here is the rest:
1) Go to Tables, and click New.
2) Select Design View
3) For the 1st field, enter Product for Field Name, with a text datatype. Click on Lookup, then click on Display Control and change to List Box. Under Row Source Type, select Value List. Under Row Source, enter "A";"B";"C";"S"
4) For the 2nd field, enter AmtSold for Field Name, but make this a Number datatype.
5) For the 3rd field, enter DateSold for Field Name, and make it a date/time datatype.
6) Before creating the report, it is best to create a query that will retrieve all the fields.
7) Create the report, using the query as the recordsource.

Once you get this part working, reply back to this post, and I should be able to give you the code that will select only those sales from the month/year that you want. HTH,
Randy Smith
California Teachers Association

 

[KimVong]

Hi Randy,
I finished creating the table the way you showed me. Now I am ready to select the product name and amtsold on a specific date. I'm trying to make the report that will show me the total amount for jan/02 sold, and the total amt sold for jan/03. How can I go about doing that?
thanks for your reply

Kim

 

[randysmid]

Hi Kim,
In a report, you can set Sorting/Grouping levels based on different fields that appear in Field List. With a date field, you can group by Month. I wrote an FAQ on how to do that exact thing. Here it is: faq703-2980

By the way, I have written a number of FAQ's for the new user. If you click on my name (randysmid), and scroll down, you will see a listing of my 19 FAQ's. Of course, this are all free. You may want to print them and review before placing them in a binder. HTH,
Randy Smith
California Teachers Association

 

[KimVong]

Hi Randy
I look at the link that you showed me, but it's not what I need. I have 3 tables, with Des, price1, price2 and date on each of the table. In the relationship, I have the date on table1 related to table2 and table2 date related to table3. I want to create a query using INNER join all three table that will give all the data from table1, table2, table3, with the date I've selected equal to on the query, I tried the innerjoin, and it keep on giving me only that data from table1, with duplicates. Can you please help me on this?
thanks

Kim

 

[randysmid]

Kim,
Why don't you try using the query tool to create your query? It looks like you are trying to write the SQL code to do this? Anyway, in the query tool you can click on the lines connecting the tables, and select Join Properties. This will determine how the data is selected from the tables.

FYI - I have been using Access 97/2000 for the past 5 years. I have rarely used coded SQL to retrieve data for a report or form, unless it is a very special circumstance, e.g., look up values that are not part of the recordset for the form/report.

HTH,
Randy Smith
California Teachers Association

 

[KimVong]

Randy,
I hope this is the last question.
From what I've describe to you on what I need. Do you think I need to have 3 tables or just one. I have three warehouse, and I decided to put it in three tables, but it's hard to make a report in three tables. what do you think?

 

[randysmid]

Hi,
From what I have seen so far, I would have the following tables:

tblProduct (I described how to set that up in my 4/4 post)
Fields:
Product
ProductDesc
ProductPrice
ProductQtyOnHand

tblCustomer (not necessary if you are not keeping track of any customers, like a garage sale)
CustomerID
CustomerName

tblProductSales - would contain the following fields:
Product
CustomerID (not necessary)
SoldQty
SoldPrice
SoldDate
SoldComments (add any notes to this field)

You can add additional fields to each table as you see fit. For instance, you may need to keep track of customer addresses, city, state, preferred method of shipment (e.g., UPS). In your ProductSales, you may want additional information, such as ship date, ShipMethod (e.g., USPS, UPS, FedEx, etc.).

So, do you see how you separate out the different kinds of information into specific tables? One of the rules of good database design is not to have any thing duplicated. For instance, you wouldn't want customer address to appear in tblProductSales, but it could contain an alternate shipping address for that particular sale.


HTH,
Randy Smith
California Teachers Association