Hi being the only (somewhat) computerliterat e 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.Am ount) 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.Am ount) 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 ?
11 1533
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
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 2,653
Recognized Expert Specialist -
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
Would it help to get my desired output if Reg and Branch were present in a table apart ?
FishVal 2,653
Recognized Expert Specialist
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
@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,i if(branch1=bran ch2;sum(amount) ;0)
iif(reg1=reg2,i if(branch1=bran ch2;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 2,653
Recognized Expert Specialist
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
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.Bran ch
FROM TBL_PLACES, TBL_REG, TBL_Branch;
QRY_Cartesian + data
SELECT QRY_Cartesian.[Place nr], QRY_Cartesian.R eg, QRY_Cartesian.B ranch, Sum(TBL_Data.Am ount) AS SumOfAmount, Count(TBL_Data. Place_number) AS CountOfPlace_nu mber, 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.DBranc h) AND (QRY_Cartesian. Reg = TBL_Data.Dreg) AND (QRY_Cartesian.[Place nr] = TBL_Data.Place_ number)
GROUP BY QRY_Cartesian.[Place nr], QRY_Cartesian.R eg, QRY_Cartesian.B ranch, TBL_Data.Dreg, TBL_Data.DBranc h
ORDER BY QRY_Cartesian.[Place nr], QRY_Cartesian.R eg, QRY_Cartesian.B ranch;
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 2,653
Recognized Expert Specialist
Hello, termitebe.
Use Nz() function to replace Null value.
... Nz(Sum([amount],0) AS Expr1 ...
Regards,
Fish
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Dave |
last post by:
Hi all,
I've been trying to figure this out for the last day and a half and
it has me stumped. I've got a web application that I wrote that
keeps track of trading cards I own, and I'm moving it from an
Access 2000 database to a SQL Server 2000 database. Everything
worked perfectly in Access, but I'm having trouble getting data to
display in SQL Server.
For reference, here's the query. It's big and nasty, but I thought
|
by: krystoffff |
last post by:
For the following query, I have a little problem ...
First, I have to rewrite several times the same query because the
alias are not recognised in the same query ( I got an error when I try
to reuse the alias "nb_bogus_leads", for instance). Do you have a way
to avoid this ? Because If I do so, the same query is calculated twice
....
Second problem, the most important :
The A.id should be for each result returned in A.*, and there...
|
by: Bruce Duncan |
last post by:
I'm a bit new to MySQL (know MS SQL well...and that may be the
problem...getting the syntax confused) and I'm having a join problem...can
anyone offer some help? Here's my problem:
I have table1 that needs to "left" join to table1A, table1B, and table1C
which is corrently done with the following:
select table1.x, table1a.y, table1b.z, table1c.q
from table1
left join table1a on table1.ID = table1a.ID
left join table1b on table1.ID =...
|
by: Andrew McNab |
last post by:
Hi folks,
I have a problem with an MS Access SQL query which is being used in an
Access Report, and am wondering if anyone can help.
Basically, my query (shown below) gets some records from a couple of
tables in my database using INNER JOINS and the WHERE clause to
specify the required constraints. However, I also want to read two
fields from a *single* record from a table called 'Locations' and then
apply one of these field's values...
|
by: Andrew McNab |
last post by:
Hi folks,
I have a problem with an MS Access SQL query which is being used in an
Access Report, and am wondering if anyone can help.
Basically, my query (shown below) gets some records from a couple of
tables in my database using INNER JOINS and the WHERE clause to
specify the required constraints. However, I also want to read two
fields from a *single* record from a table called 'Locations' and then
apply one of these field's values...
| |
by: mollyf |
last post by:
I'm creating a query, which I want to use in code in my VB.NET app.
This query produces the correct results when executed in Access:
SELECT tblEncounters.EncounterBeginDT, Query11.RID, Query11.LName,
Query11.FName, Query11.MI FROM tblEncounters INNER JOIN Query11 ON
tblEncounters.RID = Query11.RID;
Query11 looks like this:
SELECT tblCases.RID, tblCases.LName, tblCases.FName, tblCases.MI,
|
by: PW |
last post by:
I've created an ASP application which uses an Access database.
I've created an outer join query, but for some reason the "Property_Def"
column is not aligning with the "ESPN" column. They should be aligned on
the ESPI common columns.
In this attached image, the first lines ESPI of 010471 "ADJUSTABILITY"
which should line up with the "Property_Def" column that says "An indication
of whether or not the item is adjustable".
|
by: imranpariyani |
last post by:
Hi
i have a severe performance problem with one of my views which has 6 to 8 joins .. any help will be appreciated..
the view is:
CREATE OR REPLACE VIEW thsn.trade_view AS
SELECT tra.tra_id, tra.per_id, tra.fir_id, tra.tra_dcn, tra.tra_startdate::date AS tra_startdate, tra.tra_enddate::date AS tra_enddate, tra.tra_highprice, tra.tra_lowprice, tra.tra_shares, tra.tra_marketvalue, tra.tra_commonsharesheld, tra.tra_directsharesheld,...
|
by: HC |
last post by:
Hello, all, I started out thinking my problems were elsewhere but as I
have worked through this I have isolated my problem, currently, as a
difference between MSDE and SQL Express 2005 (I'll just call it
Express for simplicity).
I have, to try to simplify things, put the exact same DB on two
systems, one running MSDE and one running Express. Both have 2 Ghz
processors (one Intel, one AMD), both have a decent amount of RAM
(Intel system...
|
by: MLH |
last post by:
Gentlemen: I am having one heck of a time taking a DAO walk through
the records in an SQL dynaset. I'm trying to walk a set of records
returned by a UNION query. I'm attempting to filter the records to
those related to vehicle #60 ( = 60 ). If I explicitly
specify 60 in the SQL ==everything works fine. Take a look:
100 PString = "SELECT & " & Chr$(&H22) & Space(1) &
Chr$(&H22) & " & AS Recipient "
120 PString = PString & "FROM...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |