I have a table GIS_Subs in MS Access 2003, wherin I would like to have running sum for each Force_No field seperately based on sorted date. The SQL is below, but this clubs the Force_no together.
SELECT GIS_Subs.Force_No AS FN, DatePart("yyyy",GIS_Subs!Subs_Dt) AS AYear, DatePart("m",GIS_Subs!Subs_Dt) AS AMonth, DSum("Subs_Amt","GIS_Subs","DatePart('m', [Subs_Dt])<=" & [AMonth] & " And DatePart('yyyy', [Subs_Dt])<=" & [AYear] & "") AS RunTot FROM GIS_Subs GROUP BY GIS_Subs.Force_No, DatePart("yyyy",GIS_Subs!Subs_Dt), DatePart("m",GIS_Subs!Subs_Dt) ORDER BY DatePart("yyyy",GIS_Subs!Subs_Dt), DatePart("m",GIS_Subs!Subs_Dt);
Thanks but it did not work out. I am able to do a Running Sum through a Macro from a Record on a Form, However I don't know how to fire this Macro each time the user moves from one record to another?Thanks but it did not work out. I am able to do a Running Sum through a Macro from a Record on a Form, However I don't know how to fire this Macro each time the user moves from one record to another?
You can't post conmments that contain an email address.
Looks like your GROUP BY subclause says to group by Force_no but also the yyyy and m datepart values for Subs_Dt as separate items. [Also since you are returning "Force_no AS FN", you need to refer to it as 'FN' and not 'Force_no' in the other subclauses of the statement. Same is true for your partial-date references as well in the SELECT subclause] So the query will return the grouping not by Force_no (should be FN) and some partial date but instead by Force_no (shoukd be FN), the year, and then the month.
The fix would be to group by a concatenation of the year and month and to change the references to the fields to be what you renamed them as in your SELECT subclause using "...AS...", for example:
SELECT GIS_Subs.Force_No AS FN, DatePart("yyyy",GIS_Subs!Subs_Dt) AS
AYear, DatePart("m",GIS_Subs!Subs_Dt) AS AMonth,
DSum("Subs_Amt","GIS_Subs","DatePart('m', [Subs_Dt])<=" &
[AMonth] & " And DatePart('yyyy', [Subs_Dt])<=" & [AYear]
& "") AS RunTot FROM GIS_Subs GROUP BY FN, AYear & "-" & AMonth ORDER BY AYear, AMonth;
So now you have only 2 GROUP BY parameters instead of three and the grouped results won't be done by FN, then year, then month, but by FN and year-month. [As for the "-" in GROUP BY subclause, you can use any character you want as the separator; I just picked "-" since it is so typical.]
The DSum function in the SELECT subclause may also need some rework; also, possibly you will need to return [Subs_Dt] in the clause as well so DSum() has it to work with. Also check to be sure that reference to [Subs_Dt] is being recognized OK without using the "table!field" form of reference. I dunno if you need it given I don't know your whole table-field definition and so can't say if there would be a naming conflict someplace.
If you are still having trouble, try starting over and building this query piece by piece, one field, one condition, one subclause, at a time until you get to where it breaks for you. That'll be the "threshold of failure" you are looking for that should point you further in the right direction.
I hope this works for you. Let me know how it goes!
- If you need clarification, ask it in the comment box above.
- Better answers use proper spelling and grammar.
- Provide details, support with references or personal experience.
Tell us some more! Your answer needs to include more details to help people.You can't post answers that contain an email address.Please enter a valid email address.The email address entered is already associated to an account.Login to postPlease use English characters only.
Tip: The max point reward for answering a question is 15.
To parse a Text field that contains two words separated by a comma, follow
these steps:
Open any existing database.
Create a table with the following structure:
Table: Parse2Words ------------------ Field Name: Empl Data Type: Text
View the Parse2Words table in Datasheet view and type the following
three records in the Empl field:
Smith, John
Callahan, Laura
Fuller, Andrew
Create the following query based on the Parse2Words table:
Query: QueryTest ------------------------------------------------------------------ Field: FirstName: Right$([Empl],Len([Empl])- InStr(1,[Empl],",")-1) Show: True Field: LastName: Left$([Empl],InStr(1,[Empl],",")-1) Show: True
NOTE: You can modify the QueryTest query to account for spaces between
the two parts in the Empl field. For example, if the text in the Empl
field is "Smith,John" without spaces, remove the -1 from the FirstName
field expression.
Run the query. Note that the QueryTest query separates the text in the Empl field into the two fields below:
FirstName LastName -------------------- John Smith Laura Callahan Andrew Fuller
If Access is anything like Excel, you can put in one of the cells:
=SUM(A1:A5)
If I remember rightly. That will add all the values from A1 to A5 together and display it. Alternatively, you can do =SUM( and then select all the values you wish to add together with your mouse by dragging, or selecting individually by holding CTRL whilst individually selecting.
Hope this helps, because I'm only going under the assumption that Access is similar to Excel. Either way, you will need to use a formula similar to the one I just showed you.
In general you initiate this process by pressing the "Relationships" button on the MS Access toolbar. It is the button with three small boxes arranged in a triangle shape.
When it first comes up it will give you the option to add tables. You can add any set of tables including the possibility of adding one table more than once (you would do this if you wanted to create a relationship between fields in the same table).
Once you see the tables you're interested in, you simply drag the field from one table over a field in another table. The fields should be those that will define the relationship. Once done, you should see an Edit Relationships window. You can get back to the Edit Relationships window later by double clicking the line in the relationships window.
MS Access does a good job of figuring out the relationship type. For example, if you match a field in one table, that is not a key, with a primary key in another table, Access will make this a Many-to-One relationship.
This can become a very deep subject. Hope this at least gets you in the right direction.
example
A1 = 5
B1 = 6
C1 = A1+B1 which will make C1 show 11
type in C1 = then move cursor (arrow key) over to A1 then type + then move cursor to B1 then hit enter - formula showing in C1 on top should show
=A1+B1 and valud in cell should show 11
The easiest way to solve this one would be to use a function that takes the Partno and a parameter, and that counts the number of occurrences of this Partno, resetting to 1 if a new Partno is found. Integrate this into a simple sorting query as a computed field. For more Access tips, see Access 2003 Code Examples.
open your table in query, follow steps to do this...
1. open new query in access
2. locate the table
3. once table selected, select field name in selected table you only want to include in query
3. once finished selecting the field save the query
4. you can now view selected field in your table and print as well
5. you can edit the query anytime if you want to add or remove field
6. query will not affect your table since this is only query.
If you just want to change the format to text in a query, you can use the Str function in your query. In MS Access, in the Field area of your query in Design View, you can type the following:
YourDesiredCharFieldName: Str([YourNumericFieldName]).
On the other hand, if you prefer to convert the data once and for all in your table, you can go to the Table Design View for that Table, change the Data Type of your numeric field to "Text", and close that window to save the changes. This will convert all of your data in that field from numeric to text permanently.
Right-click the field in the form, and select Properties. Under the ALL tab, look for the Font colour, font style, etc).
For queries, select the blank area in design view, and click the "Build" icon on the Query Design toolbar. Use the expressions as you would in Excel.
Hi smiths,
Try adding a Label to the report in Design View. In the Properties, select the control source for this field. Insert one for your vertical and horizontal calculations as needed.
Hope this helps.
Regards
DeltaC
Thanks but it did not work out. I am able to do a Running Sum through a Macro from a Record on a Form, However I don't know how to fire this Macro each time the user moves from one record to another?
×