Problem with query join(s?)

[termitebe]

Hi being the only (somewhat) computerliterate person in my department I have inherited an access 2000 Db wich contains 2 tables: (strangely reporting that access is not one of the programs I am somewhat literate in was not acceptable)

Simplified these tables look like this:

TBL_Data
Place number (not unique)
Name text (not unique)
Reg 1 or 2
Branch 1 or 2
Amount number (not unique)
Dosn° text (not unique)
all fields are required but oddly there is no PK since records can be present more than once

TBL_place
Place (PK) number unique
Language N or F
all fields are required

Now I would need the following:

A list which gives me a count (even if it is zero) of all the places in TBL_place
which mentions all the possible combinations of reg and branch (11, 12, 21 and 22) with the total amounts

so I would need for each Place in TBL_place 4 lines with both a count and a sum of amounts.



I tried the following:


SELECT DISTINCTROW TBL_DATA.PLACE, TBL_DATA.Reg, TBL_DATA.[Branch], Sum(TBL_DATA.Amount) AS [Sum Of Amount], Count(*) AS [Count Of TBL_DATA]
FROM TBL_DATA
GROUP BY TBL_DATA.PLACE, TBL_DATA.Reg, TBL_DATA.[Branch]
ORDER BY TBL_DATA.PLACE, TBL_DATA.Reg, TBL_DATA.[Branch];



But this gave me
1) only those lines where there is an amount
2) not the combinations of the Reg and Branch fields where there are no amounts

Then I tried this but strangely enough it does not give me all the combinations for all the places in TBL_place:

SELECT DISTINCTROW TBL_DATA.PLACE, TBL_DATA.Reg, TBL_DATA.[Branch], Sum(TBL_DATA.Amount) AS [Sum Of Amount], Count(*) AS [Count Of TBL_DATA]
FROM TBL_PLACE LEFT JOIN TBL_DATA ON TBL_PLACE.PLACE = TBL_DATA.PLACE
GROUP BY TBL_DATA.PLACE, TBL_DATA.Reg, TBL_DATA.[Branch]
ORDER BY TBL_DATA.PLACE, TBL_DATA.Reg, TBL_DATA.[Branch];

I thought this would at least give me all the places in TBL_place ?

Any advice on how to get what I need ?

[joannem]

Inyour query grid right click in the grey area and at the bottom of the list click properties

You should see a property Unique records set that to no

Alternatively remove the Distinctrow from your SQL


this will return all your records and you can start to filter from here


jo

[termitebe]

Hi,

I tried what you are suggesting however my problem is not that there are no duplicate line entries visible (since there is grouping) but that I do not get the "empty lines" for the reg/branch combinations where there are no records in TBL_data. Nor do I get all the places in TBL_place

I would like some output like this (withouth the blanks between different places that is).

Place Reg Branch Sum of Amount Count
1 1 1 1000 57
1 1 2 200 15
1 2 1 350 22
1 2 2 750 37

3 1 1 7000 52
3 1 2 0 0
3 2 1 3570 21
3 2 2 7507 32

4 1 1 0 0
4 1 2 0 0
4 2 1 0 0
4 2 2 0 0

In this example for all reg/branch combinations for place n° 4 there woudl be no data in data.

For the place n° 3 there would only be no data for the 1/2 combination.

[FishVal]

SELECT DISTINCTROW TBL_DATA.PLACE, TBL_DATA.Reg, TBL_DATA.[Branch], Sum(TBL_DATA.Amount) AS [Sum Of Amount], Count(*) AS [Count Of TBL_DATA]
FROM TBL_PLACE LEFT JOIN TBL_DATA ON TBL_PLACE.PLACE = TBL_DATA.PLACE
GROUP BY TBL_DATA.PLACE, TBL_DATA.Reg, TBL_DATA.[Branch]
ORDER BY TBL_DATA.PLACE, TBL_DATA.Reg, TBL_DATA.[Branch];
 

Hello, termitebe.

