Can i make a calendar as report Results

The subject pretty much sums it up. Is it possible to make a calendar type report?

Hi friend
Anyone can help me

If I need to make the graph in report.
(the report are made from the query and i set one field as the parameter value.)

Now the parameter that I key in , normally is the date.

Many time that I key in the correct date but it show nothing in the graph.

So i thinking that for the parameter field, if I can make the calendar then just select the date in calendar and use that for the value in parameter field.
Then the graph is show according to the select dat in calendar.

How can I do that?

So here is the thing:


I started an engineering internship with a tug and barge company this summer. My boss wanted me to build him a vessel maintenance management system using which he could


1) Enter records of maintenance completed
2) Enter records of regulatory inspections completed
3) View a history of maintenance and inspections completed
4) View all upcoming maintenance and inspections, tiered by priority.
5) An alert function for tasks upcoming within a given time duration
6) A way to print a report for work history (3) and a worklist (4)
7) There would be a separate workbook for each vessel. Easily add new components or inspections to book for it to track.
8) Easily copy and modify the workbook to work with a new vessel


I am a really computer savvy guy but had little to no knowledge of programming or macros. I do have a really good ability to pick up new computer skills quickly to get work done. As long as I have a defined problem, I can come up with solutions. It took me a little over two weeks to figure out how VBA works with Excel and build a system that does everything mentioned above really well. The maintenance and inspections work off of either time or engine hours. I figured out a way for the worksheet to pull the engine hours from another workbook that gets updated with running hours every month. Using "Hours to Go" till an overhaul or inspection is due and the average number of hours an engine runs over the course of a month, the workbook can predict when a new inspection or maintenance is coming up. All these inspections and maintenances are compiled into a sheet where they are prioritized using conditional formatting. Once an inspection or maint. is completed, my boss will select it from a drop down and hit a button which will paste the entry with a time stamp into a Work History sheet and at the same time, reset the Upcoming Work sheet so that the component or inspection gets an updated due date (satisfying 1, 2, 3 and 4). Then I figured out a way for Excel to send out an email using Outlook once a component or inspection fell within a certain "Days to Go" range (5). The email would contain the name of the vessel, the component or inspection that needs attention and the number of Days to Go. Currently working on figuring out how to have Excel automatically add the due dates to the Outlook Calendar. I am also in the process of streamlining 6,7 and 8, and just making the workbook robust in general. My boss is highly satisfied with this current system I built but I want to make it better. I want to explore all the possibilities.


I have two more months to go on the internship and I have just now started looking at MS Access. The program just seems to be a lot more clean-cut and has the same VBA capabilities. I am wondering if it would be worth it for me to move this whole program into Access or if it is a total crap shoot. As it stands right now, I know nothing about the program but I can learn quick and am pretty interested in learning about databases. I have already started playing around in Access, just exploring what it is capable of. So it is time for me to reach out to you Access gurus! Is it worth it? Are there any better options?

Hello kind people,

I know this must be easy, but I have so much trouble figuring out how to make the reports look the way I want them to...

Here's my basic setup: I have three main tables of data:
Times (a set of non-overlapping blocks of dates)
People
Places

I have a fourth table, Events, where each event represents one member of each of the above three tables (i.e., a person at a place at a time). There is not a 1-1 relationship (i.e., multiple people may be at the same place at the same time, and there may be time/place combinations with no person there).

What I would like to do is output this data (the Events) as a single large grid with (for example) Times across the top, Places down the left column and the remaining spaces filled with the People who are in that place at that time:


	Code:
	            Time1        Time2      Time3        Time4
Place1      People       People     People       People
Place2      People       People     People       People
Place3      People       People     People       People
Place4      People       People     People       People

I think that should make sense (at least I hope it does).

Whenever I try to make a report to do this, I can't seem to get it to do rows and columns with the data where I want it. I have looked through some of the calendar examples posted on this forum, but they all seem to be way too complex for what I want, and modifying them seems to be a little out of my league. I'm hoping that this task is simple enough it can be done without any VBA (I have VBA experience in ppt and excel, so I am willing to tackle it if necessary).

My questions are:

