can we make this smart query? (1 Viewer)

ariansman · Nov 13, 2012

There is a table where we record student name, lecture subject and presenting date since last year.
We need a query to show all the students who have made a lecture. We want the query to remove a lecturer who has presented less than a month on the same topic. For example, John made a lecture on politics on 1st August and then he again presented the same lecture on 15 August. In this case we want the second record not to be shown. However if he presents again on politics on 2nd of September it will be shown on the query.
Thank you,

plog · Nov 13, 2012

Can you provide sample data (along with field names and the table name) from your table, and then what you want the results of the query to be based on that sample data?

rodmc · Nov 13, 2012

You could use an IIF statement to check to see if there were other lectures in the same month.

Kind of like taking the 2 dates, date format to show the month and subtract one from the other, if the answer is 0 then both are in the same month and one can be discounted.

Not really sure how you could do this if there were more than 2 lectures in the month unless you use the earliest lecture in the month for all calculations (maybe loop through the lecture dates for the particular month perhaps?????)

ariansman · Nov 14, 2012

table name: student lectures
fields: ID, studentname, lecture, date
records:
1, john, politics, 1/1/12
2,steve, politics,20/1/12
3, jack, economics,21/1/12
4,jack,politics,22/1/12
5,john,economics,22/1/12
6,john, politics, 25/1/12
7,john,politics,3/2/12

based on our definition the query will show all the records except the 6th one.

plog · Nov 15, 2012

I need some clarification on your definition. Do you want the first lecture on a topic by a student in a month, or the first lecture within 30 days of the last lecture by the same student on the same topic?

Here's an example that should tell me exactly what I need to know:

1,john, politics, 1/1/12
2,john, politics, 31/1/12
3,john, politics, 1/2/12
4,john, politics, 1/3/12

What should your query produce as the result for that data?

ariansman · Nov 16, 2012

the query shows, studentname, lecture, and date. but if a student presents the same subject in less than 30 days, the query will not show that record.
if this query works on your example, we will see:
1,john, politics, 1/1/12
2,john, politics, 31/1/12
4,john, politics, 1/3/12
the third one is not shown because the same student presents the same topic less than 30 days from the last time
this lecture was provided.
thank you
.

plog · Nov 16, 2012

I was afraid of that. I'm not convinced this can't be done with just some queries, but the easiest method I see is using VBA. So that's what I did.

First though, I renamed your field named 'Date' to 'lectureDate' because Date is a reserved word in Access and might cause some issues with the VBA that will be required to use my method. So be sure you call your field 'lectureDate'.

Next, you need to copy your table called 'student lectures' and paste just its structure, naming this new table 'MonthlyLectures'. This is where the data you want is going to end up.

Then, create a query with this SQL code and name it 'MonthlyLectures_sub':

Code:

SELECT [student lectures].ID, [student lectures].studentname, [student lectures].lecture, [student lectures].lectureDate
FROM [student lectures]
ORDER BY [student lectures].studentname, [student lectures].lecture, [student lectures].lectureDate;

This query puts every record in the correct order so that the VBA can process it easily. This is the VBA you will need to process the data and output your results to the 'MonthlyLectures' table:

Code:

Function CompileMonthlyLectures()
Dim rsfrom As DAO.Recordset                 ' holds tables queries to copy from
Dim f As DAO.Field                          ' will hold field names when copying
Dim odate, ndate As Date                    ' holds prior used and current records date
Dim ostudent, nstudent As String            ' holds prior used and current records student data
Dim olecture, nlecture As String            ' holds prior used and current records lecture
odate = CDate("1/1/1900")
ostudent = "xxx"
olecture = "xxx"
    ' loads fake lecture data into old variables so that first record will always be used
Set rsfrom = CurrentDb.OpenRecordset("MonthlyLectures_sub")
  ' sets from source to the process_15_expand_DAYS query which identifies records with multiple DAYS
DoCmd.RunSQL "DELETE * FROM MonthlyLectures"
    ' truncates MonthlyLectures table so it can hold new data
rsfrom.MoveFirst
Do Until rsfrom.EOF
  ' loops through every record in query and adds appropriate amount of records to Master table
    
    invalid = 1
        ' determines if current record should be inputted, by default it will not
    ndate = rsfrom.Fields("lectureDate")
    nstudent = rsfrom.Fields("studentname")
    nlecture = rsfrom.Fields("lecture")
    ' gets current records data from record set
    
    If nstudent <> ostudent Or nlecture <> olecture Then invalid = 0
    If nstudent = ostudent And nlecture = olecture And DateDiff("d", odate, ndate) > 30 Then invalid = 0
    
    If invalid = 0 Then
    ' current record is valid and will be added to output table
        SQL = "INSERT INTO MonthlyLectures (ID, studentname, lecture, lectureDate) VALUES ("
        SQL = SQL & rsfrom.Fields("ID") & ", '" & nstudent & "', '"
        SQL = SQL & nlecture & "', '" & ndate & "');"
    ' compiles SQL statement to insert current records data into table
        DoCmd.RunSQL (SQL)
        odate = ndate
        ostudent = nstudent
        olecture = nlecture
    ' loads current record into old data variables for next records testing
        End If
        
    rsfrom.MoveNext
    Loop
 

