looking up product weights

[Jesse]

My hunch is this question will make clear at least one basic concept
of Access 2000 that is escaping me, but I'll put vanity behind.
Besides, maybe if I get this to work I'll get the concept(s), too ...

I have a db. promo_tbl, with names and addresses and four columns.
Each column -- prod1_qty, prod2_qty, prod3_qty, prod4_qty --
corresponds to a product, and in each one goes the qty of that product
that will be sent to the person. I want to do a query and in it add a
column that calculates for each record the total weight of the
products going to that person -- and then I export the query to csv
for mailing software.

There is a simple second table, catalog_tbl, with 5 columns, including
prod_ID and prod_wt (weight).

1st question: Does this have to be written directly in SQL or can it
be done as Access expressions?

2nd question: If it must be in SQL and if I have the code, where does
it actually go? Does it get stored somewhere and then I invoke it in
the Access query column? Does it go in the Access Field row in the
query?

3rd: If it gets typed into the query's SQL view, is there a way to
increase the font size of that tiny type?

In plain English, I want:

prod1_qty * prod_wt where prod_ID = "prod1" +
prod2_qty * prod_wt where prod_ID = "prod2" + (etc) ...

Help with any of this would be greatly appreciated. TIA. - Jesse

 

[John Vinson]

My hunch is this question will make clear at least one basic concept
of Access 2000 that is escaping me, but I'll put vanity behind.

Well, maybe a couple of concepts actually...

Besides, maybe if I get this to work I'll get the concept(s), too ...

I have a db. promo_tbl, with names and addresses and four columns.
Each column -- prod1_qty, prod2_qty, prod3_qty, prod4_qty --

Then your table structure IS WRONG. "Fields are expensive, records are
cheap". If a person can buy four products, someday they might buy
*five* - and what do you do then? And do you store a person's name and
address over and over again if they happen to want repeat business?

STOP. You really need to learn about normalization! Check out the
resources at

Jeff Conrad's resources page:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

especially the "Database Design 101" links on Jeff's page. More
below...

corresponds to a product, and in each one goes the qty of that product
that will be sent to the person. I want to do a query and in it add a
column that calculates for each record the total weight of the
products going to that person -- and then I export the query to csv
for mailing software.

There is a simple second table, catalog_tbl, with 5 columns, including
prod_ID and prod_wt (weight).

1st question: Does this have to be written directly in SQL or can it
be done as Access expressions?

Access expressions ARE SQL. The query grid is nothing but a tool to
create SQL queries.

2nd question: If it must be in SQL and if I have the code, where does
it actually go? Does it get stored somewhere and then I invoke it in
the Access query column? Does it go in the Access Field row in the
query?

You can open a Query in design view and select View... SQL from the
Menu, or choose the SQL tool from the dropdown tool on the left end of
the query design toolbar. You can toggle back and forth between SQL
view and design-grid view of the Query. The SQL is fundamental, and is
the "real query"; the grid is, as I say, just a tool to make it easier
to create SQL.

3rd: If it gets typed into the query's SQL view, is there a way to
increase the font size of that tiny type?

Sure wish there were!!

In plain English, I want:

prod1_qty * prod_wt where prod_ID = "prod1" +
prod2_qty * prod_wt where prod_ID = "prod2" + (etc) ...

Help with any of this would be greatly appreciated. TIA. - Jesse

First off... normalize your tables. I'd see:

Customers
CustomerID <Autonumber Primary Key>
LastName
FirstName
<other bio information, address, etc>

Products
ProdID <Primary Key>
ProductName
CurrentPrice
Prod_Wt
<other information about the product as an entity>

Purchases
PurchaseID <Autonumber Primary Key>
CustomerID <long integer link to CustomerID>
PurchaseDate

PurchaseDetails
PurchaseID <Long Integer, field 1 of 2-field primary key>
ProdID <Field 2 of 2-field primary key>
Quantity


You can certainly create valid SQL - the above isn't it - directly in
the SQL window, but you can also create queries dirctly in the grid
(I've been writing SQL since 1985 and I use the grid often); you can
put calculated expressions such as

TotalWeight: [Prod_Wt] * [Quantity]

directly in a vacant Field cell.

Good luck - you've made a good start, just got off into the
(well-trodden!) undergrowth beside the path.

John W. Vinson[MVP]