(1) Is this the best way to set up my database for doing this? I'm doing other things with the data, too, but this seems to be the most complicated part of the problem, and I'm still at a point where a redesign would be fairly simple to do

(2) Can I do this with a simple report, or do I need to think about it differently?

Thank you for any suggestions you may have.

ok guys looking for some help again

I have a few reports that run off dates the user has to input i.e. Start Date and End Date

What i'm trying to do as i've seen it on other databases is instead of users having to input a date they can select a date from a calendar pop up

hope this makes sense

I need to create a database and it has been a while since I have created a DB and I get some of this okay. It is so that I can create a schedule report. So far I think I need three tables. One is the machines. Two is the product schedule (how long it takes to produce X number of products). The last would be work orders. I think that pretty much covers the data.

I think most of the data can be maniputated by queries and that is even done on the report. The report as we all know is the reason for a database. If it was just about the data it would be simpler to do a spreadsheet. It could get fancy and make a pivot table. That output can be a calendar of sorts. What I would like to see is a weekly or montly report that have lines per machine with job orders lining up so that we can see any overlap or too long of a gap between workorders.

I am planning on having the database operator only need to punch in the workorder, number of items, and when the job is needed to be completed. In the output of the report I will have it do some math to determine how many days it will take to complete the workorder and enter that as a line in the calendar. With 33 machines this would probably be weekly since 33 lines in each day is quite large.

I am envisioning it as a single line beginning this day stretching to this day. It would be nice to color code it and print the workorder in the line. It would be a management tool for production managers to crack the whip.

Well anyway I have to go back to creating my tables and my desk. Thanks to anyone who might have an answer.

With a lot of help from people on this site who know a darned sight more than I do, I am trying to build a customer database. I have been given a lot of help to date when I got stuck. And I've been extremely grateful!

A lot of my forms and reports etc work well in isolation, but I am well and truly stuck on what (I hope) is the final hurdle.

The aim is to produce a payments received report based on data between two given dates.

These dates are chosen using a neat calendar-driven form (thanks to the guys who helped there) and stored in a table.

A report on the payments is made using a query that gets the payments between the two dates.

What I want to do is...

From the switchboard, the user selects that a report is needed. The form 'pops up' to get the required between dates. The dates are loaded into the table (no problems there). Next, the query runs in the background (ideally) to work out all the payments received between the two dates. And finally, the report pops on the screen in preview mode.

Using macros, I can, of course, make the calendar form open up and I can make the report open up, but I can't run the query in between. Also, the report has never waited until the calendar form worked.

So - bottom line now - is there a way I can, from the switchboard:

1. Open the calendar form and allow the underlying table to action the new dates. Then...

2. Run the query (invisibly) so that the data is ready for the next stage...

3. Open the report in print preview, ready to print, with all the most up-to-date info in it.

I have tried (extremely unsuccessfully) to crack this and I am about ready to go back to a much simpler (but not as snazzy) way that I used to do it. But I'd like to make people think I'm an ace developer!

Or am I just trying to be too clever for my own good?

TIA

Chris

So you are standing by the watercooler and I walk up, fill my waterbottle looking a little frazzled. You say "Hey you look like your on edge, what is going on?"

I take a long pull on my water and run my free hand through my gray hair. I shake my head and in a long sigh I say. "I need some help. I need to make a report that shows a work schedule for 10 employees. I need it to print in a monthyly calendar format with horizontal lines to show start and end date and time. I need it to show that there is or is not any overlap for that machine."

It has been a while since I have created a DB and I get some of this okay. So far I think I need three tables. One is the machines. Two is the product schedule (how long it takes to produce X number of products). The last would be work orders and that pretty much covers the data.

I think most of the data can be maniputated by queries and that is even done on the report. The report as we all know is the reason for a database. If it was just about the data it would be simpler to do a spreadsheet. It could get fancy and make a pivot table. That output can be a calendar of sorts.

Well anyway I have to go back to creating my tables and my desk. Thanks to anyone who might have an answer.

I've searched and searched... I can't seem to find anything close to this.

I have 10 sets of fields:
[Photo01date], [Photo01], [Photo01note]
[Photo02date], [Photo02], [Photo02note]
[Photo03date], [Photo03], [Photo03note]
etc...

