[Access] Query met ordertotalen

[Yagermeister]

Ik ben op dit moment aan het proberen om een query te maken die me de orders van 1 tabel samenvoegt met een producttabel waar ik het totaal per order wil hebben. Dit wil ik dan in een complete orderlijst laten zien om wat extra informatie te kunnen toevoegen/bewerken.

De verkoop tabel is als volgt opgebouwd:

code:

tbl_Verkoop
tbl_Verkoop.so_id (autonummering), tbl_Verkoop.so_number, tbl_Verkoop.so_invoice_date, tbl_Verkoop.so_customer, tbl_Verkoop.so_due_date, tbl_Verkoop.so_amount_recieved, tbl_Verkoop.so_date_recieved, tbl_Verkoop.so_comment_line,

De tabel waar de producten in staan is als volgt opgebouwd (niet relevante kolommen zijn weggelaten):

code:

tbl_Verkoop_product
tbl_Verkoop_product.id (autonummering), tbl_Verkoop_product.so_id, tbl_Verkoop_product.so_part_number, tbl_Verkoop_product.so_qty_shipped, tbl_Verkoop_product.so_sales_price

Zoals eerder gezegd wil ik de ordertotalen hebben in 1 query gesorteerd op so_number. Bij de verkoop tabel heb ik het id als autonummering gebruikt waarna ik ditzelfde nummer gebruik om de producten aan te geven per order.

Na wat zoeken ben ik bij onderstaande query uitgekomen echter moet ik daar een so_number ingeven en dan telt die me alles orders bij elkaar op.

code:

SELECT tbl_Verkoop.so_id, tbl_Verkoop.so_number, tbl_Verkoop.so_invoice_date, tbl_Verkoop.so_customer, tbl_Verkoop.so_due_date, tbl_Verkoop.so_amount_recieved, tbl_Verkoop.so_date_recieved, tbl_Verkoop.so_comment_line, (SELECT Sum(tbl_Verkoop_product.so_qty_shipped * tbl_Verkoop_product.so_sales_price) FROM tbl_Verkoop_product where tbl_Verkoop_product.so_number = so_number)
FROM tbl_Verkoop;

Weet iemand misschien een oplossing hiervoor zodat ik een overzicht krijg van de totalen? Deze wil ik namelijk in een overzicht laten zien zodat we kunnen aangeven wat er betaalt is etc.

[Lustucru]

where tbl_Verkoop_product.so_number = so_number

Volgens jezelf komt in verkoop_product geen veld so_number voor? En als dat wel het geval zou zijn dan zal Access piepen dat het niet duidelijk is uit welke tabel de tweede so_number (zonder tableaanduiding) gehaald moet worden. Het zal wel zoiets moeten worden:

code:

select t.id, t.nogWat, select sum(t2.bedrag) from t2 where t2.foreignKey=t.id
from t
where t.teFilterenVeld=[GeefParameterOp]

Je kutn het jezelf makkelijk maken door eerst een query te maken voor de tellingen en dan een volgende query die je eerste query joint met je verkopen. Ook makkelijker troubleshooten.

[ Voor 39% gewijzigd door Lustucru op 18-05-2014 10:31 ]

[Yagermeister]

Lustucru schreef op zondag 18 mei 2014 @ 10:25:
[...]


Volgens jezelf komt in verkoop_product geen veld so_number voor? En als dat wel het geval zou zijn dan zal Access piepen dat het niet duidelijk is uit welke tabel de tweede so_number (zonder tableaanduiding) gehaald moet worden.

Ik stond net op het punt om die fout eruit te halen. Per toeval viel me dit op vanmorgen.

De correcte code is als volgt:

code:

SELECT tbl_Verkoop.so_id, tbl_Verkoop.so_number, tbl_Verkoop.so_invoice_date, tbl_Verkoop.so_customer,
  (SELECT Sum(tbl_Verkoop_product.so_qty_shipped * tbl_Verkoop_product.so_sales_price) FROM tbl_Verkoop_product where tbl_Verkoop_product.so_id = so_id) AS Total
FROM tbl_Verkoop;

Echter is dit nog steeds niet werkend. Nu telt die me namelijk alle producten op en zet dat bij elk order neer. Het enigste wat ik nu dus mis is het tellen per order ipv totaal van alles.


