If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#31
|
|||
|
|||
Calendar and time sheet
My first thought was to take my non-MVP ideas and stay home, but what fun
would that be? In Duane's solution: you may want to consider what happens when you (or others) manage to enter more than one case of Sample2 on the same date. -----------------------------------data structure (Duane) SampleID Autonumber primary key SampleTypeID link to table containing descriptions SampleDate SampleValue ------------------------------------------(maybe not if you strictly use Duane's input screen for all data input) Nothing from a data structure perspective prevents this. This could easily be fixed by setting a unique index to the SampleTypeID/date pair. SampleID SampleTypeID SampleDate SampleValue 1 2 12/1/2005 3 2 2 12/1/2005 6 3 2 12/1/2005 7 ---------------------------------------------- Now what do you want to see? Case A Description 12/1/2005 Sample2 3 Sample2 6 Sample2 7 --------------------------- Case B Description 12/1/2005 Sample2 16 (total) ------------------------- Case C Description 12/1/2005 Sample2 3 (count) -------------------------- Case D Description 12/1/2005 Sample2 5.3 (Avg) If Case B,C, or D then you will want to use a pivot table/crosstab query and do the data updates via a simple form. If you really want only one case per day and really need to show it as a matrix as discussed then, with the limit above you will probably want to follow Duane's suggestion, because you will want to query the data for some useful informtion in the future. Ed Warren. "Fehn" wrote in message ... Thanks Duane! That is what I want, I want it to be updatable... I will try other methods that other MVP's suggested.... Thanks again..... "Duane Hookom" wrote: A pivot or crosstab was my first thought also. However, Fehn possibly wants a solution that is "updatable". The pivot and crosstab work great for presenting the data from a normalized table but unfortunately they are not updateable. -- Duane Hookom MS Access MVP -- "Ed Warren" wrote in message ... Your example looks a lot like a pivot table; some grouping at the top and left with some data in the center of the table. You just might be able to get to where you want to be using a clever query and then displaying it in a pivot table. Ed Warren. "Fehn" wrote in message ... Does anyone know how to make this possible ad updatable in MS Access? Description - 12/1 - 12/2 - 12/3...... 12/31 Sample1 - 2 - - 5 .....etc Sample2 - 6 - 8 - 3 .....etc : : etc. Thanks. |
#32
|
|||
|
|||
Calendar and time sheet
So show me how you'd generate SQL you'd use to retrieve the value from your
denormalized table in the circumstance you just suggested, Steve. With a normalized table, I'd need a single parameter query, and I'd be done. You're right: my example wasn't that realistic in terms of being invoked through a prompt (although there are certainly times when you would want to determine a date programmatically in terms of nth weekday of the month). However, Duane's example of "Give me the sum of all of the samples from November 1st, 2005 to November 15th, 2005" (or, more generically, "Give me the sum of all of the samples between a day I'm going to select using a calendar control and another day I'm going to select using a second calendar control") certainly is realistic. How would you handle that with your denormalized table? Again, it's a single parameter query with a properly normalized table. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "PC Datasheet" wrote in message ink.net... You wouldn't build a search form that has the user enter "the second Tuesday of the month". You would pop-up a calendar and have him select the date. By selecting the date, the user has now selected the Year, Month and Day. November 8 was the second Tuesday of November. To get the value for Sample1 for Nonember 8, 2005, all you need is a query to find the record for Sample1 for the Year 2005, the month of November and Day8. You don't need more additional code stacked on alot of code to populate a bunch of text boxes. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com Over 1050 users have come from the newsgroups trusting me and requesting help. My fees are very reasonable. "Douglas J. Steele" wrote in message ... You can't be serious, Steve! Sure, it may be a little more work setting up the data input, but by having properly normalized tables, then you have the data such that you can do any analysis you require. Queries and reports become easy, so in the unlikely event that it did cost more to design the input forms, you'd more than make it up on the savings in the reporting area. A denormalized table is so inflexible for standard lookups. What if you need to know the value for Sample1 for the 2nd Tuesday of the month. What's the SQL for that? In a properly normalized table, the Where clause would include Where Description = 'Sample 1' AND MyDate = GetDate(2005, 12, vbTuesday, 2) where GetDate is: Function GetDate (WhatYear As Long, _ WhatMonth As Long, _ WhatWeekDay As Long, _ WhatWeekDayOfMonth As Long) As Date Dim dtmFirstOfMonth As Date dtmFirstOfMonth = _ DateSerial(WhatYear, WhatMonth, 1) GetDate = DateAdd("w", _ (WhatWeekDayOfMonth - 1) * 7, _ DateAdd("d", _ (WhatWeekDay - _ Weekday(dtmFirstOfMonth) + 7) Mod 7, _ dtmFirstOfMonth) _ ) End Function In case the arguments aren't obvious, WhatYear is the year for the date, WhatMonth is the number of the month (January = 1, February = 2 and so on to December = 12), WhatWeekDay is the number of the weekday (Sunday = 1, Monday = 2 and so on to Saturday = 7. Note that these are the same values as vbSunday, vbMonday, …, vbSaturday) and WhatWeekdayOfMonth is the order number of the weekday for the month (1 = first occurrence of that weekday in the month, 2 = second occurrence of that weekday in the month, etc.). -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "PC Datasheet" wrote in message link.net... So your solution is to write a bunch of code to fill a bunch of textboxes rather that simply using an nonnormalized table. HOW DUMB!! Is this the way you build applications so you can jack up the cost? Fehn, there's nothing wrong with using a non-normalized table in this case. Keep it simple and you get a non-code solution! You get your data displayed the way you want and it's updateable. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com Over 1050 users have come from the newsgroups trusting me and requesting help. My fees are very reasonable. "Duane Hookom" wrote in message ... A pivot or crosstab was my first thought also. However, Fehn possibly wants a solution that is "updatable". The pivot and crosstab work great for presenting the data from a normalized table but unfortunately they are not updateable. -- Duane Hookom MS Access MVP -- "Ed Warren" wrote in message ... Your example looks a lot like a pivot table; some grouping at the top and left with some data in the center of the table. You just might be able to get to where you want to be using a clever query and then displaying it in a pivot table. Ed Warren. "Fehn" wrote in message ... Does anyone know how to make this possible ad updatable in MS Access? Description - 12/1 - 12/2 - 12/3...... 12/31 Sample1 - 2 - - 5 .....etc Sample2 - 6 - 8 - 3 .....etc : : etc. Thanks. |
#33
|
|||
|
|||
Calendar and time sheet
PC Datasheet wrote:
This is totally untrue!! Keith Wilby would rather spend his time showing how two-faced he is. How exactly do you do that? Can you explain it? Thought not. Idiot. |
#34
|
|||
|
|||
Calendar and time sheet
Maybe so. But he is nothing but a trouble maker everywhere else.
"Jeff Conrad" wrote in message ... "PC Datasheet" wrote in message: ink.net... Keith Wilby would rather spend his time showing how two-faced he is. Keith answers a LOT of questions in the Security newsgroup Steve. Do not ever forget that. -- Jeff Conrad Access Junkie - MVP http://home.bendbroadband.com/conrad...essjunkie.html http://www.access.qbuilt.com/html/articles.html |
#35
|
|||
|
|||
Calendar and time sheet
PC Datasheet wrote:
Maybe so. But he is nothing but a trouble maker everywhere else. Keith answers a LOT of questions in the Security newsgroup Steve. Do not ever forget that. Only for you boy, only for you. |
#36
|
|||
|
|||
Calendar and time sheet
Great comments...
-- Duane Hookom MS Access MVP -- "Ed Warren" wrote in message ... My first thought was to take my non-MVP ideas and stay home, but what fun would that be? In Duane's solution: you may want to consider what happens when you (or others) manage to enter more than one case of Sample2 on the same date. -----------------------------------data structure (Duane) SampleID Autonumber primary key SampleTypeID link to table containing descriptions SampleDate SampleValue ------------------------------------------(maybe not if you strictly use Duane's input screen for all data input) Nothing from a data structure perspective prevents this. This could easily be fixed by setting a unique index to the SampleTypeID/date pair. SampleID SampleTypeID SampleDate SampleValue 1 2 12/1/2005 3 2 2 12/1/2005 6 3 2 12/1/2005 7 ---------------------------------------------- Now what do you want to see? Case A Description 12/1/2005 Sample2 3 Sample2 6 Sample2 7 --------------------------- Case B Description 12/1/2005 Sample2 16 (total) ------------------------- Case C Description 12/1/2005 Sample2 3 (count) -------------------------- Case D Description 12/1/2005 Sample2 5.3 (Avg) If Case B,C, or D then you will want to use a pivot table/crosstab query and do the data updates via a simple form. If you really want only one case per day and really need to show it as a matrix as discussed then, with the limit above you will probably want to follow Duane's suggestion, because you will want to query the data for some useful informtion in the future. Ed Warren. "Fehn" wrote in message ... Thanks Duane! That is what I want, I want it to be updatable... I will try other methods that other MVP's suggested.... Thanks again..... "Duane Hookom" wrote: A pivot or crosstab was my first thought also. However, Fehn possibly wants a solution that is "updatable". The pivot and crosstab work great for presenting the data from a normalized table but unfortunately they are not updateable. -- Duane Hookom MS Access MVP -- "Ed Warren" wrote in message ... Your example looks a lot like a pivot table; some grouping at the top and left with some data in the center of the table. You just might be able to get to where you want to be using a clever query and then displaying it in a pivot table. Ed Warren. "Fehn" wrote in message ... Does anyone know how to make this possible ad updatable in MS Access? Description - 12/1 - 12/2 - 12/3...... 12/31 Sample1 - 2 - - 5 .....etc Sample2 - 6 - 8 - 3 .....etc : : etc. Thanks. |
#37
|
|||
|
|||
Calendar and time sheet
Duane,
One of the prime signs of a dimwit is a post like the one you just made. You have sunk to the lowest depths with John Marshall. This is just evidence that you don't give a damn about the newsgroups, about polluting the newsgroups with worthless garbage and wasting a large volume of bandwidth. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com Over 1050 users have come from the newsgroups trusting me and requesting help. My fees are very reasonable. "Duane Hookom" wrote in message ... Steve, One of the greatest assets of a good programmer is the ability to identify patterns. Can you see the pattern below? News Groups ==================================== A question is posted: no advertising: No conflict A question is posted: no advertising: No conflict A question is posted: advertising: Lots of conflict A question is posted: no advertising: No conflict A question is posted: no advertising: No conflict A question is posted: no advertising: No conflict A question is posted: advertising: Lots of conflict Can you find the conflicts? If you look at actual threads with conflicts, can you find the ONE common initial element of each thread? Do you ever see Arno R or myself or any others creating conflict without an advertising post first? Think about it... -- Duane Hookom MS Access MVP -- "PC Datasheet" wrote in message link.net... Duane, Just take a cue from the people who are class acts and quit supporting and promoting Arno R and his band of dimwits. It's that simple. It's not complicated. It's not difficult. You are not impressing anyone siding with Arno R and his band of dimwits. You don't see these class act people siding with Arno R. They adhere to common netiquette rules. Whatever their feelings are, they stick to helping people in the newsgroups. There's an old saying, "birds of a feather flock together". Everyone is seeing that you are just like Arno R and his band of dimwits. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com Over 1050 users have come from the newsgroups trusting me and requesting help. My fees are very reasonable. "Duane Hookom" wrote in message ... Steve, Just start behaving like everyone else. It's that simple. It's not complicated. It's not difficult. Everyone else understands this. Everyone else is very good at complying. Everyone else sticks fairly close to common netiquette rules. -- Duane Hookom MS Access MVP -- "PC Datasheet" wrote in message ink.net... Duane, You continue to demonstrate that you are not even in the league with class acts like Allen Browne, Ken Snell, Pieter Linden, Albert Kallal, Fred G, Graham Mandello, Chuck Grimsby, MG Foster, and others. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com Over 1050 users have come from the newsgroups trusting me and requesting help. My fees are very reasonable. "Duane Hookom" wrote in message ... I would keep all the data in normalized tables (and ignore or verbally abuse PC Datasheet). tblSamples ================ SampleID Autonumber primary key SampleTypeID link to table containing descriptions SampleDate SampleValue If you really need a form like this, consider -writing code that loads your data into a "flat" table for editing more code would be needed to normalize after editing -create an unbound form like a grid of text boxes. use code to fill the grid with data and then to save the data to your normalized table I wrote some code for another poster a while back the filled a bunch of text boxes with customer names and order dates from Northwind. One of the keys was to use a scheme for naming the text boxes as below: txtCust1 txtOrdDate1_1 txtOrdDate1_2 ...etc... txtCust2 txtOrdDate2_1 txtOrdDate2_2 ...etc... txtCust3 txtOrdDate3_1 txtOrdDate3_2 ...etc... ...etc... ...etc... ...etc... ...etc... The code to fill the text boxes with customers and order dates: Private Sub cmdPullOrderDates_Click() Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSQL As String Dim intCustomer As Integer Dim strCustomer As String Dim intOrder As Integer strSQL = "SELECT CompanyName, OrderDate " & _ "FROM Customers INNER JOIN " & _ "Orders ON Customers.CustomerID = Orders.CustomerID " & _ "ORDER BY CompanyName, OrderDate" Set db = CurrentDb Set rs = db.OpenRecordset(strSQL) rs.MoveFirst With rs intCustomer = 0 Do Until .EOF Or intCustomer 2 strCustomer = .Fields("CompanyName") intCustomer = intCustomer + 1 Me("txtCust" & intCustomer) = strCustomer intOrder = 0 Do Until strCustomer .Fields("CompanyName") Or intOrder 4 intOrder = intOrder + 1 Me("txtOrdDate" & intCustomer & "_" & intOrder) = .Fields("OrderDate") .MoveNext Loop Loop .Close End With Set rs = Nothing Set db = Nothing End Sub -- Duane Hookom MS Access MVP "Fehn" wrote in message ... Does anyone know how to make this possible ad updatable in MS Access? Description - 12/1 - 12/2 - 12/3...... 12/31 Sample1 - 2 - - 5 .....etc Sample2 - 6 - 8 - 3 .....etc : : etc. Thanks. |
#38
|
|||
|
|||
Calendar and time sheet
So your "friend" is Arno R!
"Duane Hookom" wrote in message ... Steve, "a bunch of code" "jack up the cost"? I had the form and code created in a matter of minutes (apparently I work faster than you). I then provided/donated most of the solution free in this public news group (and an earlier thread). The tables were normalized and the data entry met the requirements. I have done this a few times and it becomes quick and easy. My customers don't have to pay extra later on when they need to query for all Tuesdays or sum the values between 5/12 and 5/25. I would NEVER provide a solution to a client that would work as you proposed. When I read your post with the "spreadsheet" looking table structure, I held back from criticizing your solution. Although I vehemently criticize your advertising, I was too much of a gentleman to criticize your lack of understanding of application development. I called you an "idiot" once when you took a friend's statement totally out of context and then immediately apologized. I have often criticized actions and have tried to never refer to anyone as "DUMB", "Nitwit", or other derogatory expressions (although I often think it). You might get more work if you didn't display a lack of understanding of news groups and programming. -- Duane Hookom MS Access MVP -- "PC Datasheet" wrote in message link.net... So your solution is to write a bunch of code to fill a bunch of textboxes rather that simply using an nonnormalized table. HOW DUMB!! Is this the way you build applications so you can jack up the cost? Fehn, there's nothing wrong with using a non-normalized table in this case. Keep it simple and you get a non-code solution! You get your data displayed the way you want and it's updateable. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com Over 1050 users have come from the newsgroups trusting me and requesting help. My fees are very reasonable. "Duane Hookom" wrote in message ... A pivot or crosstab was my first thought also. However, Fehn possibly wants a solution that is "updatable". The pivot and crosstab work great for presenting the data from a normalized table but unfortunately they are not updateable. -- Duane Hookom MS Access MVP -- "Ed Warren" wrote in message ... Your example looks a lot like a pivot table; some grouping at the top and left with some data in the center of the table. You just might be able to get to where you want to be using a clever query and then displaying it in a pivot table. Ed Warren. "Fehn" wrote in message ... Does anyone know how to make this possible ad updatable in MS Access? Description - 12/1 - 12/2 - 12/3...... 12/31 Sample1 - 2 - - 5 .....etc Sample2 - 6 - 8 - 3 .....etc : : etc. Thanks. |
#39
|
|||
|
|||
Calendar and time sheet
On Sat, 03 Dec 2005 19:53:12 GMT, PC Datasheet wrote:
Duane, You continue to demonstrate that you are not even in the league with class acts like Allen Browne, Ken Snell, Pieter Linden, Albert Kallal, Fred G, Graham Mandello, Chuck Grimsby, MG Foster, and others. Steve, You have gone too far. To partially quote Joseph Welch -- "... Have you no sense of decency ...?" Your first reply to the OP was a direct solicitation of business, without even an attempt to give a workable answer to his question. This went far beyond your signature solicitation and was totally uncalled for. You then assumed my non-participation in calling you to task was some sort of approval of your behavior. Quite the contrary. I didn't wish to take up my time, nor the time of others, in what I perceive is a futile attempt to get you to observe the common courtesies of participation in an international newsgroup dedicated to 'free' technical help. You then went and mentioned my name in an attempt to separate the posters who have actively called you to task for these solicitations from those of us, MVP's and others, who for whatever reason chose not to participate. I can keep quiet no longer. Steve, after following these many threads in the various Access newsgroups over the past months, I can surely state that there is more class in the fingertips of Arno, Duane, John, Randy, Keith and Jeff, (and I'm sure lot's others who have not been actively participating in this 'discussion'), than you have in your whole body. You see, it's not just a matter of what you 'can or can't do,' it's a matter of what you 'should or should not do' that separates 'Class Acts' from the rest. In a later post in this thread your wrote: "There's an old saying, "birds of a feather flock together". I agree. I'm very happy to be associated with Arno, Duane, et alia. Believe me, it's much better to be an eagle than a turkey! -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
#40
|
|||
|
|||
Calendar and time sheet
rhetorical question
Huh? /rhetorical question http://humanities.byu.edu/rhetoric/F...0questions.htm -- Duane Hookom MS Access MVP -- "PC Datasheet" wrote in message ink.net... Duane, One of the prime signs of a dimwit is a post like the one you just made. You have sunk to the lowest depths with John Marshall. This is just evidence that you don't give a damn about the newsgroups, about polluting the newsgroups with worthless garbage and wasting a large volume of bandwidth. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com Over 1050 users have come from the newsgroups trusting me and requesting help. My fees are very reasonable. "Duane Hookom" wrote in message ... Steve, One of the greatest assets of a good programmer is the ability to identify patterns. Can you see the pattern below? News Groups ==================================== A question is posted: no advertising: No conflict A question is posted: no advertising: No conflict A question is posted: advertising: Lots of conflict A question is posted: no advertising: No conflict A question is posted: no advertising: No conflict A question is posted: no advertising: No conflict A question is posted: advertising: Lots of conflict Can you find the conflicts? If you look at actual threads with conflicts, can you find the ONE common initial element of each thread? Do you ever see Arno R or myself or any others creating conflict without an advertising post first? Think about it... -- Duane Hookom MS Access MVP -- "PC Datasheet" wrote in message link.net... Duane, Just take a cue from the people who are class acts and quit supporting and promoting Arno R and his band of dimwits. It's that simple. It's not complicated. It's not difficult. You are not impressing anyone siding with Arno R and his band of dimwits. You don't see these class act people siding with Arno R. They adhere to common netiquette rules. Whatever their feelings are, they stick to helping people in the newsgroups. There's an old saying, "birds of a feather flock together". Everyone is seeing that you are just like Arno R and his band of dimwits. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com Over 1050 users have come from the newsgroups trusting me and requesting help. My fees are very reasonable. "Duane Hookom" wrote in message ... Steve, Just start behaving like everyone else. It's that simple. It's not complicated. It's not difficult. Everyone else understands this. Everyone else is very good at complying. Everyone else sticks fairly close to common netiquette rules. -- Duane Hookom MS Access MVP -- "PC Datasheet" wrote in message ink.net... Duane, You continue to demonstrate that you are not even in the league with class acts like Allen Browne, Ken Snell, Pieter Linden, Albert Kallal, Fred G, Graham Mandello, Chuck Grimsby, MG Foster, and others. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com Over 1050 users have come from the newsgroups trusting me and requesting help. My fees are very reasonable. "Duane Hookom" wrote in message ... I would keep all the data in normalized tables (and ignore or verbally abuse PC Datasheet). tblSamples ================ SampleID Autonumber primary key SampleTypeID link to table containing descriptions SampleDate SampleValue If you really need a form like this, consider -writing code that loads your data into a "flat" table for editing more code would be needed to normalize after editing -create an unbound form like a grid of text boxes. use code to fill the grid with data and then to save the data to your normalized table I wrote some code for another poster a while back the filled a bunch of text boxes with customer names and order dates from Northwind. One of the keys was to use a scheme for naming the text boxes as below: txtCust1 txtOrdDate1_1 txtOrdDate1_2 ...etc... txtCust2 txtOrdDate2_1 txtOrdDate2_2 ...etc... txtCust3 txtOrdDate3_1 txtOrdDate3_2 ...etc... ...etc... ...etc... ...etc... ...etc... The code to fill the text boxes with customers and order dates: Private Sub cmdPullOrderDates_Click() Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSQL As String Dim intCustomer As Integer Dim strCustomer As String Dim intOrder As Integer strSQL = "SELECT CompanyName, OrderDate " & _ "FROM Customers INNER JOIN " & _ "Orders ON Customers.CustomerID = Orders.CustomerID " & _ "ORDER BY CompanyName, OrderDate" Set db = CurrentDb Set rs = db.OpenRecordset(strSQL) rs.MoveFirst With rs intCustomer = 0 Do Until .EOF Or intCustomer 2 strCustomer = .Fields("CompanyName") intCustomer = intCustomer + 1 Me("txtCust" & intCustomer) = strCustomer intOrder = 0 Do Until strCustomer .Fields("CompanyName") Or intOrder 4 intOrder = intOrder + 1 Me("txtOrdDate" & intCustomer & "_" & intOrder) = .Fields("OrderDate") .MoveNext Loop Loop .Close End With Set rs = Nothing Set db = Nothing End Sub -- Duane Hookom MS Access MVP "Fehn" wrote in message ... Does anyone know how to make this possible ad updatable in MS Access? Description - 12/1 - 12/2 - 12/3...... 12/31 Sample1 - 2 - - 5 .....etc Sample2 - 6 - 8 - 3 .....etc : : etc. Thanks. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How can I fill a time sheet from a calendar | PeterM | General Discussion | 6 | November 29th, 2005 09:24 PM |
Time Zone suggestions for Outlook Calendar | Ben Knox | Calendar | 2 | October 20th, 2005 03:42 PM |
Calendar Time | Linn Allen | Calendar | 1 | September 20th, 2005 10:04 PM |
Calendar should allow scheduling appointments in any time zone | Art H | Calendar | 1 | August 19th, 2005 11:25 PM |
Access Calendar | lost | General Discussion | 2 | July 7th, 2004 04:58 AM |