These 10 fields are shown in my form for entry. There are default values for each field that need to be maintained in order for the reporting to work (I'm grabbing photos into my report).

If a user no longer wants this field to show up on the report the default values need to be re-entered. Rather than having the user enter the default values each time I want them to be able to click a button that will enter the default values for them.

So... I have a drop down menu that lists "01", "02", "03", etc... If the user selects a number that will determine which field will return to the defaults. For instance, if the user selects "05" then fields [Photo05date], [Photo05], [Photo05note] will all return to their default values.

The following code is obviously wrong (why I'm posting)... but hopefully it will make some sense as to what I'm trying to do.



	Code:
	Private Sub ReturnToDefaultButton_Click()
    Dim stFieldDate As Field
    Dim stFieldName As Field
    Dim stFieldMemo As Field
    
    Set stFieldDate.Name = "[Photo" & [ReturnToDefaultSelection] & "Date]"
    Set stFieldName.Name = "[Photo" & [ReturnToDefaultSelection] & "]"
    Set stFieldMemo.Name = "[Photo" & [ReturnToDefaultSelection] & "Note]"

    stMsgBoxPrompt = "Are you certain you want to return the following fields to default:" & _
    vbNewLine & stFieldDate & _
    vbNewLine & stFieldName & _
    vbNewLine & stFieldMemo
    
    If [ReturnToDefaultSelection]  "" Then
        prompt = MsgBox(stMsgBoxPrompt, vbOKCancel + vbQuestion, "Are you sure?")
        
        If response = vbCancel Then
            Exit Sub
                        
        Else
            'Here is where the code goes to paste the lengthy path
            stFieldDate = ""
            stFieldName = "Daily Calendar Database - White.jpg"
            stFieldNote = ""
        End If
        
    Else
        prompt = MsgBox("You must select a field number", vbOKOnly+ vbCritical, "Action canceled")
        Exit Sub
    End If
        
End Sub



I'm wondering if it is possible to store data as part of a report, but not part of an underlying table. I have a simple calendar database with a report that prints one page for each day of a project listing all the various events for each day. I would like a version number to appear atop each page so that as events change and pages are reprinted with new information we can keep track of what information is most current.
My first thought was to place a combo box in the group header of the report (I've used the group feature to organize the record set into individual days) with fixed values of the version numbers we use. My hope was that in report view, I could simply select a version for each day and then that information would print with the report. The problem I've run into is that this information doesn't seem to "live" anywhere. Any time I navigate away from report view, the values are all reset to 1. As a possible solution I tried creating a table that would simply store the values v1 through v9 and making that the data source for the combo box. Access doesn't like this, though, because there is no actual relationship between the values in that table, and the value of the table that makes up the record set the report generates.
Is what I'm trying to do even possible, or am I simply approaching this from the wrong angle? Thank you for your help and patience.

-Jim

Got a need to assign 2, two digit codes to each day of a yearly calendar. An example would be:

01 Jan 12 = 61,62
02 Jan 12 = 62,63
03 Jan 12 = 63,64
04 Jan 12 = 64,65
05 Jan 12 = 65,66
06 Jan 12 = 66,61

The point of this is that employees are assigned one of these 60 series numbers to indicate a day off group. Everyone is assigned a day off group and obviously, people's days off work are dictated by the day off groups listed as off for that day. In the above example, on 01 Jan 12, people in day off group 61 & 62 are off.

Our personnel table contains the day off group for everyone in the company.

The end goal is to create a simple app that when a user clicks on a calendar day and submits it, a report pops up and shows everyone who's working on that given day.

So I can let the app hit the personnel table with each request to pull the day off group numbers for a given day. What I think I need locally is a table generated that contains every day of the year and the associated day off group numbers, or conversely I think it can also be done on the fly with VBA.

Now comes the hard part.... is it better to create a static table that contains the days or have it done on the fly through code? As far as the DOG assignments to days, it never changes. That is that it doesn't matter what year we go into, the DOG assignments stay the same so I shouldn't need to create a calendar every year as the value should be calculated with a static formula.

Just need help on how to create the formula to calculate the 2 day off group numbers for any given day no matter when it is. I seem to think as long as we know one day's day off group numbers we can work off that forever.

Make sense?



*edit..... I'm thining this could be something where a recordset is opened and 01 Jan 12 is assigned 61,62 and the rest is calculated by taking the date value from a calendar and comparing datediffs and somehow calculating the DOG values from the known first value....just can't wrap my head around it, like do I have to have a static value for 01 Jan 12 in a local table and refer to it in the module or can I create a temporary variable with it and then make a recordset off the values I pull from the personnel table?

*edit 2..... as I think now, what about just a simple query that calcs the 2 day off groups then shows me the results from the personnel table where matching day off groups are found? Also could link that to the "time off" table where those people who've been granted time off on a certain day could be removed from the personnel list for a given day.


aaaand edit 3..... forgot to add 3 extra day off groups. day off group 8 is Saturday-Sunday, d.o.g. 9 is Friday-Saturday and d.o.g. 10 is Sunday-Monday. Those folks are in set dog's and their days off never change like everyone else's do.

I have a database that is almost complete and I don't have time to make any drastic changes because I am leaving the country soon. I have a table, "tblEntity" with a field, "FiscalYearEnd". This field contains all of the months in a year using a drop down box so that it would be easier to choose a company's fiscal year end in a form or query. I did this in the Lookup tab in design view of "tblEntity". The "FiscalYearEnd" field has the following:
Display Control: comboboxRow Source Type: Value ListRow Source: "January";"February";"March";"April";"May";"June"; "July";"August";"September";"October";"November";" December"That's how I made the drop down box and it's been great because my boss doesn't have to type in each company's month.

I made a query and report based on each company's fiscal year end, which works fine. I grouped the report by each month, which also works.
HERE'S MY PROBLEM: I can only sort each month in either Ascending order (April, August, December, February, etc.) or Descending order (September, October, November, May, etc.).

I want to be able to sort the months in calendar order from January, Feb, March, April May and so on. I have tried a few different things but the only things I can think of are:
Creating an entirely new table with the Month and an Autonumber from 1-12 (months) Which is something I DO NOT want to do because then I will have to redo all of my reports, queries, relationships, and forms.Using some sort of expression in a queryUsing some sort of Input Mask or Validation Rule in the Design of my "tblEntity"Does anybody have any suggestions as to how I can do this?

Thanks a lot.

-Luke

So here is the thing:


I started an engineering internship with a tug and barge company this summer. My boss wanted me to build him a vessel maintenance management system using which he could


1) Enter records of maintenance completed
2) Enter records of regulatory inspections completed
3) View a history of maintenance and inspections completed
4) View all upcoming maintenance and inspections, tiered by priority.
5) An alert function for tasks upcoming within a given time duration
6) A way to print a report for work history (3) and a worklist (4)
7) There would be a separate workbook for each vessel. Easily add new components or inspections to book for it to track.
8) Easily copy and modify the workbook to work with a new vessel