Zojuist heb ik de oplossing gevonden.

code:

SELECT tbl_Verkoop.so_id, tbl_Verkoop.so_number, tbl_Verkoop.so_invoice_date, tbl_Verkoop.so_customer,
  (SELECT Sum(tbl_Verkoop_product.so_qty_shipped * tbl_Verkoop_product.so_sales_price) FROM tbl_Verkoop_product where tbl_Verkoop_product.so_id = tbl_Verkoop.so_id) AS Total
FROM tbl_Verkoop;

Nu telt die wel per order. Blijkbaar was ik ook daar vergeten de tabel aan te geven. Mijn dank voor het meedenken.

Ik kan nu wel nog niet de query bewerken wat wel nodig is. Is daar een aparte query voor nodig? Ik heb namelijk ook nog een aantal velden die ik in bovenstaande query heb weggelaten zoals bijv datums van betaling en hoeveel al betaald is.

[ Voor 27% gewijzigd door Yagermeister op 18-05-2014 10:35 . Reden: Oplossing gevonden ]

[Lustucru]

Nogal wiedes, want voor elk record in de subquery is je je wehre voorwaarde waarde. Verwijs eens expliciet waar je in tbl_Verkoop_product.so_id = so_id so_id vandaan haalt.

edit:
Ah, je hebt het zelf ook gevonden

[ Voor 11% gewijzigd door Lustucru op 18-05-2014 10:36 ]

[Yagermeister]

Gevonden ja maar de data is niet bewerkbaar. Dit is echter wel nodig dus ik moet even zoeken hoe ik dit voor elkaar krijg.

Eindelijk de oplossing gevonden. Per toeval kwam ik op de Dsum functie uit. Met wat pielen is het gelukt om alles te laten zien en ook de data bewerkbaar te houden.

Voor de compleetheid is hieronder de volledige code.

code:

SELECT tbl_Verkoop.so_id, tbl_Verkoop.so_number, tbl_Verkoop.so_invoice_date, tbl_Verkoop.so_customer, tbl_Verkoop.so_due_date, tbl_Verkoop.so_amount_recieved, tbl_Verkoop.so_date_recieved, tbl_Verkoop.so_comment_line, Dsum("tbl_Verkoop_product.so_qty_shipped * tbl_Verkoop_product.so_sales_price","tbl_Verkoop_product","tbl_Verkoop_product.so_id = " & tbl_Verkoop.so_id) AS Total
FROM tbl_Verkoop;

[ Voor 72% gewijzigd door Yagermeister op 18-05-2014 10:48 ]

[Yagermeister]

Ik ga deze dan toch een schop verkopen aangezien ik nog een vraag heb over dezelfde query.

Zoals hierboven al aangegeven werkt bovenstaande query perfect. Het enigste wat ik nu niet voor elkaar krijg is de due_date. Deze wil ik berekenen aan de hand van de invoice date + payment terms (staat in andere db). Het veld payment terms is gewoon een getal wat ik dan bij de date kan optellen.

Ik heb geprobeerd om dit via een dlookup te doen welke de payment terms ophaalt uit de andere db en deze dan optelt echter krijg ik dan een so_id error.

De dlookup wat ik gebruikt heb is als volgt:

code:

DLookup("[customers_payment_terms]" , "[tbl_Customers]" , "[customers_invoice_company] = " & [so_customer]) AS Due_Date

Als ik dit veld aan de query zelf toevoeg krijg ik een inner join maar dan is de data niet meer bewerkbaar. Is er een makkelijke manier om de dlookup (of iets anders) om die payment terms uit de andere tabel te halen zodat ik die kan gebruiken?

[Yagermeister]

Toch maar eens bumpen in de hoop dat iemand een oplossing heeft.

[Lustucru]

Dat veld [customers_invoice_company] is dat numeriek of tekst>?
Geef eens de hele query en de volledige foutmelding?

[Yagermeister]

De hele query is als volgt:

code:

