Can you group records within a continuous form?

[OllyJ]

Hi guys, probably a basic question but have been struggling for some time so thought i'd see if you could help.

I have a main schedule table which contains the fields...
Date; Machine; Arm/Head; Job Number; Stock Code; Operator; QtyPred; QtyAct

My ideal form would be a continuous one (so I can easily search through a selection of dates eg. 01/12/2008 - 09/12/2007) but need to have only one record per date. *** My problem is that the table contains many records per date. Is there any way of grouping the results of a table and then producing a continuous form based on these results.

NB I understand that I can create a qry to search machine; arm/head to LIMIT the results but then I wish to have a single record for a date containing up to 6 Job Numbers with their relevant 6 Stock Codes, 6 Operators, 6 QTYPred and 6 QTYAct.

I hope you can help me

Many thanks in advance, OllyJ

[Shalini Bhalla]

I havn't understud it properly but still , try to us subform

[OllyJ]

Thank you for your response but you cannot use subforms within a continuous form, well... at least I don't know a way round that!

[jaxjagfan]

If you are just looking for sorts, this can be done with a query as a datasource for the form. If you wish to edit these records you cannot use an aggregate query (groupby, sum, count, etc).

If you are looking to show a group of records together in a continuous form the make that the subform and make a parent form that drives the group.

Post more details of the dataset in question for all to see and/or a more detailed description of what you are trying to do.

[NeoPa]

My instinct would also be to use a subform, but if that's not possible then how about triggering (button or double-click whatever) a procedure to open a separate form related to the selected item from the main form's GROUP BY query.
The main form would have dates as single records and the separate form would be a continuous form of the items on the selected date.

[OllyJ]

Thank you for all of your timely responses.

I know what I am asking for are quite specific requirements so I shall try to explain them in more detail. (If you could add subforms to continuous forms I wouldn't have a problem at all!)

Fields in tbl_schedule are: *Cannot be changed*
Date; Machine; Arm/Head; Job Number; Stock Code; Operator; QtyPred; QtyAct

EG: 2 records in table:
1. 2.
Date: 07/01/2008 Date: 07/01/2008
Machine: MC1 Machine: MC1
Arm/Head: Arm1 Arm/Head: Arm1
Job No: 1 Job No: 2
Stock Code: PART001 Stock Code: PART002
Operator: OJ Operator: OJ
QTYPred: 12 QTYPred: 12
QTYAct: 0 QTYAct: 0

When setting out a form based on this table, 'Machine'; 'Arm/head' can be given before opening form / chosen from header to limit results.

The highlighted fields - job no and stock code vary and there can be up to 6 different job numbers / stock codes whilst the other results remain the same.

I need the results to be grouped by date so that 1 record in the form would contain (on the far left of the form) 07/01/2008 and then for record 1: 'Job No'; 'Stock Code'; 'Operator'; 'QTYPred'; 'QTYAct' would appear in the form (in editable format) as a group that would form "column 1" and record 2 form "column 2", moving right horizontally across the page. Thus meaning 1 record contained 6 groups of information for each date.

These layout requirements are to match the current method of scheduling. If that could be kept it would be great. The important thing is that it is all kept within the same form.

I hope this helps you understand which in turn helps you to hopefully help me! :)

Many thanks in advance, OllyJ

[OllyJ]

My last post might have confused some? I am still struggling with this so hope to solve it ASAP if anyone can help?

I would like to attach a basic mdb file with 1 table and 1 form to explain what im struggling with clearer but not sure how to attach a file? I don't have a browse button or alike?

Hope you can help, OllyJ

[jaxjagfan]

Thank you for all of your timely responses.

I know what I am asking for are quite specific requirements so I shall try to explain them in more detail. (If you could add subforms to continuous forms I wouldn't have a problem at all!)

Fields in tbl_schedule are: *Cannot be changed*
Date; Machine; Arm/Head; Job Number; Stock Code; Operator; QtyPred; QtyAct

EG: 2 records in table:
1. 2.
Date: 07/01/2008 Date: 07/01/2008
Machine: MC1 Machine: MC1
Arm/Head: Arm1 Arm/Head: Arm1
Job No: 1 Job No: 2
Stock Code: PART001 Stock Code: PART002
Operator: OJ Operator: OJ
QTYPred: 12 QTYPred: 12
QTYAct: 0 QTYAct: 0

When setting out a form based on this table, 'Machine'; 'Arm/head' can be given before opening form / chosen from header to limit results.

The highlighted fields - job no and stock code vary and there can be up to 6 different job numbers / stock codes whilst the other results remain the same.

I need the results to be grouped by date so that 1 record in the form would contain (on the far left of the form) 07/01/2008 and then for record 1: 'Job No'; 'Stock Code'; 'Operator'; 'QTYPred'; 'QTYAct' would appear in the form (in editable format) as a group that would form "column 1" and record 2 form "column 2", moving right horizontally across the page. Thus meaning 1 record contained 6 groups of information for each date.

These layout requirements are to match the current method of scheduling. If that could be kept it would be great. The important thing is that it is all kept within the same form.

I hope this helps you understand which in turn helps you to hopefully help me! :)

