Newbie Help On Queries

[DJSSystems]

I have a costing model which my employer wants converting from Foxpro into Access (Version shipped with Office 2000).

The model has a serious of forms for collecting data about a particular warehouse ie. Pallets received per hour, unloading time per pallet, moving to storage time for a pallet etc. There are around 100 of these values split over
several screens but all stored in one table (tblWarehouse).

I then have a serious of forms to display the results of calculations performed on these stored values ie.

Pallets received per hour/Cases Per Pallet=Cases
Received Per Hour.

There are around 50 or so of these calculated values which i need to show on one form. I have tried using a query to do the calculations but I can only do 16 calculated values in the query it won't allow any more.

What i am asking is do i need to build 5 queries to calculate the results and then set the source properties to the 5 query fields (if so how do i do this) or do i need
to create a module to perform the calculations in. (if so how do i declare the results variables in order to access them on my results form).

I am new to access and i am only just grasping the basics so any help would be greatly appreciated.

Thanks

David David J Sides - DJS Systems UK

 

[LittleSmudge]

I would suggest that you create a form to display the results of the calculations that is UNBOUND. ( Ie. not tied to an underlying table.

Then in a suitable event ( On_Load maybe ) open a recordset on the data in the underlying table and do all of the calculations based on this dynaset. You can then populate the controls on the form with the calculated data.

I've used this approach on a form showing dozens of calculated values. There is no practical limit.


If you need help on creating the Recordset etc then come back here with some more questions.


'ope-that-'elps.

G LS

 

[DJSSystems]

LittleSmudge

Thanks for the info i got the desired effect by setting the control source of my form to the table containing the warehouse data and then calculating each result in an unbound text box.

I would prefer to use your suggested method as my next results screen deals with some of the more complex calculations and will require IF and CASE structures. Although i have been programming for 10 years i have only used Foxpro and i am new to Access and this method of development so could you provide me with an idiot proof description of how to do the Dynosets, Recordsets etc.

Thanks

David



David J Sides - DJS Systems UK

 

[LittleSmudge]

A dynaSet is the collection of data that you get from selecting a Recordset.

HOW to do recordsets depends on what version of Access you are running

A97 & before use DAO
A2k and beyond your ADO

The two are HUGELY different - that's why MicroSoft saw fit to call them such similar names :-(


What version are you on ?

G LS

 

[DJSSystems]

LittleSmudge

I am running the version that is shipped with MS Office 2000
(9.0.2720). So i presume i am ADO whatever that is.

Thanks

David

David J Sides - DJS Systems UK

 

[LittleSmudge]

Yes David - you are on Access 2000 with will, by default, run ADO

A recordset can be thought of as the result of a Query that is held in memory and can be manipulated using code.

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenKeyset
rst.LockType = adLockPessimistic
rst.Open "SELECT * FROM tblMyTable"

The rst.Open line takes any valid SQL SELECT statement that you'd use in a query design.
For complex queries - design it in the Access QBE query design screen and then switch to viewing the SQL version, COPY the SQL text and then paste it into the rst.Open line in VBcode.

You can then do things like:-
If rst.EOF Then ( EndOfFile means there are no records in your set )

While Not rst.EOF
ControlOnForm = rst!FieldName
Control2OnForm = rst!SecondFieldName

rst.MoveNext
Wend




'ope-that-'elps.

Try it and see where you get to.. ..

G LS