473,327 Members | 2,071 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

how to SUM and return distinct ?? or Distinct SUM ?? Access 2007

Hello All,
Am trying to perform this query having the followig table:
Expand|Select|Wrap|Line Numbers
  1. -------------------------------
  2. Color   |   Flag |   qty  |  u.p
  3. --------------------------------
  4. red     | 00     |   5    |   6
  5. red     | 00     |   12   |   2
  6. blue    | 00     |   13   |   1.8 
  7. blue    | 01     |    1   |   4
  8. red     | 02     |   10   |   2 
  9. red     | 01     |   2    |   2
  10. blue    | 00     |   6    |   2
  11. blue    | 02     |   6    |   2
  12. -----------------------------------
  13.  
the criteria is the flag, lets say user chose 00, then the result should be

Expand|Select|Wrap|Line Numbers
  1. -------------------------------
  2. Color   |   Flag | Total
  3. -------------------------------
  4. Red     |   00   |   54
  5. blue    |   00   |   35.4
  6. ---------------------------
  7.  
Where total = SUM(qty * u.p)

it is causing me a big headache ,
Any suggestions ???

Thank you very much in advance.
Mar 28 '11 #1
3 3103
Rabbit
12,516 Expert Mod 8TB
There's no need for a distinct clause, you just need a group by.
Mar 28 '11 #2
Hey Rabbit

Can you suggest your sql query plz ??
Mar 28 '11 #3
Thanks rabit for the hint i think i figured it out :
Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. SELECT color, flag, sum(qty*u.p) as total
  4. FROM table
  5. WHERE flag = "00"
  6. GROUP BY color;
  7.  
Mar 28 '11 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

27
by: Wayne | last post by:
I've been clicking around Access 2007 Beta 2 and can't see the custom menu bar designer. Is it in the beta? Maybe I'm blind. The question that comes to mind is: Will custom menu bars be the same...
49
by: Allen Browne | last post by:
If you are looking for opinon on what's useful in Access 2007, there's a new article at: http://allenbrowne.com/Access2007.html Covers what's good (useful features), what's mixed (good and bad),...
1
prn
by: prn | last post by:
Hi folks, I'm relatively new to Access, but I seem to have drawn the short straw, so I have the assignment for my workplace of looking for problems/inconsistencies in migrating applications to...
5
by: ARC | last post by:
Just found out something interesting with Access 2007... In table design, if you set a memo field to the new rich text option, and put that control on a form, set the control to rich text, you can...
17
by: Neil | last post by:
A client of mine likes some of the new bells and whistles in Access 2007, and is thinking about converting our A03 format MDB to an A07 format file. However, while some of the users have A07, many...
5
by: Hollywood | last post by:
Good day dear members of the comp.databases.ms-access newsgroup. Please I need your help on the following. I'm the administrator of an Access 2007 database. I would like my enterprise personnel...
8
by: elias.farah | last post by:
Hello Everyone, I'm having some very weird behavior on a couple of Access forms. (Not all forms, just some of them). The forms have been working for years, under Access XP/2003 etc, and last...
1
by: PatdeLux | last post by:
Hi, I have have an Access 2003 application with Sql Express as back end. After migrating the front end to Access 2007, users have a serious problem: One form contains text controls bound to...
9
by: prakashwadhwani | last post by:
Hi !! I'm about to develop a new project for a client. Should I go about it in Access 2003 or 2007 ? Purchasing it either for me or for my client is not a major consideration here ... what I'd...
1
by: ARC | last post by:
I think I made a major blunder, and now Access 2007 no longer opens. I made the mistake yesterday of using the "test" option in wise installer for my Access 2007 runtime app. It actually installed...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.