Make Table on Start up - Autoexec?

[Yoplumpy]

Hi all

I have a 2007 database that on startup needs to replace a table of student attendance as this makes is easier to report on.

I am not the best at VBA!

I can easily set up an autoexec macro to do this but get the warnings messages. I tried to get around it by converting the Macro to a Module and editing is so:

Function autoexec()
DoCmd.SetWarningsFalse
DoCmd.OpenQuery "qryAllAttendanceData", acViewNormal, acEdit
DoCmd.SetWarningsTrue
End Function

I don't think it's working - can an autoexec function be a module rather than a macro?

Any help woule be great.

Cheers

Paul

 

[vbaInet]

I have a 2007 database that on startup needs to replace a table of student attendance as this makes is easier to report on.

Could you please elaborate more on this bit. When you say replace, what is the source of the "incoming" records?

 

[Yoplumpy]

Hi

The MakeTable is combining attendance from 3 other tables - they are all holding the same information (but for differing types of attendance) so Mark, Date, Date Month, Cohort etc.

It just makes one table of attendance by their ID.

Cheers

Paul

 

[vbaInet]

Making a table for every startup is ineffecient and will cause your database to bloat. If you have the right relationships and joins setup you should be able to combine all the necessary fields into a query instead of creating a table. Basically, if the records exist, you shouldn't be duplicating them.

Have you thought about pulling all this information using a query?

 

[Yoplumpy]

hi Thanks for your advice.

I had actually tried that before and was using a Union query to combine the data but had problems when I started using a form to set From and To parameters to report. This seemed to work so I stuck with it!

It's not anticipated that there will be a large volume of data - but I fully appreciate what you are saying. Any ideas why that would have happended?

Cheers

Paul

 

[vbaInet]

It's not anticipated that there will be a large volume of data - but I fully appreciate what you are saying. Any ideas why that would have happended?

It's not about the size but business needs change. In your circumstance you may need to use that data in other places (in the future) and with the current method it would be very difficult to do so.

Maybe if you give a brief explanation of what you're trying to do we might be able to come up with a way of reaching a solution using queries? What problems did you have?

And if it's not possible using queries, which would most likely not be the case, we could look other alternatives.

 

[Yoplumpy]

Right I will try

It a database that captures core details of young people and then they attend certain pre-education/education/mentoring/casework sessions which can be anything from 3 to 24 sessions for each but it depends.

I have a tabbed entry form with the main core fields coming through an subforms to capture the attendance data in separate tables - all of which link using the IDs which are in each table.

I am required to report on all Attendees for a given period (using a separate form to capture the Attendance Date) and also all Attendances plus Attendee Dempgraphics etc.

(This is very convoluted) As the date range wasn't working I created a Select UNION, then created a query that would Make a table of the data, then another Select query to use for my reports.

As I am sure you can tell - my talents do not necessarily lie in the programming camp!

UNION Query Below:

SELECT tblAttendance12Weekprogramme.FFPID, tblAttendance12Weekprogramme.[Attendance Date], tblAttendance12Weekprogramme.[Attendance Mark], Format([Attendance Date],"mmmm") AS [Month], Format([Attendance Date], "mm") AS [MonthNo]
FROM tblAttendance12Weekprogramme
WHERE (((tblAttendance12Weekprogramme.[Attendance Mark])="1"))
UNION ALL
SELECT tblAttendanceMentoring.FFPID, tblAttendanceMentoring.[Attendance Date], tblAttendanceMentoring.[Attendance Mark], Format([Attendance Date],"mmmm") AS [Month], Format([Attendance Date], "mm") AS [MonthNo]
FROM tblAttendanceMentoring
WHERE (((tblAttendanceMentoring.[Attendance Mark])=1))
UNION ALL
SELECT tblAttendancePathway.FFPID, tblAttendancePathway.[Attendance Date], tblAttendancePathway.[Attendance Mark], Format([Attendance Date],"mmmm") AS [Month], Format([Attendance Date], "mm") AS [MonthNo]
FROM tblAttendancePathway
WHERE (((tblAttendancePathway.[Attendance Mark])=1))
UNION ALL SELECT tblAttendancePrePathway.FFPID, tblAttendancePrePathway.[Attendance Date], tblAttendancePrePathway.[Attendance Mark], Format([Attendance Date],"mmmm") AS [Month], Format([Attendance Date], "mm") AS [MonthNo]
FROM tblAttendancePrePathway
WHERE (((tblAttendancePrePathway.[Attendance Mark])=1));