I am a really computer savvy guy but had little to no knowledge of programming or macros. I do have a really good ability to pick up new computer skills quickly to get work done. As long as I have a defined problem, I can come up with solutions. It took me a little over two weeks to figure out how VBA works with Excel and build a system that does everything mentioned above really well. The maintenance and inspections work off of either time or engine hours. I figured out a way for the worksheet to pull the engine hours from another workbook that gets updated with running hours every month. Using "Hours to Go" till an overhaul or inspection is due and the average number of hours an engine runs over the course of a month, the workbook can predict when a new inspection or maintenance is coming up. All these inspections and maintenances are compiled into a sheet where they are prioritized using conditional formatting. Once an inspection or maint. is completed, my boss will select it from a drop down and hit a button which will paste the entry with a time stamp into a Work History sheet and at the same time, reset the Upcoming Work sheet so that the component or inspection gets an updated due date (satisfying 1, 2, 3 and 4). Then I figured out a way for Excel to send out an email using Outlook once a component or inspection fell within a certain "Days to Go" range (5). The email would contain the name of the vessel, the component or inspection that needs attention and the number of Days to Go. Currently working on figuring out how to have Excel automatically add the due dates to the Outlook Calendar. I am also in the process of streamlining 6,7 and 8, and just making the workbook robust in general. My boss is highly satisfied with this current system I built but I want to make it better. I want to explore all the possibilities.


