Tilføje ekstra entries til en tabel

Not quite sure what your after!

"Lad os tage et eksempel:
Varenummer A har haft 20 dage det seneste år, hvor der har været aktivitet. Det vil sige at der har været 200 dage (hvis vi kun fokuserer på arbejdsdage), hvor der INGEN aktivitet har været. Hvordan får jeg dem tilføjet min tabel?"

What do you want "tilføjet" to your table?

"Hvis jeg "blot" kunne få tilføjet et fiktivt "0" i "Antal varer", ville det gøre det muligt for mig at udregne den korrekte standardafvigelse."

How would that help?

can you give a bit more information and maybe some examples as to what you you expect ?

Du kunne vælge den "lavteknologiske" og køre hele året igennem:
(Ret selv tabel- og feltnavne)

Sub OpretDummyDage()
  Const Varenummer = "ABC123"
  Const StartDato = #1/1/2016#
  Const SlutDato = #12/31/2016#
  Const TabelNavn = "DinTabel"
  Dim d As Date
 
  DoCmd.SetWarnings False
  For d = StartDato To SlutDato
    If IsNull(DLookup("Dato", TabelNavn, "Dato=#" & Format(d, "yyyy/mm/dd") & "#")) Then
      DoCmd.RunSQL ("INSERT INTO " & TabelNavn & "(Varenummer, Dato, AntalVarer) SELECT '" & Varenummer & "',#" & Format(d, "yyyy/mm/dd") & "#, 0")
    End If
  Next d
  DoCmd.SetWarnings True
End Sub

@Terry;
Ja, det kan godt være at jeg ikke har gjort mig helt forståelig :)

Jeg vil gerne udregne standardafvigelsen på vareforbruget på flere varer i løbet af et år. For at kunne bruge funktionen "Stdafv" (standard deviation) i en query i Access, skal jeg have oprettet alle observationer i datatabellen for at udregningen bliver rigtig. Min udfordring er at når jeg trækker vareforbruget på en vare i vores ERP system, trækker jeg kun de datoer ud, det seneste år, som har haft transaktioner. De resterende datoer, det seneste år, skal jeg selv have skabt i min datatabel med vareforbrug = 0.
Da det handler om mange varenumre skal det helst kunne gøres automatisk via a funktion i Access.

Jeg har dog ikke selv kunnet finde en metode.

@fdata;
Jeg vil meget gerne afprøve din metode! Når jeg har indsat modulet, hvad skal jeg derefter gøre for at få koblet min nuværende datatabel sammen og bede den udfylde alle blanke felter med "0"?
Plus, det store spørgsmål: Hvad sker der når jeg har mange varenumre der skal igennem sammen kørsel/databehandling? De vil ikke påvirke hinandens "kalendre"?
Det jeg frygter er at hvis vare A har haft vareforbrug 1/1 og vare B har haft vareforbrug 2/1, så vil der ikke blive indsat et vareforbrug for vare A d. 2/1 = 0, men den vil overtage vare B's forbrug den dag.
Håber det giver mening, ellers prøver jeg gerne igen med en alternativ forklaring :)

Just reading through your last comment and answering at the same time, so maybe a load of rubbish .... :-)
Its ages since I last used stddev so not sure how it works, so idea maybe needs adjusting on th eway.

assuming that for each varenummer/dato there are a number of transactions, other wise if there is only one record per vaenummer/dato then you wont need this.
1: import the ERP data into a table.
2:make a query where you group on varenummer and dato and sum on transactions

Assuming that you have a table containing ALL varenummer.

Make a query (outer join) where you join on the table containing all varenummer and the table or query you just made above.
You need to alter the joins between the tables so that you include all records from the table containing all varenummer and only those from the other table/query you just made above.

Now you need to add the fields you have need, for example varenummer, dato and transactions.
Now I think it will be necessary to save this as a query and then use this query in a new aggregate query where you can use stddev.

Try this and lets see what you get out of it so far ....

Hvis du skal oprette de manglende datoer for alle dine varer, kan du pakke min forrige kode ind et gennemløb af alle dine varer (som du formodentlig har i en varetabel).

Indsæt nedenstående kode i et modul (Alt-F11) og udskift tabel- og feltnavne med dine egne. Vær specielt opmærksom på at få udskiftet alle forekomster af feltet "Varenummer" med dit eget feltnavn.

Stil markøren i den første rutine og tast F5. Så oprettes alle datoerne med 0 i antalfeltet.

Kode:
---------------------------------------------
Const TabelNavn = "DinTabel"
Const Varetabel = "Varenumre"
Const StartDato = #1/1/2016#
Const SlutDato = #1/15/2016#