Many thanks in advance, OllyJ

Looks like you want a primary form (single record) displaying a single Machine-Arm/Head combination and a subform (continuous records) that displays your table details in ascending order by date. The primary form would not be editable data.

Main form:

SELECT tbl_schedule.Machine, tbl_schedule.[Arm/Head]
FROM tbl_schedule
GROUP BY tbl_schedule.Machine, tbl_schedule.[Arm/Head]
ORDER BY tbl_schedule.Machine, tbl_schedule.[Arm/Head]

Sub form:

SELECT tbl_schedule.*
FROM tbl_schedule
ORDER BY tbl_schedule.[Date],tbl_schedule.[Job No]

Link the subform to the main form on Machine and on Arm/Head. As you navigate through the main form the subform will display the details in date order and you can edit/add records. You dont need to display the Machine and Arm/Head fields in the subform (just include them in the recordset)

If you need to add new machines and/or arm/heads I recommend using another form.

Hope this helps,

[OllyJ]

Thank you, I think i understand that... but im now having issues with layout and a specific layout is required from the final user. I have this layout shown clearly on an unbound form, is there a way to attach a small database on here?

[OllyJ]

Think this should contain attachment... never done it before!

this should help explain.... any help will be greatly appreciated on how i could achieve this layout and still allow editable controls that relate to specific records in an underlying table.

Any help will be greatly appreciated! Thanks, OllyJ

[jaxjagfan]

Think this should contain attachment... never done it before!

this should help explain.... any help will be greatly appreciated on how i could achieve this layout and still allow editable controls that relate to specific records in an underlying table.

Any help will be greatly appreciated! Thanks, OllyJ

Thanks - this will help. You may have to be more flexible with the layout. You will not be able to lay the records side by side as you have displayed. I will mockup a form and repost.

Revised version uploaded here.
Check out the main and sub forms. I added a few records to test. The sub opens within the main form. Use the combo box to navigate.

I noticed you had labels for day and night. Within the DateAdded field, are the users entering date and time? Or should the day/night field be editable as well (I have it so in my sub).

This is very rudimentary but it should get you past your current dilemma.

[jaxjagfan]

Check out my previous revised post and upload.

[OllyJ]

Thanks very much for your help. I have looked through your revised version, it is something I shall definitely consider using. However, before i commit, are you (or any expert friends) aware of a method of getting the results side by side no matter how complex the code? I appreciate that this is a big ask but I have been asked to try and do everything I possibly can to get it into this format so that it is the same layout as the spreadsheet being used currently.

If you can help with that it'll be great (even if it means using a single form somehow / adjusting the table slightly maybe?)

If this is impossible, I am pretty sure I shall use the method you sent so thank you once again.

NB Day/Night is not chosen just presented on the form.

OllyJ

[OllyJ]

Jaxjagfan... I have been thinking and with some moderations, I think I could run with the method you kindly showed me.

Here is a list of 'ideals' that I would need in place in order to use the continuous subform, I have been looking at some of these today and have struggled. If you could assist me in any way with these it'd be great...

1. User needs to filter numerous times from controls within the header of the main form i.e. Filter the date down and then from what's remaining, filter again, and again ... is this possible?

2. The first filter would need to be date so from the header in the drop down box there could be a combo with 'this week'; 'next week'; 'this month' etc. when selected the subform should filter.

When doing week do you do 'between'?...as I know that
- First of week =Datediff("d",Weekday(Date()-1,2),Date())
- Last of week =Datediff("d",Weekday(Date()-1,1),Date())+6

3. The following controls would limit the list of results within the continuous form further, i.e. 'machine'; 'arm/head'; 'operator' etc.

You can probably see where i'm going with this. My problem is that i'm struggling with the basics of limiting the continuous subform from the header of the main form... and i'm also finding the coding of the dates a particular struggle.

Again, any help on this will be greatly appreciated.

OllyJ

[jaxjagfan]

The easiest way to implement filters is to train the end users about the capabilities of MS Access. I taught some of my end-users about that here at work and they loved it. For some it has become the predominate way they drill down to the data they want to see.

If you right-click on a field (in a form or in a table), one of the options you will see is filter by selection (in the case of your date in the subform - it would narrow down (filter) the list to just the date selected.

Another option from right-click is "Filter For:". This allows the end-user to enter custom criteria such as >=1/1/2008 or Between 1/1/2008 and 1/5/2008.

To remove filters, just right click again and choose "Remove Filter/Sorts".

This will remove ALL filters - not just the last one done.

[OllyJ]

I understand what you're saying regarding teaching them about how to use access and filtering and have previously put that forward (as I knew it would save me time) but i've been told i need to reduce the end user's scheduling time as much as possible, hence unfortunately i do need to limit from the header of the main form.

Any ideas...?

Do you set filters to the continuous subform and then on update of a combo box set if (x is selected) then me.subform.filter = true????