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

# 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

• 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

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

Hi,
a 6ya expert can help you resolve that issue over the phone in a minute or two.
best thing about this new service is that you are never placed on hold and get to talk to real repairmen in the US.
the service is completely free and covers almost anything you can think of (from cars to computers, handyman, and even drones).
goodluck!

Posted on Jan 02, 2017

×

my-video-file.mp4

×

## Related Questions:

### 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

### Can't use irc undernet server this is the error Local host: siemens (10.0.0.100) --us.undernet.org- *** Looking up your hostname --us.undernet.org- *** Checking Ident --us.undernet.org- *** Found your...

I would suggest you port forward port 113 (IDENT) port from your router to your computer. You'll need to look up instructions on how to do this, as all routers are different.

in mIRC type /identd on <some IDENT ID here>
in the above command, notice the word is IDENTD not IDENT
then try reconnect. The <some IDENT ID here> will show up in a whois as <your nickname>!<some IDENTD ID here>@<your isp>

Looking at your output that you've pasted, I'm also assuming you're using the mIRC IRC client.

Oct 10, 2009 | Computers & Internet

### 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

### Write into excel format

Your code does not save in access format it just makes a copy of existing file. You need to open table by table and export data using csv format.
It can be done using access OLE to export, or
write some VB code to access the database tables via ODBC and save row data to csv file

Feb 17, 2009 | Microsoft Visual Basic Professional...

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

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

Dec 13, 2008 | Microsoft Access 2002 for PC

### Connectivity between HTML page and Ms-Access

<HTML>
<BODY>
<%
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
%>
<%
RS.MoveNext
WEND
%>
</BODY>
</HTML>

Nov 11, 2008 | Microsoft Office Standard for PC

### 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)...

### MS access database file sharing

There could be a shared-connections issue here, as well as a table-locking issue, if you are using linked tables, as I suspect you are. If not, how are connections being managed? And finally, it could be a network issue, though if you are using linked tabled and haven't set up a row locking policy, it is probably an app-level issue.

In short: More questions to answer rather than answers to the question. :)

Feb 27, 2008 | Computers & Internet

### 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)...

### "denial of service": email, firefox, etc. cannot find server..

What told this was a "denial of service"?

Some hackers use DoS (above) it's considered the worst thing you can download, they can go from keylogging your PC, from taking over your pc anytime they like, and using your bandwidth which is called DDOS.

This is a very compllicated thing to get ride of,

One way you can test to see if your a victum of this is to check the port, here is how:

To open the MS Dos screen go to:
Start>Run>type "cmd" and press enter (without quotes)

All of the IRC Zombie/Bots open and maintain static connections to remote IRC chat servers whenever the host PC is connected to the Internet. Although it is possible for an IRC chat server to be configured to run on a port other than "6667", every instance I have seen has used the IRC default port of "6667".

Consequently, an active connection to an IRC server can be detected with the following command:

netstat -an | find ":6667"

Open an MS-DOS Prompt window and type the command line above, then press the "Enter" key. If a line resembling the one shown below is NOT displayed, your computer does not have an open connection to an IRC server running on the standard IRC port. If, however, you see something like this:

TCP 192.168.1.101:1026 70.13.215.89:6667 ESTABLISHED

. . . then the only question remaining is how quickly you can disconnect your PC from the Internet!

A second and equally useful test can also be performed. Since IRC servers generally require the presence of an "Ident" server on the client machine, IRC clients almost always include a local "Ident server" to keep the remote IRC server happy. Every one of the Zombie/Bots I have examined does this. Therefore, the detection of an Ident server running in your machine would be another good cause for alarm. To quickly check for an Ident server, type the following command at an MS-DOS Prompt:

netstat -an | find ":113 "

As before, a blank line indicates that there is no Ident server running on the default Ident port of "113". (Note the "space" after the 113 and before the closing double-quote.) If, however, you see something like this:

TCP 0.0.0.0:113 0.0.0.0:0 LISTENING

. . . then it's probably time to pull the plug on your cable-modem!

Note that a Windows IRC client program running in the PC will generate false-positive reports since these are tests for IRC client programs. So be sure to completely exit from any known IRC client programs BEFORE performing the tests above.

---------------------------------------------------------------------------------------------------------------------------------------------------

Oct 18, 2007 | Dell Inspiron 2200 Notebook

## Open Questions:

#### Related Topics:

542 people viewed this question

Level 3 Expert

Level 3 Expert