Creating a form to show all locations (for a particular year only)

hi Folks
As always would appreciate your help with this...
I have attached a database. This is what I want..
The form (Painting Schedule Central block sub-form) - I only want to show the current year's data, but I want all locations showing so that the user sees a full list of all the locations and can then pick out what locations during what months have been painted. However the complete list of locations are listed in the tblLocations. How can I set this form up (Painting Schedule Central block sub-form) so that
(a) it only shows the current year's  data (b) it is easy to quickly add new locations with ID and description to this form (and these new locations and descriptions to the tblLocations) .
thanks as always
Painting-vers-EE.accdb
LVL 1
agwalshAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Essentially that's correct. If you add a new Location, it would show up - but it would not show up for different Years. In order to do that, you'd have to add a new record to the [Painting Schedule Central Block] table for EACH Year, for EACH Location. Then, when you filter for a specific Year, the form would show all Locations for that year.

I also noticed some data issues in that table. Referring to the two records for the Location identified as "B" - the LocationID for those two records are different. I'm not sure if this is just a typo, or if this is test data that does not accurately represent live data, but the LocationID should be the marker for the Location. There's also a "PaintingID" field in that form ... not sure what that is.

So to summarize - yes, you could just enter a new Location, but in order to use the grid layout you'd also have to enter a new record in the [Painting Schedule Central Block] for that Location. You'd also need to enter a NEW record in that table for each Location every year.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I only want to show the current year's data, but I want all locations showing so that the user sees a full list of all the locations and can then pick out what locations during what months have been painted.
Do you mean you want to see ALL locations in a grid style (as you seem to be showing in your subform), regardless of whether they are in the Painting Schedule Central Block table? If so, then the only way to do that is to use temporary table.

So for example if you want to show all data for 2011, you would build a temporary table that include ALL locations, and a column for each month for 2011.

You'd then have to modify the records in that table, based on your STORED data (i.e. the data in the Painting Schedule Central Block table) - so you'd update each record in the temp table to indicate if they already have data for that year:

Dim rst As DAO.Recordset
dim rstTemp as DAO.Recordset

Set rstTemp = Currentdb.OpenRecordset("SELECT * FROM MyTempTable')

Do Until rstTemp.EOF
  Set rst = Currentdb.OpennRecordset("SELECT * FROM [Painting Schedule Central Block] WHERE LocationID=" & rstTemp("LocationID") " AND Year=" & YourYearValue)
  If Not (rst.EOF and rst.BOF) Then
    rstTemp("January") = rst("January")
    rstTemp("February") = rst("February")
    etc etc
  End If

  rstTemp.MoveNext
Loop


You'd then base your subform on the temp table. When the user has finished editing records, you'd need to move the data from the Temp table back into the live table.

the reason for these convoluted manipulations is due to the way your data is stored. If you could store the data in a more normalized fashion, you'd find things like this to be much easier. You'll also find that you'll have more and more issues down the road (i.e. when writing reports and such) if you don't go ahead and bite the bullet and normalize the tables now.
0
 
agwalshAuthor Commented:
@lsmconsulting. - This is the way the perso  wants to see it..they want to see all the locations at one go and then just add painted locations on the fly...

"Do you mean you want to see ALL locations in a grid style (as you seem to be showing in your subform), regardless of whether they are in the Painting Schedule Central Block table? If so, then the only way to do that is to use temporary table."
So to take your suggestion on board of normalising the tables - one table for location and then a sub-form for each painting transaction..I presume..how can I normalise it and also get this format? thank you
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
one table for location and then a sub-form for each painting transaction..I presume..how can I normalise it and also get this format?
Essentially that is correct. We'd have to know more about the usage of the database to fully understand the best way to structure it, however.

Even with a normalized structure you'd have to resort to a temporary table to get the format you want, unless you simply store a new record for every Location for each year, and store a new (blank) record for each "transaction".
0
 
agwalshAuthor Commented:
Alternatively I could just tell them that they have to enter new locations via the new  locations form and then it would be picked up in the sub-form...
0
 
agwalshAuthor Commented:
Yes, get the user to enter the details in the form :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.