is this a form problem?

  • Thread starter Thread starter ben
  • Start date Start date
B

ben

I have been wrestling with this problem for the past
couple weeks, and I can't seem to get a solid answer from
anyone I talk to...hopefully one or more of you guys can
here.

I have a total of four tables, each with several fields
1.) tblShaftdescriptiondata
Counter #
Type
Manufacturer
Model
Date/Cost

2.) tblShaftpropertydata
Counter #
Sample #
CPM 0
CPM 90
....21 other fields

3.) tblFrequencyprof
Counter #
CPM @ location 1
CPM @ location 2
.....CPM @ location X...

4.) tblTorsionprof
Counter #
deg @ location 1
....deg @ location X...

From these tables, I have a query that averages values
from tables 1.) and 2.)

I currently have several hundred records, with thousands
more on the way...

What I have been trying to find out is if it's possible
to be able to go through the records, select a set number
of records (this will not be possible by filtering and/or
querying), and have those selected records pass on to a
prepared report.

I think I have the report part figured out, but trying to
get the query information to a point where I can SELECT
the records I want.

I realize this is a long post, but I am trying to be as
clear as I can.

Thank you for any help, and for your patience reading
this post.

Thanks again
Ben
 
Your advice did limit the records to the number I set in
the properties menu(6)...but the results aren't what I am
looking for...

Here is the SQL of my query:

SELECT DISTINCTROW [Shaft Description Data - BEN].
[Yes/No], [Shaft Description Data - BEN].[Shaft Mfg],
[Shaft Description Data - BEN].[Shaft Description],
[Shaft Description Data - BEN].Flex, Last([Shaft Property
Table - BEN].Counter) AS [Counter], Avg([Shaft Property
Table - BEN].[Fuji CPM -0-]) AS [Avg Of Fuji CPM -0-], Avg
([Shaft Property Table - BEN].[Fuji CPM -90-]) AS [Avg Of
Fuji CPM -90-], Avg([Shaft Property Table - BEN].[Brun
CPM -0-]) AS [Avg Of Brun CPM -0-], Avg([Shaft Property
Table - BEN].[Brun CPM -90-]) AS [Avg Of Brun CPM -90-],
Avg([Shaft Property Table - BEN].[Tip ID]) AS [Avg Of Tip
ID], Avg([Shaft Property Table - BEN].[Butt ID]) AS [Avg
Of Butt ID], Avg([Shaft Property Table - BEN].[Tip OD -0-
]) AS [Avg Of Tip OD -0-], Avg([Shaft Property Table -
BEN].[Butt OD -0-]) AS [Avg Of Butt OD -0-], Avg([Shaft
Property Table - BEN].[MOP Flex @ 0]) AS [Avg Of MOP Flex
@ 0], Avg([Shaft Property Table - BEN].[MOP Flex @ 90])
AS [Avg Of MOP Flex @ 90], Avg([Shaft Property Table -
BEN].Weight) AS [Avg Of Weight], Avg([Shaft Property
Table - BEN].Torque) AS [Avg Of Torque], Avg([Shaft
Property Table - BEN].[Tip Def]) AS [Avg Of Tip Def], Avg
([Shaft Property Table - BEN].[Butt Def]) AS [Avg Of Butt
Def], Avg([Shaft Property Table - BEN].[Pur Length]) AS
[Avg Of Pur Length], Avg([Shaft Property Table - BEN].
[HST Tip]) AS [Avg Of HST Tip], Avg([Shaft Property
Table - BEN].[HST Butt]) AS [Avg Of HST Butt]
FROM [Shaft Description Data - BEN] RIGHT JOIN [Shaft
Property Table - BEN] ON [Shaft Description Data -
BEN].Counter = [Shaft Property Table - BEN].Counter
GROUP BY [Shaft Description Data - BEN].[Yes/No], [Shaft
Description Data - BEN].[Shaft Mfg], [Shaft Description
Data - BEN].[Shaft Description], [Shaft Description Data -
BEN].Flex;

