Three groups of data required in a month (1 Viewer)

[captgnvr]

D/All

For example: first batch joined on 10th, second batch on 20th and third batch on 30th of the month.

In each batch certain persons will also leave.

What is required is:

1. query for full persons list as on 9th

2. query for those who joined on 10th, another query when second batch and final query after third batch join. So first query to have only the first batch. second batch query to have first n second batch. and when third batch join the query to have all first, second and third batch.

What is most important is to have the three batches sorted out according to their seniority number in such a way, when the second batch or when the third batch list is made, the each batch to be sorted out according to the seniority number and as per the date joined group.

Is it possible to select the three dates in a calendar control and pass it on to the query?? or any professional way pls sugest.

Can anyone guide me??

 

[Trevor G]

Can you upload an extract of your data so I can work with it. You would need something like this.

Query 1
Select date field and other fields then in the criteria <10/month/year

Query 2
Select date field and other fields then in the criteria >10/month/year <20/month/year

Query 3
Select date field and other fields then in the criteria >20/month/year <31/month/year

If you then want to run them from a form with an option to use all three criteria, use a combo box which will display the dates and set that as the criteria.

If I miss the mark, then upload a sample of your data

 

[captgnvr]

D/TREVOR
Thanks for the guidance. I have uploaded the file for your scrutiny. Actually I am in midst of figuring out how to use staff data and stores data to get sales made to staff report and stock balance report. From the main file I have copied only the tables meant for making this sales report which is posted as my thread in -access-general- forum.

I got the idea of three queries and once i make the three queries, do i have to make another query to join all the three??

Also pls if u can find out why I am unable to select the names or the products in my attached file?? What I aimed is to input the purchases against the selected staff name and the sales thus done should be updated into the sales table. So pls see if u could find out and guide me why I an unable to select the name nor the store items in the form to enter sales data??

Thanks and looking forward to -- brgds/captgnvr

 

[Trevor G]

D/TREVOR
Thanks for the guidance. I have uploaded the file for your scrutiny. Actually I am in midst of figuring out how to use staff data and stores data to get sales made to staff report and stock balance report. From the main file I have copied only the tables meant for making this sales report which is posted as my thread in -access-general- forum.

I got the idea of three queries and once i make the three queries, do i have to make another query to join all the three??

Also pls if u can find out why I am unable to select the names or the products in my attached file?? What I aimed is to input the purchases against the selected staff name and the sales thus done should be updated into the sales table. So pls see if u could find out and guide me why I an unable to select the name nor the store items in the form to enter sales data??

Thanks and looking forward to -- brgds/captgnvr

First suggestion,

Create a results table (you can use the first query to use a make table here) which holds the data from the 3 queries about selecting the date data, then use an append query to append the records to the results table.

I would use a form so you can select the dates as per each month as you move forward and then append the results, you then have historical data. You can then use the results table and sort the data in a report or form setting the options you need.

Second - Your form F_SALES_TRIAL

I think the reason why you can't select from the combo box is that you are using the record source, you could use a form which is not bound to the table, then use the combo box wizard to find your NAME field data, this will allow you to then pick your data. Then you would need to use aq requery to the subform to show the found name and the results.

 

[captgnvr]

D/Trevor, thanks for the step by step for handling the different date group data. With regard to the combo box have corrected it and have to now assimilate all into my project. I might need about a week to first make those tables of staff, stores and sales and then get to the various staff joined for the month. Will update once I come to that stage n thanks for the guidance. From urs and another 'hitechcoach' I got quite a lot directions and headway.