Office & Productivity Software forum

General discussion

access - help!

by mokibaby / February 5, 2006 11:13 AM PST

I am working with Access 2000 and am trying to get some data out of my database. Can anyone tell me how to create a report that will contain every 5th record? I am new to access and base on the little I know, I believe I have to use a query but how to go about it? Can anyone please help me?

Thanks..

Discussion is locked
You are posting a reply to: access - help!
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: access - help!
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Collapse -
Re: every fifth record
by Kees Bakker / February 5, 2006 4:52 PM PST
In reply to: access - help!

That's a problem, more or less.

There's a fundamental problem, and that's that MS Access is a relational database. And the concept of a 'set' in that theory implies there's no sequence, so you can't speak of each fifth. So you have to impose a sequence yourself.

What I should do:
1. Make a copy (structure only) of the table.
2. In the copy, remove all primary key fields, and add a new field (type Counter) as new primary key.
3. Copy the old table into the new one (with a Add-query)
4. Make a query on the new table selecting all records where the last digit if the ID = 1 or 6 (use the mod operator).

You might need some experimenting and research, but this is the essence.

Hope this helps.


Kees

Collapse -
every fifth record
by mokibaby / February 6, 2006 2:07 AM PST
In reply to: Re: every fifth record

Kees,

If you don't mind, can you please go a little more in detail? How is it different between a primary key (i.e. the record #) and the counter?
Can I get the every fifth record if the data is in excel instead of access (would it be easier?)

Thanks....

Collapse -
Re: every 5th
by Kees Bakker / February 6, 2006 5:08 AM PST
In reply to: every fifth record

If there already IS a field with consecutive integers in the file there is no need to make one, of course. But if there isn't a counter is a nice way to make such. There's no such thing as a 'record number', although - of course - you can read the table in VBA and count from 1. But I would say that making a new table from the GUI is easier than write a VBA-module accessing record sets.

And yes, you can do this in Excel. Record a macro that deletes 4 rows, then goes down 1. Assign it to a keyboard shortcut (like ctrl-shift-X) and execute it as often as you need. That works well up to about 1000 records (press ctrl-shift-X 200 times), but I wouldn't really do it for 100,000 records (press the keys 20.000 times).

I agree with Richards post below that the request is unusual. So is the solution, because there is no regular one. You might like to change the requirements.

Hope this helps.


Kees

Collapse -
every 5th record?
by richardp123 / February 6, 2006 4:23 AM PST
In reply to: access - help!

Perhaps you can explain in more detail why it is that you need your report to contain every 5th record, as this is quite an unusual request. It may be that there is an alternative solution.

Collapse -
every fifth record
by mokibaby / February 9, 2006 8:07 AM PST
In reply to: every 5th record?

To elaborate:

it is a database that consist of 1000 records, I would like to get a random sample of about 200 records to conduct a survey. Therefore, the question is how do I get every fifth records from the 1000 records? I need to get it on a report format so that I can print the data to use.

Thanks...

Moki

Collapse -
Re: every fifth ...
by Kees Bakker / February 9, 2006 4:25 PM PST
In reply to: every fifth record

That's a random selection indeed. But there can be other solutions about as reliable. Anyway, nobody will be able to tell that it's no good.

- If there's a name in the record (first name, last name, doesn't matter) sort on say the third character of that name and take the upper (or lower, or middle) 200.
- If there's a birthtdate in the record, use everybody born in may or october.
- If there's an address in the record, sort on house number or fifth character of city.

All of this is assuming that the sort item (month born, symbol in name) doesn't influence the behavior you want to study in the sample.

If you use the selection based on a single character in the name, then resort on custumer number, my guess is that your customer won't notice how you did it. And even if he notices there's no real objection to it.

Kees

Popular Forums

icon
Computer Newbies 10,686 discussions
icon
Computer Help 54,365 discussions
icon
Laptops 21,181 discussions
icon
Networking & Wireless 16,313 discussions
icon
Phones 17,137 discussions
icon
Security 31,287 discussions
icon
TVs & Home Theaters 22,101 discussions
icon
Windows 7 8,164 discussions
icon
Windows 10 2,657 discussions

FALL TV PREMIERES

Your favorite shows are back!

Don’t miss your dramas, sitcoms and reality shows. Find out when and where they’re airing!