Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Can you help me adapt this existing code?

Status
Not open for further replies.
Wend5

Wend5

Technical User
Sep 30, 2003
9
US
I would like to include in Deposits only those totals that have a media type of 1. (i.e. [E1 Payment Media Number] = 1.) How would I change this code to screen out any other payment media numbers?


' Retrieve the Cash Deposits for each store
sSQL = "SELECT [Store Number],"
sSQL = sSQL & " [Posting Date],"
sSQL = sSQL & " Sum([E1 Payment Media Ttl]+[E2 Payment Media Ttl]+[E3 Payment Media Ttl]+[E4 Payment Media Ttl]+[E5 Payment Media Ttl]+[E6 Payment Media Ttl]+[E7 Payment Media Ttl]+[E8 Payment Media Ttl]) AS Deposits"
sSQL = sSQL & " FROM D_DEP"
sSQL = sSQL & " GROUP BY [Store Number], [Posting Date]"
sSQL = sSQL & " HAVING [Posting Date]= #" & Format(gdPostingDate, "mm/dd/yyyy") & "#;"
Set rs = db.OpenRecordset(sSQL)


Thanks for your help.
 
Sort by date Sort by votes
Hi,

Try this...
Code: 
    sSQL = "SELECT [Store Number],"
    sSQL = sSQL & " [Posting Date],"
    sSQL = sSQL & " Sum([E1 Payment Media Ttl]+[E2 Payment Media Ttl]+[E3 Payment Media Ttl]+[E4 Payment Media Ttl]+[E5 Payment Media Ttl]+[E6 Payment Media Ttl]+[E7 Payment Media Ttl]+[E8 Payment Media Ttl]) AS Deposits"
    sSQL = sSQL & " FROM D_DEP"
    sSQL = sSQL & " WHERE [E1 Payment Media Number] = 1"
    sSQL = sSQL & " GROUP BY [Store Number], [Posting Date]"
    sSQL = sSQL & " HAVING [Posting Date]= #" & Format(gdPostingDate, "mm/dd/yyyy") & "#;"
Hope this helps :)

Skip,
Skip@TheOfficeExperts.com
 
Upvote 0 Downvote
It works great for E1 Payment Media, but I also need it to screen E2, E3, E4, E5, E6, E7 & E8.

Any additional suggestions?
 
Upvote 0 Downvote
Wend,

It depends how the select criteria is stated.

Is it (in shorthand)

WHERE
E1 = 1 AND
E2 = something AND
E3 = somethingelse....

or is it

WHERE
E1 = 1 OR
E2 = something OR
E3 = somethingelse....

or is it some other criteria???

Skip,
Skip@TheOfficeExperts.com
 
Upvote 0 Downvote
I think I understand what you are asking. I want:

E1 Payment Media Ttl to add to Deposits only when E1 Payment Media Number is 1.

Thenk I want E2 Payment Media Ttl to add into Deposits only when E2 Payment Median Number is 1.....

What I am trying to achieve is separate 1= Cash Deposits from 2 = Credit Cards.

Unfortunately, I can't dictate to the stores which Media Numbers E1 or E2...E8 are cash and which are credit cards. The program just assigns them numerically and some stores make 3 deposits, some make 2, some don't have credit cards, so do....etc.

I cannot express enough, how grateful I am for your help.


 
Upvote 0 Downvote
Unfortunately, you are dealing with NON-NORMALIZED DATA in your table. If it were NORMALIZED, you would not have this problem.

