View data into Columns per 20 Records

[gstylianou]

Hello my friends,

I have the Patients table with 80 records inside and i need to make one form in order to view some filtered records within 4 columns per 20 records.

Now i'm using Datasheet Form but must constantly do Scroll Up / Down.

I believe that this can be achieved with a code, but It's out of my knowledge

thanks in advanced

 

[plog]

A Report is best used if you just want to view data. More importantly you're talking like your working with a spreadsheet. Your image supports this. Databases and spreadsheets are two different animals.

Can you better explain what your data is and tell us what each column represents? Field names would be helpful as well. Lastly tell us what the ultimate goal is--what will creating this form\report allow you to do?

 

[gstylianou]

A Report is best used if you just want to view data. More importantly you're talking like your working with a spreadsheet. Your image supports this. Databases and spreadsheets are two different animals.

Can you better explain what your data is and tell us what each column represents? Field names would be helpful as well. Lastly tell us what the ultimate goal is--what will creating this form\report allow you to do?

Hi, i just upload the spreadsheet image just ti give you to understand exactly how i want to work with my data into form, not in report.

Thanks again

 

[Uncle Gizmo]

This is pretty close to what you want although the data is spread out over several subforms adjacent to each other. The data is all from one table and the number of records in each subform is controlled by the code.

In your case, you want to do something similar but instead of dividing the data amongst 3 subforms, you want to to display the data on one subform. You need to look up "Pagination" and apply it to the basic principles in this example:-

Split a Large MS Access Datasheet in to 3 Smaller Datasheets - Nifty Access

Split a Large MS Access Datasheet in to 3 Smaller Datasheets You've got a large "VERTICAL" List of Data in datasheet form, you might prefer to have it spread out across the screen instead of constantly having to page up and down the list. In this set of videos I demonstrate two ways of implementing

www.niftyaccess.com

 

[gstylianou]

Thanks for the solution you suggest but that doesn't help.
I want to repeat from the beginning what is required.

I have daily entries in the "Patients" table. Each record can be set to [Active] = True or False, depending on the filtered data. Keep in mind that the maximum number of active entries is 80, because some data is deleted daily.

Based on the above, I believe that using VBA (if possible) we can do the following steps:
1. The first step is to count all entries from "Patients" that have the field [Active] = True.
2. Dynamically we can create 4 queries which each shows only 20 entries.

Can we do something?

 

[vba_php]

Can we do something?

do you know how to filter records on a form? there's a button through the interface. there's code.

in terms of you NOT wanting to scroll, you don't have a choice. a screen is only SO big, vertically. and that means that you HAVE to scroll, regardless if you are scrolling the form window, or the dataset's window. either way, you're stuck doing it. you're stuck doing it even if you want to put 4 queries, 20 records each, one above the other. that would result in you having to scroll up and down in the FORM's window, not in the queries themselves.

in terms of code, and filtering records using it, you will want to look up these properties of the form object:

form.filter

form.filter = true/false

post back if you need more than this.

 

[gstylianou]

do you know how to filter records on a form? there's a button through the interface. there's code.

in terms of you NOT wanting to scroll, you don't have a choice. a screen is only SO big, vertically. and that means that you HAVE to scroll, regardless if you are scrolling the form window, or the dataset's window. either way, you're stuck doing it. you're stuck doing it even if you want to put 4 queries, 20 records each, one above the other. that would result in you having to scroll up and down in the FORM's window, not in the queries themselves.

in terms of code, and filtering records using it, you will want to look up these properties of the form object:

form.filter

form.filter = true/false

post back if you need more than this.

Hi vba_php,

As i told the problem is not on how can filter the data..My problem is how can take 20 records only (from 80) on Query, 2,3, and 4 using vba..

Thanks

 

[vba_php]

As i told the problem is not on how can filter the data..My problem is how can take 20 records only (from 80) on Query, 2,3, and 4 using vba..

this is not a standard way to do things, i gotta tell ya. but if what you're asking is how to PULL 20 records each from the FIRST query that you created and display them as 4 different query datasets on the form, you can do it 2 different ways:

1) create 4 different subform controls through the screen interface, then set the recordsource of each with code:

me.subform_control.form.recordsource = "query statement here"

2) write 4 different queries, and put some criteria in the WHERE clause regarding your ID FIELD. like this:

query 1:

select * from table where ID_field > 0 and ID_field < 20 (or this):
select * from table where ID between 0 and 20

query 2:

select * from table where ID between 21 and 40

etc, etc...

the of course, all you have to do is set the recordsource of your subform controls to every one of those queries.

follow?

 

[cheekybuddha]

Hi,

Can you use the solution provided in this post in another thread?

RecordSource of first subform:
SELECT TOP 20 t.* FROM YourTable t;

RecordSource of second subform:
SELECT TOP 20 t.* FROM YourTable t WHERE ID NOT IN (SELECT TOP 20 t1.ID FROM YourTable t1);

RecordSource of third subform:
SELECT TOP 20 t.* FROM YourTable t WHERE ID NOT IN (SELECT TOP 40 t1.ID FROM YourTable t1);

RecordSource of fourth subform:
SELECT TOP 20 t.* FROM YourTable t WHERE ID NOT IN (SELECT TOP 60 t1.ID FROM YourTable t1);

hth,

d

 

[gstylianou]

this is not a standard way to do things, i gotta tell ya. but if what you're asking is how to PULL 20 records each from the FIRST query that you created and display them as 4 different query datasets on the form, you can do it 2 different ways:

1) create 4 different subform controls through the screen interface, then set the recordsource of each with code:

me.subform_control.form.recordsource = "query statement here"

2) write 4 different queries, and put some criteria in the WHERE clause regarding your ID FIELD. like this:

query 1:

select * from table where ID_field > 0 and ID_field < 20 (or this):
select * from table where ID between 0 and 20

query 2:

select * from table where ID between 21 and 40

etc, etc...

the of course, all you have to do is set the recordsource of your subform controls to every one of those queries.

follow?

Thanks, i will try.

Another one issue is how can set the criteria if the field [Activate] = True on dynamic queries ?

(sorry, i'm not so familiar with vba)

 

[vba_php]

Another one issue is how can set the criteria if the field [Activate] = True on dynamic queries ?

that's in the WHERE clause:

WHERE [activate] = true

you might also want to try cheeky's post. the word TOP can be used in a situation like this too:

https://www.access-programmers.co.uk/forums/threads/view-data-into-columns-per-20-records.310725/post-1684702[/code]

 

[moke123]

I would be surprised if vba_php's code works as I doubt your records would always have an ID field of 1 through 20.
In addition to a where clause you may also wish to apply an order by clause.

 

[Uncle Gizmo]

I doubt your records would always have an ID field of 1 through 20.

If that's the case you can add a number... See:- NumberedQuery.mdb (beginner)

http://www.rogersaccesslibrary.com/forum/topic309.html

Explination HERE:-

Domain Function Examples: Numbered Query With DCount

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

 

[vba_php]

I would be surprised if vba_php's code works as I doubt your records would always have an ID field of 1 through 20.

i'm surprised you people are acting like richard. bash me in the watercooler, not here. there's been enuf hijacking.

 

[The_Doc_Man]

Adam, whenever you post bad advice, you are fair game to get bashed.

 

[vba_php]

it wasn't bad advice grandpa