I have two more months to go on the internship and I have just now started looking at MS Access. The program just seems to be a lot more clean-cut and has the same VBA capabilities. I am wondering if it would be worth it for me to move this whole program into Access or if it is a total crap shoot. As it stands right now, I know nothing about the program but I can learn quick and am pretty interested in learning about databases. I have already started playing around in Access, just exploring what it is capable of. So it is time for me to reach out to you Access gurus! Is it worth it? Are there any better options?

I have this idea for a computer program that will make my job as an electrical foreman much easier. I'm pretty sure that I can use Access for what I want but don't know, so I need some advice.

Currently we use Excel tables for everything, timesheets, order forms, tracking. I want to move everything into Access for a few reasons. On the material front our current system of Excel table order forms creates two main problems. There is a lot of duplicated data, and time wasted duplicating it. For example if I order 300' of 3" EMT conduit, I need to order straps and connectors and couplings so I type out 3" EMT connectors x 10, 3" EMT couplings x 50, 3" EMT straps x 100. This process is repeated when I order 1/2" EMT or 3" PVC. I think it would be easier to store the material info and then have a form with drop down menus for size, type etc. I ideally I would like to be prompted for the accessories once conduit is chosen, or connectors when cable chosen, this kind of functionality. I would also like to know how much 3" EMT I have used in the last year or five or month.

Also when two applications use the same material I would like the program to be able to deal with totals. Ideally I would like something that I could define specific tasks on the job. At the design phase of the project each job would get a take off for time and material. Later I could order material by job and call the take off that was created at the design phase and a report would be generated and sent to the purchasing agent.

I would also like to be able to store my workers, and assign workers to these specific tasks as they come up, hopefully using outlook calendar and contact functions. Currently worker payroll is done using excel tables of hours, based on days of the week, which are emailed to the main office from site. I would like to be able to do payroll based on these job ID's and be able to monitor estimated time in relation to actual time.

I would also like the program to be able to keep a portfolio of the employees, the specific tasks they have accomplished, and some form of ratings for efficiency in time and material. As apprentices it is important that the guys get a broad base of experience throughout their apprenticeships and a system like this would go along way to helping us develop the guys to their fullest as they move from foreman to foreman. At it's largest extreme, I would like something that the owner could use to keep tabs on all the jobs, aggregate material over the course of the year to allow for larger purchases, and be able to rate the merits of the different approaches taken by the various foremen working for him.

I would really really appreciate some advice form those of you who know how to utilize this stuff to its fullest as my experience and knowledge is above average but lacking. I need to know what I need to learn so I can get on it. Thanks for all the help you can give me.

Hi, all. I really don't know where to start with this. I have a database I use to check compliance with certain items. An auditor goes to a division and asks some questions, taken from the database, and enters a compliance level for that question. When the auditor enters a response to a question a date is automatically entered for that question. Each division has a specific number of questions which are spread over three years. I need to look at responses by year, which is done easy enough with a parameter query in a report. In this report, I look at total questions asked in that year, compliance level and a few other stats.

I am having trouble with one feature I need to see. Each division is assigned a number based on it's size. So, there are N widgets for every division that I can translate into a workload for the year. With me? I can do a calculation based on [(# questions asked in a month/total questions for that year)*N widgets]. Still with me? You can see what I'm getting is simply a way to measure progress. I need to show the number of widgets I've done to date toward the total by month. In a query I reformat the date to the 3 letter month, group them by year and month, and then performs the above calculation. If I asked 25 of 100 questions in August of 2004, my measure for August would be 1.2. Life is great up to this point.

Here is my problem. I can make a report based on this information. I show my goal as 5 and I got 1.2 in August. That is all I show though is August. I've been asked to show in the report every calendar month on the reportwith or without data. I thought it would be relatively simple to put 12 unbound text boxes on the report and go through a SELECT CASE routine to put data in the correct slots. It isn't going that simple. Can you guys suggest a way to show this? It doesn't seem useful to me, but it has been requested, so ... What I will end up with is a report that lists each month and only 1 or 2 months having data. Another reason I used twelve text boxes was to be able to split the list into 2 columns. Well, I hope this is a tiny bit clearer than a mud hole. Thanks for any advice.

