Re: sql query which has got 3 dynamic parameters

On Feb 23, 12:56 pm, Ed Murphy <emurph...@xxxxxxxxxxxx> wrote:
Vic wrote:
We have another set of tables like
'TestData6061' or 'TestData606' etc where we have some data.

This sounds badly designed. Assuming that these tables look something
like this:

[TestData6061]

CustomerID | FirstName | LastName | TestResult
-----------+-----------+----------+-----------
1 | Winona | Lord | BTN
2 | Billie | Keister | XRC
3 | Daren | Koster | RNC
4 | Valentine | Perkins | FNG
5 | Aleta | Geyer | RWC

[TestData6062]

CustomerID | FirstName | LastName | TestResult
-----------+-----------+----------+-----------
6 | Bronte | Dunlap | SSC
7 | Sinclair | Basmanoff| RRE
8 | Monty | Siegrist | RBN
9 | Suzie | Bailey | NNN
10 | Shantel | Powers | BNM

they should be replaced with a single table like this:

[TestData]

TestID | CustomerID | FirstName | LastName | TestResult
-------+------------+-----------+----------+-----------
0601 | 1 | Winona | Lord | BTN
0601 | 2 | Billie | Keister | XRC
0601 | 3 | Daren | Koster | RNC
0601 | 4 | Valentine | Perkins | FNG
0601 | 5 | Aleta | Geyer | RWC
0602 | 6 | Bronte | Dunlap | SSC
0602 | 7 | Sinclair | Basmanoff| RRE
0602 | 8 | Monty | Siegrist | RBN
0602 | 9 | Suzie | Bailey | NNN
0602 | 10 | Shantel | Powers | BNM

So for running the reports we basically want to
pass that 'accounting', 'fundstructures' etc and not hard code it.

This is the confusing bit. What sort of procedure do you have that is
equally able to operate on 'accounting' and 'fundstructures' and several
other things? Unlike TestData0601 and TestData0602, they don't sound
like they have similar structures.

Are you doing a lot of SELECT * stuff? If so, then that's another thing
that may be good to revise.

So basically we have 2 tables as I mentioned 'SchemaFields' which
looks like this (gets populated by a perl script which parses some
data )

SchemaID Object Object Field Field Field Field
Class Type Name Number DataType Requirement

136 accou Accntg Record 1 genString Mandatory
nting Param Class
eters
136 accou Accntg Record 1 genString Mandatory
nting Param Action 2 ub1
Optional
eters

And then we have our 'TestData6061', TestData606, TestData607 tables
where test data is residing. It'll have test cases with actions as
'New', 'Update' and 'Delete'The idea is to map the above 6 columns
(other than SchemaID) with the data in the test data bases to find out
the coverage for each object and run report which will show a matrix
of
Object Class, ObjectType and New/Update/Delete (from TestData tables)
for these object classes n object types

So while running reports we just want a sql query (either plain select
statement or a stored/procedure, function etc) which is of the form
EXECUTE <StoredProcedure/Function> 'TestData6061', 'accounting', 133
TestData6061 and 133 is obtained from the dropdown list on the report
but the main issue is to iterate through all the object classes which
is 'accounting', 'fundstructures', 'calendar' etc and thats what the
issue is. Doest that make sense?
.

Relevant Pages

  • Re: drop down list within a query?
    ... I have done as suggested and i now get this message "The command or action ... When the code window opens the cursor will be flashing between 2 ... Let's assume it is a CustomerID number you need as criteria. ... Run the Report. ...
    (microsoft.public.access.queries)
  • Re: report returns Field ID instead of value
    ... Check on your report ... when I run the query normally, the datasheet returns the correct name so I ... CustomerID is a number. ... If you look at TblOrder, ...
    (microsoft.public.access.queries)
  • Re: Custom Parameter Dialogs for Reports
    ... "Review Date" as a parameter for a query that the report is based on. ... Let's assume it is a CustomerID number you need as criteria, ... Hide the CustomerID field by setting the Combo box's ColumnWidth ...
    (microsoft.public.access.reports)
  • Re: Bold Format Only Part of a Text Box Control
    ... You can use the Print method of the report in the On Format event. ... the CustomerID and CompanyName would need to be ... Me.Print Me.CompanyName 'must be bound control ...
    (microsoft.public.access.reports)
  • Re: sql query which has got 3 dynamic parameters
    ... CustomerID | FirstName | LastName | TestResult ... pass that 'accounting', 'fundstructures' etc and not hard code it. ...
    (comp.databases.ms-sqlserver)