DoCmd.OpenTable "MonthlyLectures"
End Function

Paste the above into a module, save it and then run it by either creating a macro or placing the code on a form. When you run that code it will populate and then open the MonthlyLectures table with the data you want.

ariansman · Dec 31, 2012

dear plog,

Thank you very much for your time and attention,
Sorry I have been too late, i hope you still remember this case,
Well, i did them all,
I saw this errors when the module is played:
run-time error ‘94’, Invalid use of Null,
then the error points to this statement: “nstudent = rsfrom.Fields("studentname")”
Then I deleted the statement that is now yellow highlighted in the module to see if it works, and it did. It was great. I was surprised how it is working despite i deleted that statement.
Moreover, it first warns me that I am going to delete some rows and then it repeatedly asks me to approve to append 1 row to the table. If there are about 200 rows i will always be keeping YES YES YES.. . . . Can we make a query instead of a table?
How can we solve them.

thank you very much and HAPPY NEW YEAR

ariansman · Jan 6, 2013

i could solve both problems. tnx

ariansman · Jan 8, 2013

anybody here to still help me?

ariansman · Jan 11, 2013

how is this mudle error solved ?

i still see the same error

plog · Jan 11, 2013

What error are we talking about? The null student name? If so, replace the offending line with these two:

nstudent=""
if (isnull(rsfrom.Fields("studentname"))=False Then nstudent = rsfrom.Fields("studentname")

For the constant errors popping up about appending or updating data, you need to set the options in Access to allow you to make changes to your data without prompting. I believe its in Client Settings, you want to uncheck all the options that say 'Confirm Updates', 'Confirm Deletions' etc.

ariansman · Jan 11, 2013

thank you very much,
can you please give me more help?
please imaginethe table 'student lectures' has more fields like , "studenthight" or "fathersname". These do not contribute to any condition but we want them to be put into the resultant table: 'MonthlyLectures'. how can we do this?

plog · Jan 11, 2013

You will need to add the fields you want in MontlyLectures to the MonthlyLectures_sub query, make sure the table MonthlyLectures has those fields in it and then reconfigure the VBA to insert that data into MonthlyLectures as well.

ariansman · Jun 21, 2013

Dear plog,Thanks a lot, for your very constructive adviceWell, now I am facing a new problem,I want to narrow the result of the created table only to lectures presented by a specific student, for example "jack". So I made a Form named studentselect where I can select a student name from a drop list and then put [Forms]![studentselect]![studentname] as a criteria in the studentname field of query MonthlyLectures_sub. But when I run the module it shows an error and says: “run time error ‘3061’: too few parameters. Expected 1” and by debugging it points to Set

Code:

rsfrom = CurrentDb.OpenRecordset("MonthlyLectures_sub")

However when I put the student name directly into the above query the module works fine. It seems the problems pops only when the criteria from the query is referred to a form. Can this be solved?thank you

plog · Jun 22, 2013

Make a new post in the forums section. I'm not the greatest working with forms.

ariansman · Jun 24, 2013

Dear plog,
Your answer to the main/first question was the best and helped me a lot so far. Thank you.
You created a query named MonthlyLectures_sub to put records in the correct order to be used by VBA. Now i need to limit this query to a student name. I directly put the student name in the criteria of studentname of the query and everything ran fine. Then i put [Forms]![studentselect]![studentname] in the criteria of studentname, in order to get the studentname from a form, instead of writing it directly in the criteria. Now, if i click the query it still runs fine with a sane result, but the VBA can no longer use this query and shows the error that i mentioned.
In fact my question is not about a form, but how to make VBA to still use the query and make the table that we want.

can we make this smart query? (1 Viewer)

ariansman

Registered User.

plog

Banishment Pending

rodmc

Registered User.

ariansman

Registered User.

plog

Banishment Pending

ariansman

Registered User.

plog

Banishment Pending

ariansman

Registered User.

ariansman

Registered User.

ariansman

Registered User.

ariansman

Registered User.

plog

Banishment Pending

ariansman

Registered User.

plog

Banishment Pending

ariansman

Registered User.

plog

Banishment Pending

ariansman

Registered User.

Similar threads

Users who are viewing this thread