Jon

Hi everybody,

I hope this posting finds you all well.

I have this Access 2000 DB (“test_Vacation”) to modify, which I am attaching, and unfortunately was not initially built by me. What this is, a customized Vacation/ Out-of-Office Database. And, basically I need to create Reports for them. Right now, there are couple of Reports; one of which is simply a Crosstab Query, which is still not showing the Data functionally. If someone can jump in and help me out, that’d be great - you will find the Word attch. (“Help”) helpful, for it provides more detailed/ specific Improvements that need to be made on Reports. I provided some general suggestions for you here:

1. You can create a Form: Each employee by Month block – total for EX: May; better yet by Week.

2. By WEEK, I’d like to see: who’s Out/ who’s In (like the Matrix, but need to fix the dates there to Count - “run by day”)

3. The reports there right now are not functioning properly

4. For Matrix Report, you may use a Tbl_CLNDR w/ all working/ Business days (so not to keep recreating them). Right now, where the dates are correlated, it shows Names and all days @ Top, whether OR not the Employees are there OR not - but I need to get a Range, so it would show in month for EX:

a. Only April days, because it would take from the Tbl_CLNDR, and where they correlate with somebody’s date, it would have 1 or 0. After this Form is clean, it would chose a Report, which needs to be able to run a Matrix and just don’t show days (Null) that are not being occupied in the calendar at all.
i. Right now the Matrix Form is done by the number of the month / Year/ to show that timeframe

5. You may think of other Reports to include, to be able to know ahead who’s going Out that “Week” (currently an incorrect name, for it is more of a 7 days in a Month) – please be my Guest, fire away with anything that you think might help my Calendar analysis.

To sum it up, for Weekly Report: you can count the number of the week, and then group by Week, as well as separate reports by Month/ Year, with total for Days please. If this info doesn’t help, the attached “Help” zip should brief you with little insight. Hopefully, you get the idea, and if someone could please simply tweak and make the existing or new Reports work.

Much of luck and thank you in advance ,
Beginner.

I have been asked to set up a database that will allow me to pull a report of all reports that are due for the next month. Unfortunately the due day is not always the same. for example I have several reports that are due the 5th business day of the month and at least one report that is due on the 5th calendar day of the month. So far i have set up a table with all the reports and thier corresponding time frames (i.e. monthly 5th business day, monthly 5th calendar day) (monthly and number of days are in separate fields). I also have a table with the months, the number of business day and the number of calendar days in each month and a table with a list of all pertainent holidays. I am now, however, stuck. I can't seem to figure out a way to get the information I need on the fly as opposed to making an entry for each accurence of a report. Any ideas?

I work in a government office that interfaces with community groups on a regular basis. I've been asked to provide a calendar 'report' which lists all the meetings and what day they fall on for the rest of the year.

The data is currently stored as "Second Saturday of Every Other Month", "Quarterly on the Last Friday", etc, in other words completely textual and not standardized. If I can decipher how to make Access read this and make sense, I could finish this idea.

I know something like this is possible because Outlook and Lotus Notes both have calendar options that 'repeat'. That is what I am after, but hopefully tying it into our existing list of Neighborhoods. I have freedom to modify the fields to suit what I need to do, and currently it looks something like this:
[MeetingFreq]: "Weekly";"Every Other Week";"Every Month";"Every Other Month";"Every Three Months";"Every Four Months";"Every Six Months";"Annually"
[MeetingOrder]: "First";"Second";"Third";"Fourth";"Last";"Seco nd to Last";"Every"
[MeetingDay]: "Sunday";"Monday";"Tuesday";"Wednesday";"Thursday" ;"Friday";"Saturday"
[MeetingTime]: (Date/Time field)

Any ideas or resources to suggest?

Thanks in Advance,
David

As with many others that posted similar threads on this topic, I am new to Access AND to VBA coding so what seem as simple answers or instructions by experienced users zoom right over my head.

