Union all and group by (2 Viewers)

[Gedy]

Hello, according to the pictures, I have tables grouped by name and totaled amounts. But now I need to group it and add it all up. So that the query contains only the name once and the sums added together. And if I don't see zero values there, I'll be happy

 

[June7]

Build another query that uses the UNION query as source to aggregate. Include filter criteria that excludes records with 0.

Why do you have 13+ tables instead of 1 table?

 

[Gedy]

Build another query that uses the UNION query as source to aggregate. Include filter criteria that excludes records with 0.

Why do you have 13+ tables instead of 1 table?

4 tables with too many fields. Second query I already have but not working with my form. I need in my form where I choose name select field where are money. Dlookup doesn't work whit it.

 

[June7]

Sorry, don't understand that. What does "not working" mean? If you don't provide code used, can't advise how to fix. If you want to provide db for analysis, follow instructions at bottom of my post.

 

[Gedy]

Sorry, don't understand that. What does "not working" mean? If you don't provide code used, can't advise how to fix. If you want to provide db for analysis, follow instructions at bottom of my post.

I tried different expressions. For example: =DLookUp("[Výpočet vaplacené peníze od číšníků Dotaz]![ Personál jméno]";"[Výpočet vaplacené peníze od číšníků Dotaz]";"[Jméno_Tabulka výplaty manažer 1].[Column](1)"="[Výpočet vaplacené peníze od číšníků Dotaz]![Personál jméno]")

It was showing me a blank field or a flashing error.

 

[June7]

Try this:

=DLookUp("[Personál jméno]";"[Výpočet vaplacené peníze od číšníků Dotaz]";"[Personál jméno]='" & [Jméno_Tabulka výplaty manažer 1].[Column](1) & "'")

I assume [Personál jméno] is a text field.

Why are you looking up [Personál jméno] when you already have that in listbox? Do you need to lookup the amount?

 

[Gedy]

Try this:

=DLookUp("[Personál jméno]";"[Výpočet vaplacené peníze od číšníků Dotaz]";"[Personál jméno]='" & [Jméno_Tabulka výplaty manažer 1].[Column](1) & "'")

I assume [Personál jméno] is a text field.

Why are you looking up [Personál jméno] when you already have that in listbox? Do you need to lookup the amount?

Yes The form is bound to the table. Some fields need to be entered manually. So he selects a name from the list (text field), it is written into the table. I need it according to the name, it looked up the information from the query and showed the amount according to the name.

 

[Gedy]

Yes The form is bound to the table. Some fields need to be entered manually. So he selects a name from the list (text field), it is written into the table. I need it according to the name, it looked up the information from the query and showed the amount according to the name.

actual

 

[Gedy]

=DLookUp("[SumOfExpr1001] ";"[Výpočet vaplacené peníze od číšníků Dotaz]";"[Personál jméno] ='" & [Jméno].[Column](1) & "'")

is working Thank you very much

 

[Pat Hartman]

4 tables with too many fields

Sounds like a normalization problem. You probably have one or more repeating groups. These are things like having 12 monthly columns rather than having data for each month in a separate row.

You probably want to use a query to do the final sum and presentation.

Access is NOT Excel