By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,455 Members | 2,293 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,455 IT Pros & Developers. It's quick & easy.

Problem with query join(s?)

P: 8
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 ?
Nov 19 '08 #1
Share this Question
Share on Google+
11 Replies


P: 2
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
Nov 19 '08 #2

P: 8
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.
Nov 19 '08 #3

FishVal
Expert 2.5K+
P: 2,653
Expand|Select|Wrap|Line Numbers
  1. 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]
  2. FROM TBL_PLACE LEFT JOIN TBL_DATA ON TBL_PLACE.PLACE = TBL_DATA.PLACE
  3. GROUP BY TBL_DATA.PLACE, TBL_DATA.Reg, TBL_DATA.[Branch]
  4. ORDER BY TBL_DATA.PLACE, TBL_DATA.Reg, TBL_DATA.[Branch];
  5.  
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
Nov 19 '08 #4

P: 8
Would it help to get my desired output if Reg and Branch were present in a table apart ?
Nov 19 '08 #5

FishVal
Expert 2.5K+
P: 2,653
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
Nov 19 '08 #6

P: 8
@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.
Nov 27 '08 #7

FishVal
Expert 2.5K+
P: 2,653
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
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
As well as SQL for all relevant queries.

Regards,
Fish
Nov 27 '08 #8

P: 8
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.
Attached Images
File Type: jpg db result.jpg (17.5 KB, 115 views)
Nov 28 '08 #9

FishVal
Expert 2.5K+
P: 2,653
Hello, termitebe.

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

Regards,
Fish
Nov 28 '08 #10

P: 8
That did it. Thank you so much.
Dec 1 '08 #11

FishVal
Expert 2.5K+
P: 2,653
:)

You are welcome.
Dec 1 '08 #12

Post your reply

Sign in to post your reply or Sign up for a free account.