That said I am creating a db for my Company. I've viewed training videos and purchased a more in depth training video. What seems so simple on the video isn't working for me. I keep getting the blasted "Operation must use an updateable query" error......

******************************
Here is the background

This is for an operation that has only ONE employee on duty at a time. There is a "Crossover" when the next Employee comes in and all money is accounted for to go to next Employee.

At the beginning of a "Shift" an employee will login, start a time card which will behind the scenes initiate the tracking of a "Shift"

The unique part (at least to me) to this is that the business day does not match a typical clock. The reason is that the "Business Day" starts at say 6 AM of one calendar day and ends at 4 AM of the next day. My report totals need to tie to totals provided by a govt reporting system which Business Day ends at that 4 AM time.

Thus, I have created the following tables with the following fields:

A) tblEmployees1)EmployeeID (PK) (Auto#)
2)EmpFirstName
3)EmpLastName
B) tblShifts1)ShiftID (PK) (Auto#)
2)EmployeeID (Lookup to tblEmployees)
3)DateTimeCreatedStamp (General Date) (Dflt Value = Now())
4)ShiftStartDate (ShortDate) (Dflt Value = Now())
5)ShiftStartTime (MediumTime) (Dflt Value = Now())
6)BusinessDateofShift (ShortDate) (Blank until "Update Query Can complete)
****************************
Queries created:
A) Select Query wherein I test the ShiftStartTime Vs. a table where I define the Default "Start Time" of a Location's Business Day. This then defines the Alias: ShiftBusinessDay

ShiftBusinessDay: IIf([tblShifts]![ShiftStartTime]>[tblBusinessDay]![StartTimeOfBusinessDay],CVDate(Format([tblShifts]![ShiftStartDate],"Short Date")),CVDate(Format(DateAdd("d",-1,[tblShifts]![ShiftStartDate]),"Short Date")))


B) THEN I created a temporary table by using "Make Table" query whose SQL stmt that got created was:

SELECT tblShifts.ShiftID, tblShifts.EmployeeID, tblShifts.DateTimeCreatedStamp, tblShifts.ShiftStartDate, tblShifts.ShiftStartTime, tblShifts.CurrentDBALocationID, qryShiftsDetails.ShiftBusinessDay INTO tblTempShiftUpdateBusinessDate
FROM (tblBusinessDay INNER JOIN tblDBALocation ON tblBusinessDay.BusinessDayID=tblDBALocation.Busine ssDayStartTime) INNER JOIN (tblCurrentDBALocation INNER JOIN (tblShifts INNER JOIN qryShiftsDetails ON tblShifts.ShiftID=qryShiftsDetails.ShiftID) ON tblCurrentDBALocation.CurrentDBAID=tblShifts.Curre ntDBALocationID) ON tblDBALocation.DBALocationID=tblCurrentDBALocation .CurrentDBAID;

**************************************************

After that temp table I then tried an "Update Query" to update the tblShifts.BusinessDateofShift but received the dreaded error.

AFTER reading some posts here, I gave a whirl at creating a "Temp Table" using the "Make Table Query". However, that works ONCE and only if I link that table to the tblShifts. Once I do that I get a new error when running the "Make Table Query" a second or subsequent time that tells me it can't delete a table due to its relationship to another table.

SO NOW I'M STUCK.

Sorry, for the length of the explanation! Thank you in advance for you help.

I'm trying to write a query for a report that will find all client who have spent more than a certain amount of money in any calendar year during the stated time interval.

The user has a screen where he/she can enter a start date, an end date and a minimum amount. The minimum amount spent would indicate the minimum that the client spent in total in any given year, not during the interval of the query.

Example 1:

User selects between Jan 1, 2005 and Dec 31, 2007, and $100 as the minimum. If a client spent $100 in 2005, this client is included even though he didn't make any purchases in 2006 or 2007

Example 2:
User selects betwee Jan 1, 2006 and Dec 31, 2008 and the same $100 minimum. If a client purchased $90 in goods every year, she will be excluded because she's never made the minimum yearly purchases.

Right now I can't see how this can be done other than using some coding with looping but I'm hoping someone can figure a way to do it using queries.

Many thanks in advance

SHADOW


Not finding an answer? Try a Google search.