• DISTINCTROW predicate has no use in context of your query. Kill it to death.
• [Reg] and [Branch] fields are present in [TBL_DATA] only. So, if there is no record(s) in [TBL_DATA] containing certain combinations of [Reg] and [Branch] values, then they will not appear in query.
• Maximum you could obtain from your current tables' set is all places enlisted in [TBL_PLACE]. For that you need to modify your query so it fetch TBL_PLACE.PLACE instead of TBL_DATA.PLACE.

Regards,
Fish

[termitebe]

Would it help to get my desired output if Reg and Branch were present in a table apart ?

[FishVal]

Sure.

You should design two tables - one for Reg, other for Branch.
Cartesian join of these two tables with TBL_PLACES will give you all possible combinations of place/reg/branch. Further outer join with TBL_DATA will give you actually what you are looking for.

BTW, you could use them as lookup tables to enter data to TBL_DATA via comboboxes and ensure referrential integrity.

Regards,
Fish

[termitebe]

@FishVal
Thank you so much for this answer. At least I get something almost like the output I need.

However when in the output ther is a place with no records for a reg/branch the sum of amount and number of occurences remains blank.

Since I like to ge my output as complete as possible
I (tentatively) solved this with 2 nested iifs
iif(reg1=reg2,iif(branch1=branch2;sum(amount);0)
iif(reg1=reg2,iif(branch1=branch2;count(place);0)

Now i was wondering if there was not a more elegant way of doing this.

As to the second part of your reply I am afraid this is outside of my knowledge/what is asked of me. I think you mean to enter data in tbl_data I would need a form however that data is delivered by third (and fourth) parties.

Still this has been a great help.

Thank you.

[FishVal]

Hello.

@termitebe
This has no little sense for me. Please include the MetaData for all relevant datasets. Here is an example of how to post table MetaData :
Table Name=tblStudent

Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

As well as SQL for all relevant queries.

Regards,
Fish

[termitebe]

I appologize for not having been clear and for not having followed the instructions.

As you pointed out I made the tables reg and branch:

TBL_REG
Reg = number (either 1 or 2)

TBL_BRANCH
Branch = number (either 1 or 2)

Which I then made a cartesian query with the places table: QRY_Cartesian

TBL_PLACES
Placenr = number
Placename = text

Then I made a query with the suggested joins: QRY_Cartesian + data

TBL_DATA
Place number = number
Dreg = number
Dbranch = number
amount = number


QRY_Cartesian

SELECT TBL_PLACES.[Place nr], TBL_REG.Reg, TBL_Branch.Branch
FROM TBL_PLACES, TBL_REG, TBL_Branch;


QRY_Cartesian + data
SELECT QRY_Cartesian.[Place nr], QRY_Cartesian.Reg, QRY_Cartesian.Branch, Sum(TBL_Data.Amount) AS SumOfAmount, Count(TBL_Data.Place_number) AS CountOfPlace_number, IIf([reg]=[dreg],IIf([branch]=[dbranch],Sum([amount]),0),0) AS Expr1, IIf(Sum([amount])>0,Sum([amount]),0) AS Expr2
FROM QRY_Cartesian LEFT JOIN TBL_Data ON (QRY_Cartesian.Branch = TBL_Data.DBranch) AND (QRY_Cartesian.Reg = TBL_Data.Dreg) AND (QRY_Cartesian.[Place nr] = TBL_Data.Place_number)
GROUP BY QRY_Cartesian.[Place nr], QRY_Cartesian.Reg, QRY_Cartesian.Branch, TBL_Data.Dreg, TBL_Data.DBranch
ORDER BY QRY_Cartesian.[Place nr], QRY_Cartesian.Reg, QRY_Cartesian.Branch;

This gives me the output in the attachment:

I wondered if there was a more elegant (or less calculation intensive) way of getting the output of expr 1 or 2 (wich are identical) nstead of what is in column Sumofamount since the Real db contains about 19 million lines and takes some time to give the results?

The problem wich I had in my previous post with the count was due to my counting an unjoined field.

I apologize for my poor english and any further unclarity in my postings.

[FishVal]

Hello, termitebe.

Use Nz() function to replace Null value.
... Nz(Sum([amount],0) AS Expr1 ...

Regards,
Fish

[termitebe]

That did it. Thank you so much.

[FishVal]

:)

You are welcome.