SELECT tbl_Verkoop.so_id, tbl_Verkoop.so_number, tbl_Verkoop.so_sales_invoice, tbl_Verkoop.so_invoice_date, tbl_Verkoop.so_customer, dlookup("customers_payment_terms" , "tbl_Customers" , "customers_invoice_company = " & so_customer) AS Due_date, tbl_Verkoop.so_amount_recieved, tbl_Verkoop.so_date_recieved, tbl_Verkoop.so_comment_line, DSum("tbl_Verkoop_product.so_qty_shipped * tbl_Verkoop_product.so_sales_price","tbl_Verkoop_product","tbl_Verkoop_product.so_id = " & tbl_Verkoop.so_id) AS Invoice_Total
FROM tbl_Verkoop, tbl_Customers;

Het veld company is een tekst veld en het veld wat opgehaald moet worden is een getallenveld. De fout wat ik krijg is #fout als ik bovenstaande code probeer en de query blijft constant doordraaien.

Als ik de (onderstaande) dlookup in een leeg tekstvak zet met onderstaande code krijg ik: de syntaxis van de expressie die u hebt opgegeven is ongeldig.

code:

=DLookup("customers_payment_terms", "tbl_Customer", "customers_invoice_company = " & [so_customer])

[Lustucru]

Yagermeister schreef op vrijdag 23 mei 2014 @ 16:01:
Als ik de (onderstaande) dlookup in een leeg tekstvak zet met onderstaande code krijg ik: de syntaxis van de expressie die u hebt opgegeven is ongeldig.

Omdat je strings tussen quotes moet plaatsen:

Visual Basic:

stringCriterium = "[foobar]='" & [veld] & "'"  'syntax voor string vergelijkingen
vs
stringCriterium ="[foobar]=" & [veld] 'syntax voor numerieke vergelijkingen

[Yagermeister]

Lustucru schreef op vrijdag 23 mei 2014 @ 18:49:
[...]

Omdat je strings tussen quotes moet plaatsen:

Visual Basic:

stringCriterium = "[foobar]='" & [veld] & "'"  'syntax voor string vergelijkingen
vs
stringCriterium ="[foobar]=" & [veld] 'syntax voor numerieke vergelijkingen

Ik zie inderdaad wat je bedoelt. Blijkbaar heb ik het tekst gedeelte gelezen van dlookup.

Echter als ik de tekst aanpas naar onderstaande blijf ik dezelfde melding krijgen.

code:

=DLookup("[customers_payment_terms]", "[tbl_Customer]", "[customers_invoice_company] ='" & [so_customer] & "'")

Kan het zijn doordat payment_terms wel een numerieke waarde is terwijl de invoice_company + so_customer tekst waardes zijn?

[Lustucru]

En die DSum doet het wel goed? Je bent wat onduidelijk met je foutmeldingen en je queryteksten.
Anders moet je maar eens kijken of je lijstscheidingsteken "," of ";" is.

Dit is trouwens ook wel typisch:

code:

FROM tbl_Verkoop, tbl_Customers;

[Yagermeister]

Lustucru schreef op zaterdag 24 mei 2014 @ 00:36:
En die DSum doet het wel goed? Je bent wat onduidelijk met je foutmeldingen en je queryteksten.
Anders moet je maar eens kijken of je lijstscheidingsteken "," of ";" is.

Dit is trouwens ook wel typisch:

code:

FROM tbl_Verkoop, tbl_Customers;

De Dsum werkt wel prima. Je hebt gelijk met de query tekst. Ik heb daar per ongeluk de verkeerde query geplakt.

code:

SELECT tbl_Verkoop.so_id, tbl_Verkoop.so_number, tbl_Verkoop.so_sales_invoice, tbl_Verkoop.so_invoice_date, tbl_Verkoop.so_customer, dlookup("customers_payment_terms" , "tbl_Customers" , "customers_invoice_company = " & so_customer) AS Due_date, tbl_Verkoop.so_amount_recieved, tbl_Verkoop.so_date_recieved, tbl_Verkoop.so_comment_line, DSum("tbl_Verkoop_product.so_qty_shipped * tbl_Verkoop_product.so_sales_price","tbl_Verkoop_product","tbl_Verkoop_product.so_id = " & tbl_Verkoop.so_id) AS Invoice_Total
FROM tbl_Verkoop;

Dit hierboven is wel de versie die ik getest heb. Die andere was toen ik een inner join had geprobeerd wat wel werkte maar niet editbaar was.

De scheidingstekens zijn overal , btw