What I want is to be able to select (preferably by way of
the check boxes) which records i can send to the
report...following your previous instructions give the
first six records.

The report will compare the selected shafts so I can find
the holes in our company's range of products...

Thanks for your help
Ben
 
Ben, I do not understand your question.

If you want to select by check boxes, you must have a check box in a table
that you can set to yes or no. In query design, you can then specify True in
the Criteria row under this field to limit the report to the ones where the
box was checked.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

ben said:
Your advice did limit the records to the number I set in
the properties menu(6)...but the results aren't what I am
looking for...

Here is the SQL of my query:

SELECT DISTINCTROW [Shaft Description Data - BEN].
[Yes/No], [Shaft Description Data - BEN].[Shaft Mfg],
[Shaft Description Data - BEN].[Shaft Description],
[Shaft Description Data - BEN].Flex, Last([Shaft Property
Table - BEN].Counter) AS [Counter], Avg([Shaft Property
Table - BEN].[Fuji CPM -0-]) AS [Avg Of Fuji CPM -0-], Avg
([Shaft Property Table - BEN].[Fuji CPM -90-]) AS [Avg Of
Fuji CPM -90-], Avg([Shaft Property Table - BEN].[Brun
CPM -0-]) AS [Avg Of Brun CPM -0-], Avg([Shaft Property
Table - BEN].[Brun CPM -90-]) AS [Avg Of Brun CPM -90-],
Avg([Shaft Property Table - BEN].[Tip ID]) AS [Avg Of Tip
ID], Avg([Shaft Property Table - BEN].[Butt ID]) AS [Avg
Of Butt ID], Avg([Shaft Property Table - BEN].[Tip OD -0-
]) AS [Avg Of Tip OD -0-], Avg([Shaft Property Table -
BEN].[Butt OD -0-]) AS [Avg Of Butt OD -0-], Avg([Shaft
Property Table - BEN].[MOP Flex @ 0]) AS [Avg Of MOP Flex
@ 0], Avg([Shaft Property Table - BEN].[MOP Flex @ 90])
AS [Avg Of MOP Flex @ 90], Avg([Shaft Property Table -
BEN].Weight) AS [Avg Of Weight], Avg([Shaft Property
Table - BEN].Torque) AS [Avg Of Torque], Avg([Shaft
Property Table - BEN].[Tip Def]) AS [Avg Of Tip Def], Avg
([Shaft Property Table - BEN].[Butt Def]) AS [Avg Of Butt
Def], Avg([Shaft Property Table - BEN].[Pur Length]) AS
[Avg Of Pur Length], Avg([Shaft Property Table - BEN].
[HST Tip]) AS [Avg Of HST Tip], Avg([Shaft Property
Table - BEN].[HST Butt]) AS [Avg Of HST Butt]
FROM [Shaft Description Data - BEN] RIGHT JOIN [Shaft
Property Table - BEN] ON [Shaft Description Data -
BEN].Counter = [Shaft Property Table - BEN].Counter
GROUP BY [Shaft Description Data - BEN].[Yes/No], [Shaft
Description Data - BEN].[Shaft Mfg], [Shaft Description
Data - BEN].[Shaft Description], [Shaft Description Data -
BEN].Flex;

What I want is to be able to select (preferably by way of
the check boxes) which records i can send to the
report...following your previous instructions give the
first six records.

The report will compare the selected shafts so I can find
the holes in our company's range of products...

Thanks for your help
Ben
-----Original Message-----
To "select a set number of records":
1. Create a query that combines the data from the tables.

2. In query design, choose the Properties for the query (View menu).

3. Set the Top Values property to the number of records you desire.

4. Use this query as the RecordSource for your report.

Hint: Define the sorting order so that it contains a unique key value after
the other sorting field(s). Otherwise Access may include 11 records when you
ask for 10 if the 10th and 11th are a tie.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")




.
Click to expand...
Click to expand...
 
Back
Top