Search results

Alright! But that presents another problem. With data that exists before the current year, ALL purchases are being counted. We reset all rewards every year. Using this SQL I get all the information necessary, just not sure how to make it work. The data given in the shared backend file...

Using this I get an operator error. I'm not the best at SQL, and it's showing. SELECT MemberID, SUM(PurchaseAmount) AS SumOfPurchaseAmount, COUNT(MemberID) AS CountOfIssueDate FROM tbPurchases INNER JOIN tblRewards ON tbPurchases.MemberID = tblRewards.CustomerID INNER JOIN tblRewards ON...

JOIN expression not supported.

My math was wrong, I did it in my head and left some out, sorry about that. My mistake. You are correct on all accounts here. I think you meant MemberID=1, in which case you are correct, there are 3 records, I'm an idiot and can't count lol. Sorry again. In both cases, you are absolutely...

plog, I'm not entirely sure what you want for data? I thought my previous posts explained what I wanted. Starting data from tables: Google Drive, or example data above. Ending data from query: MemberID, Sum of PurchaseAmount, and a Count of purchases in tbPurchases where PurchaseDate > Jan 1st...

I need to change Count(*) to Count(Where tbPurchases.PurchaseDate is between (Max(tblRewards.IssueDate WHERE CustomerID = tbPurchases.MemberID)). What I want to accomplish, in addition to what I already have working in the database (thanks for that help earlier!), is to pull all purchases for a...

What do you need differently from this? I went through and edited out anything overly personal in the data, so I'll just share both of the split files on my Google Drive. Google Drive Folder

Hi there! I just re-read my awesomely long posts describing my database and realized I never really got to the core of what I'm asking for help with. TL;DR version: In this query (below), I need to change Count(*) to Count(Where tbPurchases.PurchaseDate is between (Max(tblRewards.IssueDate...

EXAMPLE DATA - Relationship breakdown in original post tblMembers: ID FirstName LastName Address ZipCode E-mail Address MobilePhone HomePhone Country/Region Notes JoinedDate 1 Jason Something 110 Something 44485 jason@something.net 222-555-7777 222-556-8888 US Normal hair 1/1/2019 2 John...

Hey guys! Sorry for taking so long to get back to this. I've had the flu all weekend and haven't been able to work on this project. But it's Monday, and I'm back at it. First off, this is going to be long. SORRY! I can always share the database itself with you guys if you want, but I'd...

Alright, I made a whole lot of progress today, but I ended with another issue with my first query (qryCountCurrentYear) where it's not calculating the number of purchases AFTER the last Rewards Issue Date. Talking the SQL statement out, it makes sense. But it's not only counting records where...

I won't have time to look at this until tomorrow (off work now), but to answer your final question, I need the SUM(tbPurchases.PurchaseAmount) by MemberID where tbPurchases.PurchaseDate > tblRewards.MAX(IssueDate). Does that sytax seem like it'll work? I'll tinker with this in the morning...

Hi there! First off, I'm a long-time reader but first time account-creator and poster. So, hi! I have a query I'm struggling to create. I need to SELECT and COUNT records from tbPurchases where tbPurchases.PurchaseDate is > Last(tblRewards.IssueDate) WHERE CustomerID = tbPurchases.MemberID...