Make Table Query:
SELECT qryAttendancesUNION.FFPID, qryAttendancesUNION.[Attendance Date] AS Attendance, qryAttendancesUNION.Month, qryAttendancesUNION.MonthNo, qryAttendancesUNION.[Attendance Mark] AS Mark INTO tblAllAttendanceData
FROM qryAttendancesUNION;

Query used for reports:

SELECT tblAllAttendanceData.FFPID, tblAllAttendanceData.Month, tblAllAttendanceData.MonthNo, tblAllAttendanceData.Attendance, tblAllAttendanceData.Mark
FROM tblAllAttendanceData
WHERE (((tblAllAttendanceData.Attendance) Between [Forms]![frmDateRangeReport]![FromDate] And [Forms]![frmDateRangeReport]![ToDate]) AND ((tblAllAttendanceData.Mark)="1"));

No doubt I have confused things further...

Paul

 

[vbaInet]

Hmm.... I'm not sure why you have Mentoring, Pathway, 12 Week Programme and Pre Pathway split. You could have one field that is linked up to a table with all these values in it. How will the database change if say you had Special Needs? Would you go about creating another table?

Can I see a screenshot of your relationships?

 

[Yoplumpy]

Hi

Yes I am guessing a single table would make sense but there will also be reports that are excluding certain types of marks so I wasn't sure how to keep them separate.

I attach a screenshot - It's not huge as the size limit is only 100kb.

Cheers

Paul

 

[DCrake]

Union query SQL (QryAllAttendances)


Note in your post you had quotation marks around the 1 in the where statement in the first where criteria

SELECT FFPID, [Attendance Date], [Attendance Mark], Format([Attendance Date],"mmmm") AS [Month], Format([Attendance Date], "mm") AS [MonthNo]
FROM tblAttendance12Weekprogramme
WHERE [Attendance Mark]=1
UNION ALL
SELECT FFPID, [Attendance Date], [Attendance Mark], Format([Attendance Date],"mmmm") AS [Month], Format([Attendance Date], "mm") AS [MonthNo]
FROM tblAttendanceMentoring
WHERE [Attendance Mark])=1
UNION ALL
SELECT FFPID, [Attendance Date], [Attendance Mark], Format([Attendance Date],"mmmm") AS [Month], Format([Attendance Date], "mm") AS [MonthNo]
FROM tblAttendancePathway
WHERE [Attendance Mark])=1
UNION ALL 
SELECT FFPID, [Attendance Date], [Attendance Mark], Format([Attendance Date],"mmmm") AS [Month], Format([Attendance Date], "mm") AS [MonthNo]
FROM tblAttendancePrePathway
WHERE [Attendance Mark])=1;

Select Query for report

You do not need the where mark = 1 as you have already specified this in your union query

SELECT FFPID, Month, MonthNo, Attendance, Mark
FROM QryAllAttendances
WHERE Attendance Between [Forms]![frmDateRangeReport]![FromDate] And [Forms]![frmDateRangeReport]![ToDate]

 

[Yoplumpy]

Cheers - I did see that when I was pasting it in. Gone now.

 

[vbaInet]

You can manipulate records quite alot using queries. Queries can get quite sophisticated so I'm positive if you had one table it would have been alot easier to extract the relevant records. The image you sent is rather small, could you blow it up so it's clearer?

You really need to look into normalization.

In any case, did you know that the OpenReport method has a WHERE argument so you don't need to set the criteria in the record source, you can set it as you open the report. Try this:

    DoCmd.OpenReport "Name_of_report", , , "[Attendance] BETWEEN #" & _
                        (Format([Forms]![frmDateRangeReport]![fromDate], "mm/dd/yyyy") & "# AND #" & _
                        Format([Forms]![frmDateRangeReport]![toDate], "mm/dd/yyyy")) & "#"

Edit: Just noticed David's response and removed the '1'. Good catch David.