Here's what could be done ...
Code: 
    sSQL = "SELECT [Store Number],"
    sSQL = sSQL & " [Posting Date],"
    sSQL = sSQL & " Sum([E1 Payment Media Ttl]) AS Deposits"
    sSQL = sSQL & " FROM D_DEP"
    sSQL = sSQL & " WHERE [E1 Payment Media Number] = 1"
    sSQL = sSQL & " GROUP BY [Store Number], [Posting Date]"
    sSQL = sSQL & " HAVING [Posting Date]= #" & Format(gdPostingDate, "mm/dd/yyyy") & "#"
    sSQL = sSQL & " UNION"
    sSQL = sSQL & "SELECT [Store Number],"
    sSQL = sSQL & " [Posting Date],"
    sSQL = sSQL & " Sum([E2 Payment Media Ttl]) AS Deposits"
    sSQL = sSQL & " FROM D_DEP"
    sSQL = sSQL & " WHERE [E2 Payment Media Number] = 1"
    sSQL = sSQL & " GROUP BY [Store Number], [Posting Date]"
    sSQL = sSQL & " HAVING [Posting Date]= #" & Format(gdPostingDate, "mm/dd/yyyy") & "#"
    sSQL = sSQL & " UNION"
    sSQL = sSQL & "SELECT [Store Number],"
    sSQL = sSQL & " [Posting Date],"
    sSQL = sSQL & " Sum([E3 Payment Media Ttl]) AS Deposits"
    sSQL = sSQL & " FROM D_DEP"
    sSQL = sSQL & " WHERE [E3 Payment Media Number] = 1"
    sSQL = sSQL & " GROUP BY [Store Number], [Posting Date]"
    sSQL = sSQL & " HAVING [Posting Date]= #" & Format(gdPostingDate, "mm/dd/yyyy") & "#"
    sSQL = sSQL & " UNION"
    sSQL = sSQL & "SELECT [Store Number],"
    sSQL = sSQL & " [Posting Date],"
    sSQL = sSQL & " Sum([E4 Payment Media Ttl]) AS Deposits"
    sSQL = sSQL & " FROM D_DEP"
    sSQL = sSQL & " WHERE [E4 Payment Media Number] = 1"
    sSQL = sSQL & " GROUP BY [Store Number], [Posting Date]"
    sSQL = sSQL & " HAVING [Posting Date]= #" & Format(gdPostingDate, "mm/dd/yyyy") & "#"
    sSQL = sSQL & " UNION"
    sSQL = sSQL & "SELECT [Store Number],"
    sSQL = sSQL & " [Posting Date],"
    sSQL = sSQL & " Sum([E5 Payment Media Ttl]) AS Deposits"
    sSQL = sSQL & " FROM D_DEP"
    sSQL = sSQL & " WHERE [E5 Payment Media Number] = 1"
    sSQL = sSQL & " GROUP BY [Store Number], [Posting Date]"
    sSQL = sSQL & " HAVING [Posting Date]= #" & Format(gdPostingDate, "mm/dd/yyyy") & "#"
    sSQL = sSQL & " UNION"
    sSQL = sSQL & "SELECT [Store Number],"
    sSQL = sSQL & " [Posting Date],"
    sSQL = sSQL & " Sum([E6 Payment Media Ttl]) AS Deposits"
    sSQL = sSQL & " FROM D_DEP"
    sSQL = sSQL & " WHERE [E6 Payment Media Number] = 1"
    sSQL = sSQL & " GROUP BY [Store Number], [Posting Date]"
    sSQL = sSQL & " HAVING [Posting Date]= #" & Format(gdPostingDate, "mm/dd/yyyy") & "#"
    sSQL = sSQL & " UNION"
    sSQL = sSQL & "SELECT [Store Number],"
    sSQL = sSQL & " [Posting Date],"
    sSQL = sSQL & " Sum([E7 Payment Media Ttl]) AS Deposits"
    sSQL = sSQL & " FROM D_DEP"
    sSQL = sSQL & " WHERE [E7 Payment Media Number] = 1"
    sSQL = sSQL & " GROUP BY [Store Number], [Posting Date]"
    sSQL = sSQL & " HAVING [Posting Date]= #" & Format(gdPostingDate, "mm/dd/yyyy") & "#"
    sSQL = sSQL & " UNION"
    sSQL = sSQL & "SELECT [Store Number],"
    sSQL = sSQL & " [Posting Date],"
    sSQL = sSQL & " Sum([E8 Payment Media Ttl]) AS Deposits"
    sSQL = sSQL & " FROM D_DEP"
    sSQL = sSQL & " WHERE [E8 Payment Media Number] = 1"
    sSQL = sSQL & " GROUP BY [Store Number], [Posting Date]"
    sSQL = sSQL & " HAVING [Posting Date]= #" & Format(gdPostingDate, "mm/dd/yyyy") & "#;"


Skip,
Skip@TheOfficeExperts.com
 
Upvote 0 Downvote
After importing your suggestion I get the following error message:

Access Message Info: Operation not supported on a Paradox table with no primary key.

Any thoughts?
 
Upvote 0 Downvote
I had already placed a space before Select on E2 - E8.

Putting the space after Union results in the same error.

Would it help to speak directly. My number is (317)841-0909

Terri
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

thanhthanh12
  • Locked
  • Question
Help vba
Replies
3
Views
478
combo
combo
SLW89
  • Locked
  • Question
VBA newbie needs help
Replies
4
Views
422
Andrzejek
Andrzejek
Kymenglish
  • Locked
  • Question
Automated data entry code in MS Excel VB - Edge code how to hit enter to remove popups
Replies
3
Views
363
strongm
strongm
morechocolate
  • Locked
  • Question
run-tune error 3704 operation is not allowed when the object is codes: I don't think so.
Replies
7
Views
927
morechocolate
morechocolate
remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
1K
remeng
remeng

Part and Inventory Search

Sponsor

Back
Top