Pass Variable from Table to SQL

[Rickinrsm]

Okay - I'm not very good at explaining my problems but I'll do the best I can.

I have 20 small tables with one field containing approximately twenty office names.

I need Access VBA code to decalre a variable, then something like, for i from 1 to endOfTable, look at tblName, assign the first office name to the variable then run the SQL code below using the variable.

Then I will dump the WST and the WSTDV and the variable into a small table that I will place on the last page of a report and look for the next office in the tblName and do it again until all of the offices have been run.

Whew! Hope I got that right.

Please remember - I know nothing about VBA!

SELECT Sum([SALESPRICE]) AS [WSTDV], Count(OCMonthlyData.SalesPrice) AS [WSTCount]
FROM OCMonthlyData
WHERE (((OCMonthlyData.OFFICELIST_OFFICENAME) Like "variable") AND ((OCMonthlyData.OFFICESELL_OFFICENAME) Not Like "variable"));

Thanks Much . . .

Rick ~ Access Newbie

 

[GingerR]

Do you mean that you have one table per office?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[PHV]

Why not simply this ?
SELECT OFFICELIST_OFFICENAME, Sum(SALESPRICE) AS WSTDV, Count(SalesPrice) AS WSTCount
FROM OCMonthlyData
WHERE OFFICELIST_OFFICENAME <> OFFICESELL_OFFICENAME
GROUP BY OFFICELIST_OFFICENAME

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Rickinrsm]

Ginger . . .

I have 20 small tables with one field (column) containing approximately twenty office names. I need to run EACH office separately through the SQL query.

PHV . . . I'm not sure what you mean.

Where does the variable for each of the 20 or so office names come into play? I need VBA code to assign a variable to officeName and run each office through the SQL until it reaches the last office in the table.

It's even confusing for me. Ha!

I need the VBA code to "goTo" the OfficeName table, look at the first office in the officeName column, assign that office name to a variable,ie. OfcNme.

Back to the VBA code. Run the SQL inserting the OfcNme variable.

Assign the the Variables OfcNme, WSTDV, and the WSTCount to a separate table.

Then run the same VBA code again using the second officeName in the OfficeName table, an so forth, until it reaches the last officeName in the table and the exits the code.

Thanks . . .

Rick ~ Access Newbie

 

[PHV]

Have a look at Recordset (either DAO or ADODB).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Rickinrsm]

Read RecordSet in Access 2003 Bible.

Makes no sense to me!

I'll take baby steps.

How do I point to a table using VBA?

Thanks . . .

Rick ~ Access Newbie

 

[GingerR]

This is still confusing. Please tell us your table structure. Telling us you have "20 small tables and one field" isn't working!!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Rickinrsm]

Okay . . . I said I couldn’t explain this well . . .

I have 20 clients.

Each client has a set of offices that they want me to rate each month. So for each client, I create a small table that stores the office names that they want rated.

If they want to add or delete offices all I need do is change it in the table. No programming required!

So, for client A, I create, let’s say, tblA with one field that stores all the offices names, usually 20 or less.

So I want a program that when launched . . .

Ask user to enter the table they want to run (ie. tblA)

For I from 1 to EOT (Is there an EndofTable command in Access?)

User Selects tblA, assign a variable (“string”) of the FIRST office name in tblA,

DoWhile (Not sure what command/function Access 2003 uses)

Place that variable in the query . . .

Run the query with calculations. . .

Assign all of the variables and calculations to another table . . . (previously created and linked to each clients report.)

Loop back to the beginning . . .

Again point to tblA . . . select the SECOND office name in the tblA and start the process all over again until all of the office names in tblA have been run.

EndDoWhile . . .

Then, ask the user which report to open . . .

User is presented with list of reports . . .

Open the report and move to the last page of the report.

eND of program . . .

Thanks for your assistance.



Rick ~ Access Newbie

 

[PHV]

SELECT OFFICELIST_OFFICENAME, Sum(SALESPRICE) AS WSTDV, Count(SalesPrice) AS WSTCount
FROM OCMonthlyData INNER JOIN tblA ON OFFICELIST_OFFICENAME = tblA.field1
WHERE OFFICELIST_OFFICENAME <> OFFICESELL_OFFICENAME
GROUP BY OFFICELIST_OFFICENAME

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Rickinrsm]

Hi PHV . . .

FROM OCMonthlyData INNER JOIN tblA ON OFFICELIST_OFFICENAME = tblA.field1

Does this establish a link to tblA . . ?

If so, how does it go through each office listed in tblA, or does it do that automatically until it reaches the end of the table/field?

WHERE OFFICELIST_OFFICENAME <> OFFICESELL_OFFICENAME
GROUP BY OFFICELIST_OFFICENAME

Also, I don't understand the function of, < less than and > greater than, as used in this example.

Would love to know this.

Thanks . . .

Rick ~ Access Newbie

 

[abaldwin]

I understand what you are trying to do and at the risk of getting comments such as "Why not answer it the way he asked for it" I would like to point out a few things that may make your life and database app easier in the future.

Instead of a separate table for each clients offices I would suggest a more relational structure.

One table with customer information

i.e. Customer Number (your customer number), address, contact name, etc etc

one table with offices

CustomerId, storename and any other data associated with the store.

Then you can run queries based off the customer ID instead of having code for a non-code problem.

Just trying to help you get your design right in the beginning to make life a little better in for growth and reporting in the future.



Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[Rickinrsm]

Andy . . . I like that idea.

I do understand table relationships but I'm not sure how to proceed with the rest of the program.

Just so you know, I run three queries for each office name and dump the results in a linked table on the last page of a report.

In using the customer ID, how would the program run one office at a time (In the many to one tbl) through the query?

Or does it query all of the offices at once? How?

The way I have it in Padadox is in lieu of 20 different tables I also have 20 different programs connected to the 20 different tables. I KNOW there is a better way.

I want to use just one program for ALL of my clients.

I'm migrating to Access 2003 and want to design a tighter application at the same time. Yikes!

Thanks Everyone . . .





Rick ~ Access Newbie

 

[GingerR]

read about Normalization and Relational Database Design

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Rickinrsm]

Ginger . . . Those are two items that I do understand.

I've been programming in Paradox's Opal for a long time. Sort of a self taught method but I started with Paradox Version 2.5 in the old DOS days.

But I never had any formal training so now I'm trying to understand application design and the many things I know exist but but don't understand.

Paradox had one excellent thing I can't find in Access. It's called a Tcursor. Awesome capabilities.

Access and VBA is entirely new to me but it has peaked my interest.

Thanks ALL so much . . .

Rick ~ Access Newbie

 

[abaldwin]

To answer this part
"In using the customer ID, how would the program run one office at a time (In the many to one tbl) through the query?"

You would be restricting your query to the customer and the store that you wanted. Or if this is part of a sub report then you could link by customer, store, or both.

I agree that you need to read up first on Normalization and Relationships. May be confusing in the beginning but these two items are really the cornerstone building blocks of any relational db system. Some of the old flat file type db's did not do this kind of normalization and can make it harder for somebody to understand in the beginning.

There are several great books on ACCESS, VBA, and SQL out there. 100 dollars worth the books may do you a world of good.

Have a Merry Christmas.


Andy Baldwin

"Testing is the most overlooked programming language on the books!