查询连接问题（s？） [英] Problem with query join(s?)

问题描述

你是我所在部门中唯一（有点）计算机上的人，我继承了一个包含2个表的访问2000 Db :(奇怪的是报告访问不是我有点识字的程序之一是不可接受的）


简化这些表格如下：


TBL_Data

地点编号（不是唯一的）

姓名文字（不是唯一的）

Reg 1或2

分行1或2

金额（不是唯一的）

Dosn°文本（不是唯一的）

所有字段都是必需的，但奇怪的是没有PK，因为记录可以出现多次


TBL_place

地方（PK）编号唯一

语言N或F

所有字段均需要


现在我需要以下内容：


一个列表，它给我一个TBL_place所有位置的计数（即使它是零）

提到了所有的reg和分支（11,12,21和22）的可能组合总金额


所以我需要TBL_place 4行中的每个地方都有一个计数和一笔金额。



>
我尝试了以下方法：


SELECT DISTINCTROW TBL_DATA.PLACE，TBL_DATA.Reg，TBL_DATA。[Branch]，Sum（TBL_DATA.Amount）AS [金额总和]，计数（*）AS [TBL_DATA计数]

来自TBL_DATA

GROUP BY TBL_DATA.PLACE，TBL_DATA.Reg，TBL_DATA。[分支]

ORDER BY TBL_DATA.PLACE，TBL_DATA.Reg，TBL_DATA。[分支];



但这给了我

1）只有那些有金额的行

2）不是没有金额的Reg和Branch字段的组合


然后我尝试了这个，但奇怪的是它没有给我TBL_place中所有地方的所有组合：

SELECT DISTINCTROW TBL_DATA.PLACE，TBL_DATA.Reg，TBL_DATA。[分支]， Sum（TBL_DATA.Amount）AS [金额总和]，计数（*）AS [TBL_DATA计数]

来自TBL_PLACE LEFT JOIN TBL_DATA ON TBL_PLACE.PLACE = TBL_DATA.PLACE

GROUP BY TBL_DATA.PLACE，TBL_DATA.Reg，TBL_DATA。[分支]

订购BY TBL_DATA.PLACE，TBL_DATA.Reg，TBL_DATA。[分支];


我以为这至少会给我TBL_place的所有地方？


关于如何获得我需要的任何建议？

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 ?

推荐答案

在你的查询网格中右键单击灰色区域并在列表底部单击属性


您应该看到一个属性唯一记录设置为否


或者从SQL中删除Distinctrow



这将返回你的所有记录，你可以从这里开始过滤



jo

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

我尝试了你的建议然而我的问题不是没有重复的行条目可见（因为有分组）但是我没有得到空行& QUOT;对于在TBL_data中没有记录的reg / branch组合。我也没有得到TBL_place的所有地方


我想要一些像这样的输出（没有不同地方之间的空白）。


Place Reg分钟金额总和

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


在此示例中，对于位置为n°4的所有reg / branch组合，woudl数据中没有数据。


对于n°3的位置，只有1/2组合没有数据。

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.

展开 | 选择 | Wrap | 行号

这篇关于查询连接问题（s？）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！