Sub OpretDummyDage_AlleVarer()
  Dim d As Date
  Dim Db As Database
  Dim DummyRst As Recordset
   
  Set Db = CurrentDb
  Set DummyRst = Db.OpenRecordset(Varetabel, dbOpenSnapshot)
  With DummyRst
    If Not .EOF Then
      Do
        OpretDummyDage (!Varenummer)
        .MoveNext
      Loop Until .EOF
    End If
    .Close
  End With
  Set DummyRst = Nothing
  Set Db = Nothing
End Sub

Private Sub OpretDummyDage(Varenummer As String)
  DoCmd.SetWarnings False
  For d = StartDato To SlutDato
    If IsNull(DLookup("Dato", TabelNavn, "Dato=#" & Format(d, "yyyy/mm/dd") & "# AND Varenummer='" & Varenummer & "'")) Then
      DoCmd.RunSQL ("INSERT INTO " & TabelNavn & "(Varenummer, Dato, AntalVarer) SELECT '" & Varenummer & "',#" & Format(d, "yyyy/mm/dd") & "#, 0")
    End If
  Next d
  DoCmd.SetWarnings True
End Sub

Ah, now I'm waking up :-)
So the Access table in original question doesnt exist?

Layoutet er som følger:
1. Varenummer
2. Dato
3. Antal varer

This got me thinking it did
Hvis jeg "blot" kunne få tilføjet et fiktivt "0" i "Antal varer ...

actually you only need a table containing containing all varenummer (once) and another table containing all dates (once)

Then make a query without joins
SELECT tblVare.Varenummer, tblDates.Dato
FROM tblVare, tblDates

This will give you a list of all Varenummer for every dato


Saves having a table with all varenummer/dato

But you still need to use some code to get the dates in the table, unless you do it manually :-)

@fdata:
Jeg har nu ændret dit script til følgende:
Const TabelNavn = "tbl_VareforbrugStep1"
Const Varetabel = "Varenummer"
Const StartDato = #1/1/2016#
Const SlutDato = #1/15/2016#

Sub OpretDummyDage_AlleVarer()
  Dim d As Date
  Dim Db As Database
  Dim DummyRst As Recordset
   
  Set Db = CurrentDb
  Set DummyRst = Db.OpenRecordset("tbl_VareforbrugStep1", dbOpenSnapshot)
  With DummyRst
    If Not .EOF Then
      Do
        OpretDummyDage (!Varenummer)
        .MoveNext
      Loop Until .EOF
    End If
    .Close
  End With
  Set DummyRst = Nothing
  Set Db = Nothing
End Sub

Private Sub OpretDummyDage(Varenummer As String)
  DoCmd.SetWarnings False
  For d = StartDato To SlutDato
    If IsNull(DLookup("[Fysisk dato]", "tbl_VareforbrugStep1", "[Fysisk dato]=#" & Format(d, "dd/mm/yyyy") & "# AND Varenummer='" & Varenummer & "'")) Then
      DoCmd.RunSQL ("INSERT INTO" & tbl_VareforbrugStep1 & "(Varenummer, [Fysisk dato], Vareforbrug) SELECT '" & Varenummer & "',#" & Format(d, "dd/mm/yyyy") & "#, 0")
    End If
  Next d
  DoCmd.SetWarnings True
End Sub


Jeg får dog en fejl i 5. sidste sætning: "DoCmd.RunSQL ("INSERT INTO" & tbl_VareforbrugStep1 & "(Varenummer, [Fysisk dato], Vareforbrug) SELECT '" & Varenummer & "',#" & Format(d, "dd/mm/yyyy") & "#, 0")".

Den siger at der er en syntaksfejl i INSERT INTO-sætningen...?

Til info: Tabellen den skal kobles med hedder tbl_VareforbrugStep1 og Varenummer-feltet hedder "Varenummer"
- dato-feltet hedder "Fysisk dato".
Håber du kan hjælpe mig det sidste stykke :)

Der mangler bare et mellemrum efter INTO

Næ, hov.
Det hele skal rettes til:
DoCmd.RunSQL ("INSERT INTO tbl_VareforbrugStep1 (Varenummer, [Fysisk dato], Vareforbrug) SELECT '" & Varenummer & "',#" & Format(d, "dd/mm/yyyy") & "#, 0")"

Den kører fint igennem nu - uden fejl.
Nu er jeg ikke helt sikker på om jeg gør det rigtige, men jeg har efterfølgende vendt tilbage til min database, hvor jeg nu kan se at der er oprettet en ny forespørgsel. Men når jeg kører denne får jeg ikke flere entries frem end jeg havde før - altså den laver ikke disse nul-entries som jeg har brug for. Samtidig viser den kun to felter: Varenummer og fysisk dato - ikke feltet: Vareforbrug...?

En sidste ting. Hvis jeg nu skal have start og slutdato til at være dynamiske:
Startdato=Date()-365
Slutdato=Date()
Kan jeg så blot indsætte dem i scriptet i stedet for de værdier du har lavet?

På forhånd mange tak!!

PS. Jeg fremsender gerne lidt data, hvis det kan hjælpe?

I'd like to see the dB if possible
With original table, some ERP data and VBA code your using.
ekspertenAtsanthell.dk
AT = @

Allerførst: Med hensyn til datoerne kan du droppe de to Const erklæringer og i stedet bruge dine datoer direkte i funktionen:
For d = Date()-365 To Date()

Mht. din kommentar "hvor jeg nu kan se at der er oprettet en ny forespørgsel":
Koden opretter ingen forespørgsel, så den må der være en anden, der har oprettet.

Hvis der er felter, du savner, når du åbner forespørgslen, kan du åbne den i designvisning og tilføje dem.

If you have a table containing varenummer then you only need a table with dates

Then you make a query containing both of these table to get a result with each varenummer and all dates.

Once you have this you can join (outer join) this query with your ERP data
The rows which have no related ERP data will be shown as null which can be changed to 0

Hej igen
Jeg tror den bad mig gemme scriptet som en forespørgsel da jeg ville gemme modulet.

Det er ikke hver dag jeg leger med scripts. Hvis jeg nu gerne vil bruge funktionen i en forespørgsel, hvordan gør jeg så det? Altså hvor den får oprettet en tabel med både vareforbruget og mine nul-entries tilføjet?

Scripts kan ikke gemmes eller bruges direkte i en forespørgsel. Jeg er lidt i tvivl om, hvad det er du forsøger at beskrive.

Dit sidste spørgsmål giver ikke den store mening, sorry.

"... får oprettet en tabel med både vareforbruget og mine nul-entries tilføjet?"
Hvad er det helt præcis, du er ude efter?
Dine oprindelige poster (med AntalVarer) og de nye poster (med nul-værdier) ligger vel alle i tabellen VareforbrugStep1 ?!?

Jeg har brugt script i en forespørgsel før. Her fik jeg hjælp til et script som lavede en funktion jeg kunne bruge i en forespørgsel. Den oprettede en funktion der gik ind og fandt alle nummeriske værdier i et felt i en tabel:
Kode:
Public Function FindTal(Ind As String) As Integer
Dim I As Integer
For I = 1 To Len(Ind)

If IsNumeric(Mid(Ind, I, 1)) Then
FindTal = FindTal & Mid(Ind, I, 1)
End If
Next
If IsEmpty(FindTal) Then FindTal = 0

End Function

Dette brugte jeg i en query med følgende udtryk:
Nettodage: FindTal([Betalingsbetingelser])

Scriptet skal helst indgå i en fast kørsel hvor jeg får hentet vareforbrugsdata ind i access. Her klargør jeg data, som ender ud i en tabel, nemlig tbl_VareforbrugStep1.
Denne tabel indeholder
Varenummer; Fysisk dato; Vareforbrug
115; 01-02-2016; 2
115; 03-03-2016; 1
115; 05-06-2016; 5
20136; 06-02-2016; 10
20136; 03-04-2016; 100
Etc

Så håber jeg at din funktion kunne gå ind og indsætte nul-forbruget i en ny tabel á la:
Varenummer; Fysisk dato; Vareforbrug
115: 07-11-2015;
115: 08-d11-2015; 0
115: 09-11-2015; 0
115: 10-11-2015; 0
Fortsæt indtil:
115; 01-02-2016; 2
115: 02-02-2016; 0
115: 03-02-2016; 0
115: 04-02-2016; 0
Fortsæt indtil:
115; 03-03-2016; 1
115; 05-06-2016; 5
115; 06-06-2016; 0
Fortsæt indtil dags dato

Giver det mening?

