Question about Microsoft Office Access 2003 (077-02871) for PC

1 Answer

Using DSUM in ms access grouping problem

I have this table:
Partno-------Ident---------Quote
242A111---BB0001-----1.1005
242A113---BB0002-----2.4563
242A113---BB0003-----5.3245
242A117---BB0004-----0.2376
242A208---BB0005-----0.2378
242A208---BB0006-----1.2766
242A208---BB0007-----2.4535
242A208---BB0008-----4.5673
242A509---BB0009-----1.4378

I am looking for a code for a query using DSUM to generate the following result:

Partno-------Ident---------Quote------QuotePrio
242A111---BB0001-----1.1005-----1
242A113---BB0002-----2.4563-----1
242A113---BB0003-----5.3245-----2
242A117---BB0004-----0.2376-----1
242A208---BB0005-----0.2378-----1
242A208---BB0006-----1.2766-----2
242A208---BB0007-----2.4535-----3
242A208---BB0008-----4.5673-----4
242A509---BB0009-----1.4378-----1

I have so far this code but I dont' get the wanted result
SELECT [10Calc].Partno AS Part, [10Calc].Ident, [10Calc].Quote, Val(DSum("1","10Calc","Partno<=" & Chr(34) & [Part] & Chr(34))) AS RunTot
FROM 10Calc
GROUP BY [10Calc].Partno, [10Calc].Ident, [10Calc].Quote
ORDER BY [10Calc].Partno, [10Calc].Quote;
I do hope for help on this one

Posted by on

1 Answer

  • Level 1:

    An expert who has achieved level 1.

    Hot-Shot:

    An expert who has answered 20 questions.

    Corporal:

    An expert that hasĀ over 10 points.

    Mayor:

    An expert whose answer gotĀ voted for 2 times.

  • Contributor
  • 39 Answers

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.

Posted on Feb 06, 2009

Add Your Answer

Uploading: 0%

my-video-file.mp4

Complete. Click "Add" to insert your video. Add

×

Loading...
Loading...

Related Questions:

1 Answer

I built a spreadsheet that uses a lot of DSUM equations to analyze a database of transaction information. I'm noticing that it is taking a very long time for the spreadsheet to calculate, even after...


The DSUM function is very useful, but it does use a lot of processing. Every DSUM function does a scan of every row of your table. There are a few ways you can reduce computing time.
First (and the most obvious) is to reduce the number of DSUM functions or reduce the size of your table. I presume you have already tried this.
Secondly consider using Pivot Tables to do the task, or to reduce the size of your table. There is a Pivot Table wizard under the Data menu. It's a fairly user friendly feature of Excel, so I suggest you try it out on your table. Pivot tables will be many times faster than DSUM functions because they only scan the table once. There are some tutorials available on the internet.
Third, if your DSUM functions are only summing one value in the table, then it would be much quicker if you can sort the table on the lookup value (or criteria). Then use LOOKUP, VLOOKUP or MATCH functions to find the value you're looking for. On a sorted table, these functions are many times faster that DSUM functions.
I hope this helps a little. It's hard to diagnose without seeing the spreadsheet and knowing the details of the problem you are trying to solve.

Oct 23, 2009 | Microsoft Office Excel 2003 for PC

7 Answers

Undelete records from access table


ou can try to use third party Access recovery utility to restore your lost and damaged data from .mdb files. For more details about this software:-
Access File Recovery

Apr 17, 2009 | Microsoft Access 2000 (077-01277) for PC

1 Answer

How to export table in ms access to excel


yes you can export your access data into excel, follow steps on how to export.

1. open your access database
2. open the table data you to export
3. under table data view click tools menu
4. select analyze
5. select ms excel
6. this will open your seleted access table in excel

hope this will help you..

Dec 13, 2008 | Microsoft Access 2002 for PC

3 Answers

Connectivity between HTML page and Ms-Access


<HTML>
<BODY>
<%
Set MyConn = Server.CreateObject("ADODB.Connection")
MdbFilePath = Server.MapPath("sample.mdb")
MyConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & MdbFilePath & ";"
SQL_query = "SELECT * FROM Friends"
Set RS = MyConn.Execute(SQL_query)
WHILE NOT RS.EOF
%>
<LI><%=RS("Name")%>: <A HREF="<%=RS("Link")%>">Homepage</A>
<%
RS.MoveNext
WEND
%>
</BODY>
</HTML>

Nov 11, 2008 | Microsoft Office Standard for PC

1 Answer

MS access 2003 problem


I do not know the pogramme in MS access I know the simple course of MS Access.Please kindly send me full verson of MS Access programme course in my mail Id
funa_deogarh@rediffmail.co.in

Jun 20, 2008 | Microsoft Office Access 2003 (077-02871)...

1 Answer

Creating Running Sum with Grouping Query


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!

Feb 23, 2008 | Microsoft Office Access 2003 (077-02871)...

2 Answers

Ms access


before this u should know what is databse.
ms access has various objects likw table,form,query,report etc..
table is a object which stores the actual data and form is used to enter the data in to the table and display the data from the table, while report is object which is used to desplay your data in required formate.Query is used to fetch the data from the table in customised formate.for that u should have knowledge of SQL.

Nov 14, 2007 | Business & Productivity Software

2 Answers

Ms access to excel


I do this on a routine basis.
In Access select the table or query that you want to convert to Excel.
Then File, Export,
You then will see a Dialog Box that allows you to select the format and file name.

That procedure can also be performed using Access Macros for an automated solution.

Hope this helps.

Oct 11, 2007 | Microsoft Office Standard for PC

1 Answer

MS access


Hi,
Have you tried changing your Regional settings in Control Panel? This should change your language in all MS Applications.

Best of luck
DeltaC

Oct 11, 2007 | Microsoft Office Standard for PC

3 Answers

Viewing ms Sql Server database on Access


Are you familiar with "Linking" tables in Access? In Access: File --> Get External Data --> Link Tables. This will bring up a dialog box which allows you to browse to your SQL server and select the table you want.

Aug 23, 2007 | Microsoft SQL Server Standard Edition for...

Not finding what you are looking for?
Microsoft Office Access 2003 (077-02871) for PC Logo

Related Topics:

542 people viewed this question

Ask a Question

Usually answered in minutes!

Top Microsoft Business & Productivity Software Experts

Brian Sullivan
Brian Sullivan

Level 3 Expert

27725 Answers

Les Dickinson
Les Dickinson

Level 3 Expert

18298 Answers

Tony

Level 3 Expert

2598 Answers

Are you a Microsoft Business and Productivity Software Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides

Loading...