[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

Query problem

I really am struggling with this.
I have a form that enters the time a user clicks a button.
It is used to record the number of pallets that were made in a shift. Three shifts 08:00-16:00, 16:00-00:00 and 00:00-08:00.
I have setup three queries to get the number of pallets per shift (Day, Eve, Night) I have done another query, (qryAllDay), to group the Day,Eve,Night shifts to display a graph on the only form in the database showing pallets made over the last three shifts.
I need the graph to show only the pallets from the current shift (based on time) and the previous two shifts. I just cannot fathom how to make the query reflect this.
I have attached the database.

As always, your time is much appreciated. ToyPalls.accdb
0
Stephen Byrom
Asked:
Stephen Byrom
  • 8
  • 7
1 Solution
 
als315Commented:
Test query1 in example. Functions are in Module1
ToyPalls.accdb
0
 
Stephen ByromAuthor Commented:
Thanks for your time.
I tried using your query1 as the data for the graph and all is ok except when I add a record it adds to the current AND the Previous.
0
 
als315Commented:
I can't reproduce this effect. In query prev is 6, pprev is 5 and is not changing. Curr is increasing every time button is pressed.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
Stephen ByromAuthor Commented:
I open the ToyPalls in your reply, open the form in design mode, delete the graph and insert a new graph with the query1 as the datasource, rename it "PallGraph" and open it in form view, click the button and both Curr and Prev increment by one.
I must be doing something different to you.
0
 
als315Commented:
See corrected form. Query1 was not changed
ToyPalls.accdb
0
 
Stephen ByromAuthor Commented:
Sorry, but each time I try and get to the design view or visual basic Access stops working
0
 
als315Commented:
Decompiled, compacted and repaired
ToyPalls.accdb
0
 
Stephen ByromAuthor Commented:
Thanks, but it still adds to both columns in the graph.
All I did was open the file and click the button, it added to both curr and prev.
0
 
als315Commented:
It is very strange.
 1 2 3I've added data table to chart

ToyPalls.accdb
0
 
Stephen ByromAuthor Commented:
OK... I don't know what to do now.
I open the file and click the button... it adds to both curr and prev!!!
Are you using 2010 or 2007?
0
 
als315Commented:
I'm using Access 2010. May be problem is in time difference in our countries?
What values are added to table? Can you upload back my last example with some values added?
You can also add time and date values to query1 and check result. May be there is some error in functions.
0
 
Stephen ByromAuthor Commented:
Ok.
I opened file, added four records using the button and have saved the database to desktop.
I am now attaching said file to this reply ToyPallsFromAls.accdb ToyPallsFromAls.accdb
0
 
als315Commented:
Yes, there were some errors.
You can change all Cint in functions to Int

ToyPalls.accdb
0
 
Stephen ByromAuthor Commented:
Thank you so much for sticking with me. It's a tremendous help.
0
 
Stephen ByromAuthor Commented:
I have studied the code in the functions you kindly did for me and I am none the wiser.
Is there some literature I could study, for the syntax etc, to try and work out my own functions?
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now