Ja; men vi taler lidt forbi hinanden :O(
Altså:

Step 1:
"Her klargør jeg data, som ender ud i en tabel, nemlig tbl_VareforbrugStep1."
Tabellen indeholder nu de dage, hvor der har været aktivitet. OK?

Step 2:
Kør OpretDummyDage_AlleVarer
Herved TILFØJES "nul-dagene" til tbl_VareforbrugStep1
(Der oprettes ikke nogen ny tabel)

Du siger: "Scriptet skal helst indgå i en fast kørsel hvor jeg får hentet vareforbrugsdata ind i access". Du skal altså med andre ord sørge for at køre OpretDummyDage_AlleVarer i forbindelse med at du klargør dine data (Step 1 ovenfor). Hvis du kører klargøringen fra en formular, kan du jo oprette en knap, der kalder rutinen.

PS:
Og så er er i øvrigt forskel på en funktion (FindTal), som man kalder med en værdi og som returnerer et resultat og en rutine (OpretDummyDage_AlleVarer), som udfører en handling (opretter poster). Det vil ikke give nogen mening at kalde sidstnævnte fra en forespørgsel.

Ahh, okay, så forstår jeg.
Godt nu har jeg klargjort tabellen og efterfølgende kørt din rutine.

Selvom jeg kun har bedt om seneste 12 mdr får jeg følgende værdier som ikke burde komme med:
Varenr; Fysisk dato; Vareforbrug
115    12-01-2015    0
115    12-02-2015    0
115    12-03-2015    0
115    12-04-2015    0
115    12-05-2015    0
115    12-06-2015    0
115    12-07-2015    0
115    12-08-2015    0
115    12-09-2015    0
115    11-10-2015    0
115    12-10-2015    0

Jeg vedhæfter lige scriptet som det ser ud nu. Kan du se hvad der går galt?

Option Compare Database

Const TabelNavn = "tbl_VareforbrugStep1"
Const Varetabel = "Varenummer"

Sub OpretDummyDage_AlleVarer()
  Dim d As Date
  Dim Db As Database
  Dim DummyRst As Recordset
   
  Set Db = CurrentDb
  Set DummyRst = Db.OpenRecordset("tbl_VareforbrugStep1", dbOpenSnapshot)
  With DummyRst
    If Not .EOF Then
      Do
        OpretDummyDage (!Varenummer)
        .MoveNext
      Loop Until .EOF
    End If
    .Close
  End With
  Set DummyRst = Nothing
  Set Db = Nothing
End Sub

Private Sub OpretDummyDage(Varenummer As String)
  DoCmd.SetWarnings False
  For d = Date - 365 To Date
    If IsNull(DLookup("[Fysisk dato]", "tbl_VareforbrugStep1", "[Fysisk dato]=#" & Format(d, "dd-mm-yyyy") & "# AND Varenummer='" & Varenummer & "'")) Then
      DoCmd.RunSQL ("INSERT INTO tbl_VareforbrugStep1 (Varenummer, [Fysisk dato], Vareforbrug) SELECT '" & Varenummer & "',#" & Format(d, "dd-mm-yyyy") & "#, 0")
    End If
  Next d
  DoCmd.SetWarnings True
End Sub

Håber du kan hjælpe mig det sidste stykke!

Den får også nogle entries med i den anden ende som ligger efter dags dato:

115    10-11-2016    0
115    01-12-2016    0
115    02-12-2016    0
115    03-12-2016    0
115    04-12-2016    0
115    05-12-2016    0
115    06-12-2016    0
115    07-12-2016    0
115    08-12-2016    0
115    09-12-2016    0
115    10-12-2016    0

Og så mangler der også nogle datoer ind i mellem. Jeg fremsender gerne outputtet til dig hvis det kan hjælpe?

Prøv at rette datoformatet fra "dd-mm-yyyy" til "yyyy/mm/dd"

Nu virker det! Fantastisk :) Tusind tak!

Jeg har et par sidste spørgsmål i forbindelse med kørslen af rutinen.
Jeg har en række forespørgsler som jeg vil tilføje til en makro som derefter sørger for at køre dem i en bestemt rækkefølge.
Jeg har ikke lavet formularer før, så jeg ved ikke hvordan det ville virke med en knap (som du foreslog), hvis den evt. skal tilføjes min makro?

Så mine spørgsmål går egentlig på.
1. Kan jeg tilføje en formular til en makro og trigge knappen til rutinen den vej igennem?
2. Hvis ja, hvordan laver jeg så knappen så den kan kalde rutinen? Jeg har været inde i formulardesignet og danne knappen og så beder den mig tage stilling til hvilken handling den skal foretage sig. Der bliver jeg usikker på hvad jeg skal vælge?

PFT!

Hi hi ;O)
Nu vender du det hele på hovedet.

Hvis du allerede har en makro, tilføjer du bare kaldet af den nye rutine til makroen. Forslaget med en knap er helt irrelevant, når du bruger en makro.

Gør følgende:
- Gå in i VBA editoren (Alt-F11) og ret Sub OpretDummyDage_AlleVarer() til Function OpretDummyDage_AlleVarer()
  (Altså: udskift Sub med Function)
- Gem og luk

- Åbn så din makro i Designvisning
- Find det sted, hvor du vil indsætte kaldet
- Tilføj en handling med værdien "Afspil kode"
- Ud for Funktionsnavn skriver du: OpretDummyDage_AlleVarer()
- Gem og luk

Så enkelt kan det gøres. Ingen omveje omkring formularer og knapper. Vi ordner det direkte i makroen  ;O)

Jamen jeg er uden ord!
Hvor er det fedt!!  Tusind tak for hjælpen! Jeg er virkelig imponeret og taknemmelig for at I vil bruge tiden på at hjælpe sådan én som mig.

Endnu engang tak! :)

Jamen, velbekomme.
Det er jo præcis derfor Eksperten er så genial.
VI har allesammen mulighed for at få hjælp fra nogen, der ved lidt mere end os selv.