Hi everyone, I'm having some trouble with a query. I have a history table where one of the fields is a date stamp. My query takes a date from the user, and I want the query to return all records from the history table with a date stamp equal to or less than the last day of the last month.
Therefore, if the user enters February 15, 2011 when prompted for a date, I want the query to return all records dated January 31, 2011 or earlier....
Coolboy55
Last Activity: Dec 20 '11, 09:17 PM
Joined: Jul 20 '07
Location:
-
How to get dates before the last day of last month?
-
Sorting Columns in a Query?
I have a query that returns a varying number of columns and one value per column, which is a sum. I would like to sort the columns based on the value returned for that column.
The end goal is to create a pareto chart from this data. If it is possible to sort columns in the chart itself, that would be fine too. -
Nevermind, I found the problem. It turns out that if I use WHERE instead of GROUP BY and HAVING in the subquery, the results in the query are screwed up. -
Last Function in Query
I have a subquery where I am sorting first by ProductNumber and then by DateStamp.
I want to find the last quantity of each product on a given date (the quantity changes throughout the day). My query currently groups by ProductNumber, looks for Max DateStamp, and Last Quantity. But it is not working properly. The Quantity never seems to be the actual last quantity, but something in the middle somewhere, even though I can see that... -
Value Checking / Grouping SQL Query
I have a query that returns three fields: OrderID, ProductID, and Satisfied. Each Order can have multiple Products associated with it. Satisfied is a yes/no field that tells me if the order quantity for the Product has been shipped. So I might have an order where the order quantity for one product was satisfied but the order quantity for another product in the same order was not satisfied. For example:
OrderID ProductID Satisfied... -
Thank you both for your answers. I ended up using both solutions at once, but for different things. Thanks again!Leave a comment:
-
Problem Comparing Dates!
I have a general date field in a table. When a user opens a particular report, a form opens that asks them to enter a date (short date format) to filter the report by. I want the report to display all records with a date less than or equal to the entered date.
Of course, the problem is that the date entered (in other words, the "equal to" case) returns no data since the general date is LARGER than the short date for the... -
Great, thanks!Leave a comment:
-
Never mind, I found the answer:
DBEngine(0).Use rs(CurrentUser) .Groups(GroupNa me).NameLeave a comment:
-
Get current user's group name?
How can I get the current user's group name within a database? I'd like to set certain properties based on who is logged in. I have found many descriptions of how to get the Windows user name, but I want the names and groups assigned in the MDW file, not the Windows name.
Thanks! -
SQL Query Help :)
Ok I think this is a tough one. I have two tables: an Inventory table and an Inventory History table. The Inventory table has the current Quantity of each Product. The Inventory History table has the Quantity of each Product whenever it changed, along with the Date when it changed.
I'm trying to create a query that takes a date from the user as a parameter and gives the Quantity of each Product on that day from the Inventory History... -
Purpose of OLE.Class?
What is the function of the OLE.Class? It seems to me that it makes no difference what its value is set to. I can open many file formats whether I set it to Paint, Word.Document.8 , Adobe Acrobat 7.0, or whatever. Can anyone explain to me what it actually is for? :( -
How to create chart in report based on data in report?
I'm sure this can't be as difficult as it seems, but I'm trying to create a simple chart in a report in Access 2003. The report is based on a query and I'd like to display the data in a chart. Any pointers? :( -
Creating a Chart in a Report
I have a report with a number of subreports. Each subreport has in its report footer a text box with a sum such as "Total=Sum([UnitPrice]*[Quantity])".
On the main report, under each subreport I have a text box like "Total1=[SubReport1].[Report].[Total]".
I want to create a pie chart on the report with all the totals (i.e. Total1, Total2, Total3...). How would I go about doing that? :( -
Oh wow, that was embarrassingly simple. :) Thanks a lot!Leave a comment:
-
SQL Query Help :D
I have a subquery with the following fields (among others) that are pulled from various tables: AssemblyName, PartName, UnitPrice, Quantity, and Cost. Cost is simply UnitPrice * Quantity. Each assembly has many parts, and each part has its own unit price and quantity.
Now I am trying to make a new query that pulls the total part cost for each assembly. I have gotten the part cost sum successfully, but I'm not sure how to add the... -
Great, that pointed me to the solution. At first it wasn't working because my append query had a problem: it was pulling data from the table (which is not updated until after the AfterUpdate event) rather than from the form control, which is already updated to the new value. Problem solved. Thanks!Leave a comment:
-
Thanks for your response. Essentially I am keeping a price change history, and I only want the record to be written to the price history table when the price is modified. The field value has not updated yet when the BeforeUpdate form event runs. If I use the AfterUpdate form event, the value is updated, but I can't compare the current Field.Value with the Field.OldValue to see if they are different since the old value has already been updated...Leave a comment:
-
Annoying AfterUpdate/Exit Problem
When the value of a field is changed, I'm passing the new value into a history table along with a date stamp. Since the old value is not updated until the Exit event, I can't use the AfterUpdate event to pass the new value to the history table. Of course, the problem is that every time I exit the field, whether I change it or not, it wants to write the value to the history table.
I want to write the new value to the history table... -
Ignore or Delete
I figured it out. This thread can be deleted.Leave a comment:
No activity results to display
Leave a comment: