inner join

"som ikke bevirket den recordrangen i den anden tabel.." = "som ikke bevirker recordrangen i den anden tabel.."

kan det lade sig gøre eller skal jeg have to recordsets åbne på en gang??

your SELECT doesnt look right to me and as I dont know how your tables are related it isnt easy to give a solution.

The syntax for joining three tables with INNER joins is something like this

SELECT
FROM (T1 INNER JOIN T2 ON T1.ID = T2.ID) INNER JOIN T3 ON T1.ID = T3.ID;

SELECT * ....

Try using Access's query builder, you can do it all visually!

Looking at your previous question I have added the idle table to the SQL

SELECT singlesystem.*, testing.*, idle.*, singlesystem.location, testing.[test platform]
FROM (testing INNER JOIN singlesystem ON testing.[test platform] = singlesystem.name) INNER JOIN idle ON testing.[test platform] = idle.testSystem
ORDER BY singlesystem.location, testing.[test platform];

hvis jeg skal bruge access skal jeg til at oprette relationer imellem tabellerne, fordi jeg bruger forskellige navne, og jeg er ikke lige inde i access visuelt, og det ser ud som om, jeg skal bruge lang tid på at finde ud af det.. men jeg kan prøve at vise realationerne her..

tbl1.system
tbl2.system
tbl3.system

jeg skal have alle records, hvor:
  tbl3.system = tbl2.system
og
  tbl3.system = tbl1.system

altså skal outputtet være tbl1 og tbl2.. de har begge kolonnen system, og denne skal der soteres efter.

hvordan bliver data behandlet i dit svar? bliver recordsne fra de 2 tabeller lagt sammen, eller får de forskellige record? tabellerne skal ikke lægges sammen, men hver record skal have sin egen row..
Når jeg udskriver skal recordsne komme i denne rækkefølge (eksempel), hvor der er sorteres [system] ASC

tbl1.system (system = "a")
tbl2.system (system = "a")
tbl1.system (system = "b")
tbl1.system (system = "c")
tbl2.system (system = "c")

You do NOT need to make relationships between the tables. You can do this in query builder using drag and drop!

If you can send me you dB then I can maybe do it for you and then you can see how its done in a query!

eksperten@NOSPAMsanthell.dk

den fylder 30 MB, så jeg kan ikke sende dig den.. men jeg kan skrive den her.

when you select using INNER join then data from the related records are shown in the same row.

You can use a UNION join to select records from one or more tables so that the data is shown in their own row.

SELECT fld1, fld2 FROM Tbl1
UNION
SELECT fld1, fld2 FROM Tbl2

When using UNION joins the data type for each field (column) must be the same

If you comapct the database then it will not fill so much. Then you can zip it!


Try giving an example of your tables and data, and also what you want to see in the result

[singlesystem]
name varchar (255)
location varchar (255)

[testing]
test platform varchar (255)

[idle]
testsystem varchar (255)

mere er vist ikke nødvendigt for at lave forespørgslen.. og det altså (name = test platform), (name = testsystem)..

hvad sker der egentlig når man komprimerer databasen?? rydder den op eller omorganiserer den data, så søgning er langsommere - eller hurtigere?? nu fylder databasen i hvert fald kun 1,07 MB :P..

sender den om 2 sek, hvis du stadig vil have den..

try showing the some data for each table and also what you expect to see in the result

I think it is a UNION join your after!

SELECT [name] as fld1 FROM singlesystem
UNION ALL
SELECT [test platform] as fld1 FROM testing
UNION ALL
SELECT [testsystem] as fld1 FROM idle
ORDER By fld1

SELECT fld1, fld2 FROM Tbl2

hmm.. det tager lige lidt længere tid, end jeg havde regnet med.. når man komprimerer sletter den også tabeller, der er tomme, og jeg har det liggende på en anden computer.. men det kommer om lidt :P..

when you compress the dB it removes unused data (deleted records etc.) so it should be faster :o)

No it does NOT delete empty tables. You should see NO difference in the objects/data only performance/size

ok.. der forsvandt bare nogle tabeller..

compressng the dB should NOT deleted any tables!

du skulle få databasen nu..

jeg har slettet indholdet, da der var fortrolige informationer i, men du kan vel godt få en idé af, hvad der foregår alligevel..

I havent received it yet. Actually the data is also important so that I can be sure the result contains what you need.

Oops!
Sorry I forgot to say!
Remove NOSPAM from the email!

If you can put some data (fictive) in the tables then it would help

jeg kan ikke sende en zippet version af databasen, fordi hotmail mener, den indeholder virus.. hvis jeg prøver at uploade databasen, mener hotmail ikke, der er virus.. jeg ved ikke, om du vil have, jeg skal sende den??

jeg sidder på en computer, der er styret af en administration, så jeg ved ikke, hvad der kan være på den, og hvor sikret den er, så jeg vil ikke sende noget til dig, hvis du kan få virus af det..

medmindre du synes, jeg skal sende det alligevel.

just send it as it is (.mdb) and with data if possible

stofanet scan mails for virus and I'll also scan it when it arrives so there should be no risk

jeps.. nu skulle du få den..

received

From the test data you have sent me can you give an example of how you want to see the result?

You gave an example previoulsy
tbl1.system (system = "a")
tbl2.system (system = "a")
tbl1.system (system = "b")
tbl1.system (system = "c")
tbl2.system (system = "c")

but this only uses ONE field from each table and if you want to see other fields then you have a problem because the tables do NOT have the same number of fields.

So I need to know which fields from which tables you want to see and HOW you want to see the data!

jeg er kommet til at sige noget vrøvl, fordi det hele kunne laves meget nemmere.. det behøver kun at fungere som 1 tabel.. recordsettet skal være bygget op om testing, som det var i den sql, du lavede til mig i et tidligere spm.. der skal bare smides idle på, hvor idle.systemname = singlesystem.name..

hvis du laver selecten, kan jeg godt definere, hvilke felter, der skal tages med..

eller det jeg egentlig gerne vil have er en sum af alle hours-værdier i idle, der er i et bestemt dato-interval, og de skal lægges ind i recordsne, hvor idle.testsystem = singlesystem.name..

Jeg kører vist lidt rundt i det hele :P..

"Jeg kører vist lidt rundt i det hele" :o)

but this is my problem! I MUST know EXACTLY what you expect in the result before I can put an SQL together. There are SO MANY ways that data can be selected from three tables!

There is NO date/time values in the idle table. And it isnt possible in a simple SQL to add the hours:minutes:seconds
A date/time field can contain dates and times. Summing fields containing times will result in values which are NOT valid time fields
example
ADD 13:00:00 to 12:59:00
25:59:00 which is NOT a valid time!

The SQL for selecting from thre tables using INNER JOIN is

SELECT testing.[Test platform]
FROM (testing INNER JOIN singlesystem ON testing.[Test platform] = singlesystem.name) INNER JOIN idle ON singlesystem.name = idle.testsystem;

hvordan finder man summen af kolonnen idle.hours.. altså ikke antallet af records, men summen af alle værdier af hours...

jeg tror faktisk, jeg er ved at fatte INNER JOINs :P..

You will need to make some code which loops through the records and adds them up for you! But it can NOT return a date/time field!
A date field which is used for storing time values can only contain

23:59:59 after that it starts from 00:00:00 again.

So you have to make your own code to do this

jeg skal have summen af hours og ikke date..

hvor hours er af typen int..

It would be an idea to convert them to seconds, add them all up and then start calculating how amny hours minutes seconds theer are.

hours er integer..

jeg skal have summen af idle.hours i intervallen idle.date < x AND idle.date > y

Jeg kan vel gøre det på denne måde:

SELECT sum_funktion(idle.hours)
FROM (testing INNER JOIN singlesystem ON testing.[Test platform] = singlesystem.name) INNER JOIN idle ON singlesystem.name = idle.testsystem WHERE idle.date < x AND idle.date > y;

hmm.. hedder sumfunktionen bare SUM()?? eller tæller den antal records??

You should be able to use SUM

SELECT testing.[Test platform], Sum(idle.hours) AS SumOfhours, idle.date
FROM (testing INNER JOIN singlesystem ON testing.[Test platform] = singlesystem.name) INNER JOIN idle ON singlesystem.name = idle.testsystem
GROUP BY testing.[Test platform];

If you wan the idle.date to be within an inteval then you can use

>= (greater or equal) and <= (less or equal)
or you can use between

Between x AND y

når man bruger GROUP BY er det så summen af alle hours eller af alle hours, hvor testing.[Test platform] = idle.testsystem??

og kunne jeg i så fald ikke lige så godt bruge GROUP BY idle.testsystem?

GROUP BY will give you sum of hours for each GROUP in this example testing.[Test platform]

In this example you could also have used idle.testsystem but that will NOt always be the case. If you choose other fields then the result may not be as you want.




I am sure that if you tried using Access query builder you will find it VERY helpefull!

jeg kan ikke få dette til at virke:

SELECT testing.[Test platform], Sum(idle.hours) AS SumOfhours, idle.date
FROM (testing INNER JOIN singlesystem ON testing.[Test platform] = singlesystem.name) INNER JOIN idle ON singlesystem.name = idle.testsystem
GROUP BY testing.[Test platform];

"You tried to execute a query tha does not include the specified expression 'date' as a part of an aggregate function."

jeg har fundet noget på msdn.com om GROUP BY:
"When GROUP BY is specified, either each column in any non-aggregate expression in the select list should be included in the GROUP BY list, or the GROUP BY expression must match exactly the select list expression." - http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_9sfo.asp

Betyder det, at jeg skal få alle de felter, jeg vil udskrive, med i min GROUP BY (hvilket vil gøre, at sum(idle.hours) ikke bliver som jeg vil have det??)

Og hvad gør man for at komme ind i access query builder (jeg har en dansk udgave, så jeg ved ikke, hvad jeg kommer ind i - det vil hjælpe lidt, hvis jeg har den engelske forklaring på, hvordan jeg kommer derind)

when you open Access (double click the .mdb file) you should see the database window with a menu down the left side. Here you can choose tables, queries, modules ....

Choose queries. Then press New at the top of the database window. Then choose "Deign view" (the first in list). Then choose the tables you want. Then its just a case of seelcting the fields you want in the query.

Try looking at this link
http://www.hedeskov.dk/netpublikationer/udv-database/indhold.htm

then look at
7. Forespørgsler.

når jeg vil have alle med (*) skriver den, jeg ikke kan, fordi jeg ikke kan regne med totaler for hele tabellen.. hvordan får jeg summen af idle.hours for hvert name i singlesystem og samtidigt henter alle data fra testing?? det må da være muligt..

if you are using GROUP By then you can not use *. Select all of the fields you need NOT *

I think you are going to have to get hold of a book on SQL. It will take FAR TOO LONG to teach you here :o)

Tak for hjælpen, terry.. jeg fik lavet noget af det med access query-manager, eller hvad det nu hedder. jeg stødte ind i nogle problemer, da jeg skulle selecte hours-rangen, men det løste jeg med en Sub-forespørgsel:

SELECT (SELECT Sum(hours) FROM idle WHERE date >= #" & from_date & "# AND date <= #" & to_date & "# AND testsystem = singlesystem.name) AS SumOfhours, singlesystem.location, testing.[Time spend], singlesystem.hours, testing.[Done by], testing.Status, testing.[Test platform], testing.ID FROM singlesystem LEFT JOIN testing ON singlesystem.name = testing.[Test platform] WHERE [testing].[Done by] >= #" & from_date & "# AND [testing].[Done by] <= #" & to_date & "# AND testing.Status = 'DONE' ORDER BY singlesystem.location, singlesystem.name, testing.ID;

Jeg lægger nogle flere points her:
http://eksperten.dk/spm/594326

I think you will find itvery helpfull using the query builder :o)

and thanks a bunch for the points!