Et Program til Avisbundter/Etiketter

Du har stillet spørgsmålet i Access-kategorien. Betyder det, at du arbejder med data som sammenkædet tabeller til Excel-arket?

/Thomas

I så fald skal du lave en forespørgsel, som kunne hedde

SELECT Avisrute.A, Sum(Avisrute.B) AS B
FROM Avisrute
GROUP BY Avisrute.A;

Husk at skifte Avisrute ud med navnet på den sammenkædedet tabel.

Det var førse del...hvis jeg er på rette spor, så vil jeg gå videre med anden del...

/Thomas

Jeg tror du skal starte forfra og lave en tabel med alle ruter og sumaviser.
Ud fra denne tabel kan du så lave dine beregninger med hhv. 100, 75, 50 aviser pr bundt.

Niels

beregninger = forespørgsler.

hi pejsen
You need to first link the Excel table to Access.

This can be done from the File+Get External Data+Link Tables menu.
Choose Microsoft Excel from the Files of Type list. Then find the Excel file you want to use and follow the instructions. Once this is done you should have access to the Excel sheet through Access.

Now you can make a query to see the data as you wish. This is an example you could use.
This query askes you for the maximum number of \"aviser\" which should be in a \"bundt\". It also shows you the number of FULL \"bunter\" and how many are in the last \"bund\"!

What more do you need? Dont tell me, you have never used Access! If thats the case then you can send me the Excel file and let me knoiw which version of Access you are using!

SELECT tblAviser.rutenr, Sum(tblAviser.bundtstr) AS Total, ([Total]-([Total] Mod [Str]))/[Str] AS Full, [Total] Mod [Str] AS rest
FROM tblAviser
GROUP BY tblAviser.rutenr;

To make the labels \"Etikketer\" you create a report using the report wizard for making a label. Just follow ´the instructions, easy!

Hej Terry

Det er rigtigt at jeg er \"Beginner\", men jeg har fået oprettet forespørgelsen.

Og den viser
Med Bundtstr. på 100 stk

Navn    Total    Full    rest
101    130    1    30
102    195    1    95
103    100    1    0
104    37    0    37
105    110    1    10
106    170    1    70

Det jeg ikke kan finde ud af er at få det ud på etiketter
Der kommer til at se forkert ud
det skal se således ud
--------
101
100 stk
--------
  101
30 stk
--------
  102
100 stk
--------
  102
95 stk
--------
  103
100 stk
--------
  104
37 stk
--------
  105
100 stk
--------
  105
10 stk
--------
  106
100 stk
--------
106
70 stk
-------
etc.
-------
etc.
-------
etc.

Hvad gør jeg forkert?

Jeg kører med en Dansk udgave af MS Access 2000

Mvh
Pejsen :-|

hej pejsen> Your doing great!

I dont think it as anything to do with you using Dansk Access. I think you need to choose Sheet Feed instead of continuous under LABEL TYPE. Just play around with it and you will get there, or send me the dB and I will look at it.
dkTeBrSa@europe.lego.com

Undskyld jeg blander mig.

Vil i ikke få et problem hvis der skal udskrives 2 etiketter á 100 stk til samme rute.

Jeg er spændt på om det kan løses uden VBA

Niels

pejsen> dont hit on before, now I understand what your getting at, didnt think that you needed a label per stack. Correct nih!

Its getting late so will take a look tomorrow!

pejsen> I think it sgoing to take a bit of programming, so I think it would be best if you sent me the database. It will be much quicker than me trying to explain what and where to put the code.

dktebrsa@europe.lego.com (the .com) may have been removed from the hyperlink!

For those interested: A form with a single field for entering max bundt størrelse. Button on form to open report(label wizard) which uses a temp table which gets its data from the following.

Private Sub cmdLabel_Click()
Dim db As Database
Dim rst As Recordset
Dim sSQL As String

On Error GoTo Err_cmdLabel_Click

    Dim stDocName As String
    Dim lCnt As Long
   
    sSQL = \"SELECT tblAviser.Navn AS Route, tblAviser.[Postnummer og by], Sum(tblAviser.Antal) AS Total, \"
    sSQL = sSQL & CLng(Me.txtAntal)
    sSQL = sSQL & \" AS MaxBundt, ([Total]-([Total] Mod [MaxBundt]))/[MaxBundt] AS [Full], [Total] Mod [MaxBundt] AS rest\"
    sSQL = sSQL & \" FROM tblAviser\"
    sSQL = sSQL & \" GROUP BY tblAviser.Navn, tblAviser.[Postnummer og by];\"

    Set db = CurrentDb
    Set rst = db.OpenRecordset(sSQL)


    DoCmd.SetWarnings False
   
    DoCmd.RunSQL \"DELETE * FROM tblToLabel\"
   
   
    Do Until rst.EOF
        \'Only if there are papers!
        If rst!full > 0 Or rst!rest > 0 Then
            lCnt = rst!full
           
            Do While lCnt > 0
               
                sSQL = \"INSERT INTO tblToLabel (Route, PostNrBy, Antal) Values (\'\"
                sSQL = sSQL & rst!Route & \"\', \'\" & rst![Postnummer og by] & \"\', \" & rst!MaxBundt & \");\"
           
                DoCmd.RunSQL sSQL
               
                lCnt = lCnt - 1
            Loop
           
            If rst!rest > 0 Then
                sSQL = \"INSERT INTO tblToLabel (Route, PostNrBy, Antal) Values (\'\"
                sSQL = sSQL & rst!Route & \"\', \'\" & rst![Postnummer og by] & \"\', \" & rst!rest & \");\"
           
                DoCmd.RunSQL sSQL
           
            End If
           
        End If

        rst.MoveNext
     
    Loop
   
    DoCmd.SetWarnings True
   
    rst.Close
    Set rst = Nothing
    Set db = Nothing
   
    stDocName = \"rptLabels\"
    DoCmd.OpenReport stDocName, acPreview

Exit_cmdLabel_Click:
    Exit Sub

Err_cmdLabel_Click:
    MsgBox Err.Description
    Resume Exit_cmdLabel_Click
   
End Sub

there are other ways more than likely, but it works!

Hej Terry

Tusinde tak for hjælpen.
Det virker 100 %

Best regards
Pejsen ;-)))))))