AuditAttnSQLPractice.xlsx (9.2 KB)
I hadn’t used Microsoft Access much before, so this was good practice learning how to import from excel and the query requirements (compared to MySQL workbench).
SELECT e.Acct, e.GLAcctDesc, Count(g.ID) AS NumTrans
FROM EndingBalances AS e LEFT JOIN GeneralJournal AS g ON e.Acct = g.Acct
WHERE Abs(e.EndingDebitBalances)>=50000
GROUP BY e.Acct, e.GLAcctDesc, e.EndingDebitBalances
1 Like
Here is my solution!
This was a great practice. Thank you!
SELECT e.Acct, e.GLAcctDesc, Count(g.ID) AS NumTrans
FROM EndingBalances AS e LEFT JOIN GeneralJournal AS g ON e.Acct = g.Acct
WHERE Abs(e.EndingDebitBalances)>=50000
GROUP BY e.Acct, e.GLAcctDesc, e.EndingDebitBalances
Took me a little longer than the suggested time but once I watched the video, I was able to better understand. Thanks for the practice! Here is my solution:
SELECT e.Acct, e.GLAcctDesc, e.EndingDebitBalances, COUNT(g.ID) AS NumOfTransactions
FROM EndingBalances AS e LEFT JOIN GeneralJournal AS g ON e.Acct=g.Acct
WHERE e.Acct IS NOT NULL AND ABS(e.EndingDebitBalances)>=50000
GROUP BY e.Acct, e.GLAcctDesc, e.EndingDebitBalances
HAVING COUNT(g.ID) >=1000
ORDER BY COUNT(g.ID) DESC;
Thank you for the challenge. I will suggest how to import excel sheets on access and how to do relationships for those who really are Begginer level like me. I had to figure it out those thing first before trying to do the query.
challgenge 34.docx (37.7 KB)
Here was my query!! Thanks for the problem!
SELECT e.Acct, e.GLAcctDesc, e.EndingDebitBalances, COUNT(g.ID) AS NumOfTransactions
FROM EndingBalances AS e LEFT JOIN GeneralJournal AS g ON e.Acct=g.Acct
WHERE e.Acct IS NOT NULL AND ABS(e.EndingDebitBalances) >=50000
GROUP BY e.Acct, e.GLAcctDesc, e.EndingDebitBalances
HAVING COUNT(g.ID) >=1000
ORDER BY COUNT(g.ID) DESC;
I know the title says beginner, but boy this one took me forever, still couldn’t figure out how to pull the data from multiple tables. No problem though, ChatGTP and a little help from the solutions allowed me to get this…
SELECT e.Acct, e.GLAcctDesc, e.EndingDebitBalances, Count(g.ID) AS NumTrans
FROM EndingBalances AS e LEFT JOIN GeneralJournal AS g ON e.Acct = g.Acct
WHERE (((Abs([e].[EndingDebitBalances]))>=50000) AND ((e.Acct) Is Not Null))
GROUP BY e.Acct, e.GLAcctDesc, e.EndingDebitBalances
HAVING (((Count(g.ID))>=1000));
1 Like
This was a good practice challenge especially for practicing joining multiple tables. I had to follow along and get some help from the video but it was helpful!
This was a great refresher for my SQL skills! This is the solution I came to:
SELECT e.Acct, e.GLAcctDesc, e.EndingDebitBalances, Count(g.ID) AS NumTrans
FROM EndingBalances AS e LEFT JOIN GeneralJournal AS g ON e.Acct = g.Acct
WHERE ((Abs(e.EndingDebitBalances))>=50000) AND ((e.Acct) Is Not Null)
GROUP BY e.Acct, e.GLAcctDesc, e.EndingDebitBalances
HAVING NumTrans >=1000
ORDER BY NumTrans DESC;
This is my solution I came up with, very educational!
SELECT eb.Acct, eb.GLAcctDesc, eb.EndingDebitBalances, COUNT(gj.ID) AS NumOfTransactions
FROM EndingBalances AS eb LEFT JOIN GeneralJournal AS gj ON eb.Acct = gj.Acct
WHERE eb.Acct IS NOT NULL AND ABS(eb.EndingDebitBalances) >= 50000
GROUP BY eb.Acct, eb.GLAcctDesc, eb.EndingDebitBalances
HAVING COUNT(gj.ID) >= 1000
ORDER BY COUNT(gj.ID) DESC;
1 Like
SELECT e.Acct, e.GLAcctDesc, e.EndingDebitBalances, COUNT(g.ID) AS NumOfTransactions
FROM EndingBalances AS e LEFT JOIN GeneralJournal AS g ON e.Acct=g.Acct
WHERE e.Acct IS NOT NULL AND ABS(e.EndingDebitBalances) >= 50000
GROUP BY e.Acct, e.GLAcctDesc, e.EndingDebitBalances
HAVING COUNT(g.ID) >= 1000
ORDER BY COUNT(g.ID) DESC;
Such a great quick + easy SQL review! Here’s my solution:
SELECT e.Acct, e.GLAcctDesc, e.EndingDebitBalances, COUNT(g.ID) AS NumOfTransactions
FROM EndingBalances AS e LEFT JOIN GeneralJournal AS g ON e.Acct=g.Acct
WHERE e.Acct IS NOT NULL AND ABS(e.EndingDebitBalances) >=50000
GROUP BY e.Acct, e.GLAcctDesc, e.EndingDebitBalances
HAVING COUNT(g.ID) >=1000
ORDER BY COUNT(g.ID) DESC;
Learned several new operations with this challenge. I struggled to find the solution on my own so I needed to turn to the solution video for help. Definitely well put together!
Good Work!
Amazing Job!
It was a good review, I had forgotten about LEFT JOIN
Solution:
SELECT e.Acct, e.GLAcctDesc, e.EndingDebitBalances, COUNT(g.ID) AS NumOfTransactions
FROM EndingBalances AS e LEFT JOIN GeneralJournal AS g ON e.Acct=g.Acct
WHERE e.Acct IS NOT NULL AND ABS(e.EndingDebitBalances) >=50000
GROUP BY e.Acct, e.GLAcctDesc, e.EndingDebitBalances
HAVING COUNT(g.ID) >=1000
ORDER BY COUNT(g.ID) DESC;
Here is my solution
SELECT e.Acct, e.GLAcctDesc, Count(g.ID) AS NumTrans
FROM EndingBalances AS e LEFT JOIN GeneralJournal AS g ON e.Acct = g.Acct
WHERE e.Acc IS NOT NULL AND ABS(e.EndingDebitBalances)>=50000
GROUP BY e.Acct, e.GLAcctDesc, e.EndingDebitBalances
HAVING COUNT(g.ID)>=1000
ORDER BY COUNT(g.ID) DESC
;