Base Query not counting/summing correctly

[Jon170579]

OS 10.15.2
Libre Office 6.3.5.2

See File: J.D. Wetherspoons.odb

in “Z_Test_Single_Qry” I have selected 4 fields 3 different tables, there is a 4th table but I am not using any fields from it. I want base to count the unique visit dates for each Pub_Name, then count total drinks purchased from that visit date as there might be more than 1 receipt per visit, then finally I want to sum the value per visit.

The problem is when I add all the tables the count on visit date is counting the total line per visit, ie if 2 drinks were bought in the same visit it is returning 2 as the answer when it should be 1.

I have managed to do 2 seperate queries “z_Test_Multiple_1” & “z_Test_Multiple_2” which does the count for visit date correct and drink count and sum correct respectively. but when I try and link these together in a third query using Pub name as a link I get a syntax error and I cannot even save the 3rd query

I need all on the fields counting/summing correctly to use in a report

[Ratslinger]

Hello,

The answer to the question is that you have not included the Visit_Date in the grouping so it is simply totaling all for a particular Pub. You would also need to include this in the select otherwise you do not know which of the visits was which.

It also may be you have overly complicated your tables and the query. Not sure why there are different table for Visit_Time and Visit_Date. With the query I would select each particle of information and then use a left join to create the single record being output.

Just trying to figure what goes to what and exactly what you expect from the report was too confusing to go any further.

I would simplify all before proceeding.

[cpb]

i think that i agree with @Ratslinger who said that the table “tbl_Visit_Time” appears to be superfluous to requirements.

the field “Time” should probably reside in the table “tbl_Visit_Date”.

anyway i’m posting a solution to your issue.

from the ‘Base’ window hit ‘Queries’ then hit ‘Create Query in SQL View’ (query design window opens).

paste the code in the query design window.

activate the ‘SQL’ icon on the toolbar and hit F5 (execute).

select p."Pub_Name", count(distinct d."Visit_ID") "CountVisits",
    count(*) "Transactions", sum(r."Value") "Receipt"
from
	(select "Visit_ID", "Pub_ID" from "tbl_Visit_Date") d
join
	(select "Visit_Time_ID", "Visit_ID" from "tbl_Visit_Time") t
	on t."Visit_ID" = d."Visit_ID"
join
	(select "Visit_Time_ID", "Value" from "tbl_Receipt") r
	on r."Visit_Time_ID" = t."Visit_Time_ID"
join
	(select "Pub_Info_ID", "Pub_Name" from "ztbl_Pub_Info") p
	on p."Pub_Info_ID" = d."Pub_ID"
group by "Pub_Name"
order by "Pub_Name"

[Ratslinger]

@cpb,

Think you missed the objective. Each visit must have separate totals. Try and pick one like The Friar Penketh - It will make your head spin just trying to connect the dots. Visit date needs to play a part in this and be a part of the result set. And then there is the question of what to do if you visit two or more different times on the same date?

[cpb]

@Ratslinger
the OP stated that each of the 2 separate queries “z_Test_Multiple_1” & “z_Test_Multiple_2” returned accurate figures but the the problem was in merging those queries. am i missing something?

[Ratslinger]

@cpb,

The question is a bit hard to read but here is one of the items:

"...then finally I want to sum the value per visit."