[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Access roster view

Posted on 2011-03-08
3
Medium Priority
?
734 Views
Last Modified: 2013-11-28
Hi,
I have a database for all staff job allocations for my work using MS Access 2007/2010 asthe frontend. There is a record created each date a member of staff is placed on a job. It would be really useful to be able to display the dates for an entire month showing which jobs somebody is assigned to. At the moment i have some VBA code which runs through the entire month checking each day to see if there is a job assigned and creating a blank, dummy record if it isnt. This is very slow and cumbersome and doesnt allow quick updating of the records. Is there a better way of doing this?

Many thanks,
Chris
0
Comment
Question by:chrisryan43
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 8

Accepted Solution

by:
Andrew_Webster earned 500 total points
ID: 35069219
Yes there is.

Create a dates table.  The quick way to do this is to use Excel: enter the first couple of dates then select them and drag down to create a whole list of dates.  Copy this into a table in Access.

Now create a query - the key is the OUTER JOIN - that shows what you want.

Something like:

SELECT roster.staffmember, dates.thedate FROM roster LEFT OUTER JOIN dates ON roster.thedate = dates.thedate.

This will show "staffmember" that has a "thedate" matched in "dates" and every "thedate" from dates regardless of whether it's matched in "roster".  Does that make sense?

(I've used "thedate" as "date" is a reserved word, and it's good practice to avoid naming table fields with reserved words.)
0
 
LVL 31

Assisted Solution

by:Helen Feddema
Helen Feddema earned 500 total points
ID: 35069821
You can use the DateAdd function in Access to create a table filled with incrementing dates.  Here is some code that fills a table with incrementing month-year values; you can modify it to create dates, just incrementing by day instead of month.
'Clear MonthYear table
   strMonthYearTable = "zstblAllMonthYears"
   strSQL = "DELETE * FROM " & strMonthYearTable & ";"
   DoCmd.SetWarnings False
   DoCmd.RunSQL strSQL
   
   strCurrentYear = Year(Date)
   strCurrentMonth = Month(Date)
   
   dteStart = DateAdd("m", -13, Date)
   dteEnd = DateAdd("m", 12, Date)
   Debug.Print "Date range: " & CStr(dteStart) & " to " & CStr(dteEnd)
   
   Set dbs = CurrentDb
   Set rst = dbs.OpenRecordset(strMonthYearTable)
   
   For intMonth = 1 To 24
      dteDynamicMonth = DateAdd("m", intMonth, dteStart)
      strMonthYear = Year(dteDynamicMonth) & "-" & Format(Month(dteDynamicMonth), "00")
      'Add a record to month-year table
      rst.AddNew
      rst![MonthYear] = strMonthYear
      rst.Update
   Next intMonth
      
   rst.Close

Open in new window

0
 

Author Comment

by:chrisryan43
ID: 35085124
Amazing! Thankyou so so much!

All working beautifully now
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question