Sum Query

[zlo2]

Hello everybody,
I have a quick question, a piece of cake to most of you, I'm sure.
Here's the simplified scenario:
I have two tables:
tableA, with two fields:
fullName, daysRemaining

tableB, two fields as well:
fullName, daysUsed

daysRemaining starts out with 15 and decreases with every entry made into tableB (negatives allowed)

There will me multiple names and multiple entries for every names in tableB.

What I need is a query that updates tableA every time the user hits Save button in tableB form, adding up all entries for that name. In other words daysRemaining for a particular name should equal to 15 minus (sum of all daysUsed for that name)

I tried to keep it short but if something isn't clear, please ask and I'll clarify.

Thanks in advance.

 

[SimonB1978]

Hi,

If your DaysRemaining always start at 15, no need to keep them in a table, just run a query on tableB, something like:

SELECT fullName, 15 - (Sum(daysUsed)) AS daysRemaining FROM tableB GROUP BY fullName

You should not store data that depends on some other data.

Simon B.

 

[Brianwarnock]

This looks like the crazy idea of a tutor to illustrate a point, what point I've no idea but it could influence the way this is done.

Brian

 

[zlo2]

Thanks Simon,
your solution would work. I have yet to try it out since the real program is quite a bit more complex but I trust you know what you're doing.
I dont really understand why I shouldn't store these values but now that I think about it, I guess there's really no need since the query will only be used to populate a listbox.
Anyway, thanks for your help.

 

[SimonB1978]

Hi,

You should not store those kind of data because:

1 - It is easily computed.
2 - Data integrity will go wrong if for some reason tableB gets modified but tableA is not updated.
3 - There are other reasons if you have a look on data normalization.

Simon B.

 

[zlo2]

Hi Simon,
I have one (hopefully) last question. So here's data in the two tables described above, slightly more complex:
tableA:
City_______FullName____DaysAvailable
Toronto--__Alex--------20
Chicago--__Bob---------20

tableB:
FullName___DaysUsed
Alex-------1
Alex-------4
Alex-------10
Bob--------5
Bob--------5

What I want my listbox to look like:
City_______FullName______DaysRemaining
Toronto---_Alex-----------5
Chicago---_Bob------------10


This is the query I tried:
SELECT [tableA].City, [tableA].FullName, [tableA].DaysAvailable -(Sum([tableB].DaysUsed)) AS DaysRemaining
FROM [tableA], [tableB]
GROUP BY [tableB].FullName
ORDER BY [tableA].FullName

This is the message I'm getting:
You tried to execute a query that does not include the specified expression [tableA].City as part of an aggregate function.

Adding [tableA].City to GROUP BY didn't help.
Thanks a lot.

 

[vbaInet]

1. Create a SUM Query on DaysUsed for Table B grouping by FullName and sort by FullName.
2. Create a query based on Table A, sorty by FullName.
3. For the DaysRemaining field use a DLookup function to find the matching SUM (performed in 1) based on the FullName and subtract it from DaysAvailable.

Something like:

DaysRemaining: [DaysAvailable] - DLookup("[Sumfield]", "QueryName", "[FullName] = '" & [FullName] & "'")

This would work if the names are unique so you should use the ID instead.

 

[zlo2]

vbaInet,
I had to read your solution at least 10 times until it finally made sense, and even then it didn't.
So I went ahead and followed your instructions and, voila, it worked.
Thank you so much, I managed to brighten up my day.

 

[vbaInet]

Sometimes you need to read things over and over again lol. There were some typos so it may not completely make sense on the first few reads

Glad you got it working.

 

[SimonB1978]

Hi,

Another possibility would be to create a query like this: (air code)

SELECT A.FullName, A.City, (A.DaysAvailable - B.SumDaysUsed) 
FROM tableA A INNER JOIN
(SELECT FullName, Sum(DaysUsed) AS SumDaysUsed FROM tableB GROUP BY FullName) B ON A.FullName = B.FullName
ORDER BY A.FullName

You could also use the left join if you want those that do not figure in tableB to be included.

I don't know which method would get the best performance, and you would possibly need lots of data to see anything significant...

Simon B.

 

[vbaInet]

Yes, you could use a subquery for achieving that too.

For performance, my inclination would be in the subquery. Test both out and see.