Als u ooit een lijst met veld-/querywaarden, gescheiden door scheidingstekens, samen met andere gegevens uit uw database moest genereren, weet u dat dit op zijn zachtst gezegd een uitdaging kan zijn – en een taak waarvan de meeste mensen denken dat die onmogelijk is. Stel dat u bijvoorbeeld gevraagd wordt een rapport te maken van de 5 best presterende verkopers – in aflopende volgorde – per maand, of de 3 dichtstbijzijnde winkels per klant, dan bent u mogelijk aangewezen op kruistabelquery's of het handmatig combineren van de resultaten buiten Access. Maar met behulp van een beetje VBA-code kunt u binnen enkele minuten resultaten zoals de volgende genereren…
| jan | John, Sally, Bill |
| Feb | Sally, Adam, John |
| Mar | Adam, Sally, Bill |
Er zijn vaak momenten waarop het nuttig zou zijn om een afgebakende lijst met resultaten weer te geven als onderdeel van een grotere zoekopdracht - een lijst met de top 3 van grootste klanten voor elke verkoper, de 5 winkels die het dichtst bij elk van uw best verkopende klanten liggen , de lijst gaat verder.
Stop met het samenvoegen van uw rapport
Hoewel het meer dan mogelijk is om de details te krijgen om dat soort rapporten te maken, zou u de resultaten meestal handmatig moeten samenvoegen buiten Access als onderdeel van een spreadsheet of rapport.
De volgende code elimineert de noodzaak om met de queryresultaten buiten Access te rommelen, en stelt u in plaats daarvan in staat om met gemak het soort resultaten te produceren dat hierboven wordt weergegeven.
Laten we eerst beschrijven wat we willen dat de code kan doen:
"Geef bij een zoekopdracht een x aantal resultaten op, gecombineerd tot een enkele tekenreeks die wordt gescheiden door y"
Voordat we beginnen
Voordat we naar de code kijken, is het belangrijk op te merken dat, hoewel het mogelijk is om een VBA-functie te maken die algemener is en elke querycombinatie kan verwerken, dit zou resulteren in behoorlijk lange code, dus wat we in dit artikel zullen doen is om een voorbeeldgeval te definiëren en de functie te schrijven om dat geval af te handelen. Als u dit op deze manier doet, wordt het veel gemakkelijker voor u om de code opnieuw te maken om aan uw specifieke behoeften te voldoen.
De voorbeeldcase
We hebben een verkoopdatabase waarin de details van de aankopen van klanten worden opgeslagen, samen met de categorie van het product waartoe de aankoop behoort. We willen een rapport maken dat per maand de top 3 categorieën in aflopende volgorde laat zien.
Voor de eenvoud houden we het voorbeeld bij een enkele tabel, hoewel het principe werkt, ongeacht uw instellingen. De tafel die we gaan gebruiken is als volgt opgebouwd:
| Sales |
| Klantnaam |
| Transactie datum |
| Transactiewaarde |
| Categorie |
Dit is duidelijk een te vereenvoudigde opzet, maar je snapt het idee.
Nu - de code
Public Function CategoryList(Month As String, NumResults As Integer, SortAscending As Boolean, Delimiter As String) As String
Dim sSql, resultString As String
Dim rst As Recordset
Dim firstLine As Boolean
'Create our SQL string using the supplied parameters
sSql = "SELECT TOP " & NumResults & " [category] FROM sales GROUP BY Format([TransactionDate],""mmm""), sales.Category HAVING (((Format([TransactionDate], ""mmm"")) = """ & Month & """))"
If SortAscending Then sSql = sSql & " ORDER BY Sum(sales.TransactionValue) DESC;"
Set rst = CurrentDb.OpenRecordset(sSql)
firstLine = True
'Loop through the results, and create the string to return
With rst
Do While Not .EOF
If Not firstLine Then
resultString = resultString & Delimiter & .Fields("category")
Else
resultString = resultString & .Fields("category")
firstLine = False
End If
.MoveNext
Loop
End With
Set rs = Nothing
CategoryList = resultString
End Function
De code - uitgelegd
Zoals je ziet, is de code vrij vanzelfsprekend. We beginnen met het gebruiken van de meegeleverde parameters om onze query samen te stellen. Zodra de query-string is aangemaakt, voeren we deze uit en doorlopen we de resultaten, waarbij we ze samenvoegen tot de ene resultaatstring waarnaar we op zoek zijn.
U zult merken dat we controleren of we een scheidingsteken aan de tekenreeks moeten toevoegen of niet - zonder deze controle zou u ofwel een slecht opgemaakte resultaatstring krijgen, of zou u code moeten toevoegen om ongewenste scheidingstekens te verwijderen - op deze manier blijft het vanaf het begin netjes en opgeruimd.
De functie zelf kan natuurlijk worden aangeroepen vanuit een query, een rapport of waar u maar wilt vanuit Access.
Herstel beschadigde toegangsdatabase
Als je tegenkomt beschadigde Access-databases Wanneer u de bovenstaande code uitvoert, is het beter om een gespecialiseerd hulpmiddel te gebruiken om ze te repareren en alle tabellen uit de corrupte databases te herstellen.
Auteur Introductie:
Mitchell Pond is een expert op het gebied van gegevensherstel in DataNumen, Inc., de wereldleider in technologieën voor gegevensherstel, waaronder reparatie SQL-probleem en Excel-herstelsoftwareproducten. Voor meer informatie bezoek www.datanumen.com