Working with Subqueries — DataWright Information Services
Using Subqueries — an introduction Download (207,489 bytes) Returning grouped records in a single query Sometimes you find that you can’t get just the information you want using just a Totals query. For example, in a table with Manufacturer, Month, Sales and Year, you may want to view the highest sales by month and year, […]
Introduction to Range Names — DataWright Information Services
Introduction to range names What are range names? Range names are markers A range name is a reference to a cell or group of cells in a workbook. By default range names are absolute references. You can use range names in a whole lot of ways. Here are just a few: Identify a value that […]
Using Dates with SQL — DataWright Information Services
Using SQL with dates If your regional settings are not the same as the US settings for dates, you will most likely experience problems when you build SQL expressions in VBA that use dates. This is because SQL requires US date formats. The normal query builder does not have this problem; evidently, it can use […]
Creating Union Queries — DataWright Information Services
Creating Union queries Download What is a Union Query? A Union query is used to combine records from different tables or queries that have the same structure. The result is a non-updatable query that combines all the records from the different data sources. The fields don’t have to have identical names in the queries that […]
DataWright Information Services
Data Types in Jet, DDL and ADOX The following table shows the data types that can be created through the Access Jet interface, and their equivalents in DDL (SQL) and ADOX. Access Interface DDL (Queries) ADOX constant Text Text (size) adVarWChar Memo Memo adLongVarWChar Number — Byte Byte adUnsignedTinyInt Number — Integer Short adSmallInt Number […]
Excel pivot tables — changing a filter on an external data source — DataWright Information Services
Changing an external data source for a pivot table A while ago I was asked to build a reporting tool that consisted of twenty or so pivot tables based on an external data source. The data in the original query was filtered by State, so that users could only see data that related to them. […]
Using VBA to simplify maintenance of complex cascading lists
Simplifying cascading validation with complex lists Validation lists are often used in Excel to improve data entry. By defining a named range and using that as the source for a validation list, you can store your lists in one worksheet and use them throughout the workbook. The problem arises when you want the second list […]
Recovering a corrupted form or report
Recovering a form whose code no longer works If a form with VBA code suddenly starts giving messages that the event does not result in a recognised macro or function, and the code no longer runs, you may have a corrupted code module. The first step is to identify the culprit; then you need to […]
Merging Worksheets — DataWright Information Services
Merging lists to create a single unique list If you’ve ever had to combine two or more lists in Excel so that you get a unique list from the result, you’ll know that it can be a tedious job. A common example is two address lists with the same layout, and overlapping data. This article […]
Rebuilding a corrupt Access table
Rebuilding a Corrupt Access Table Recently, a client’s database started behaving strangely. It had worked well for about 10 years but now there were signs that all was not well; Queries, forms and reports started giving error messages about missing fields. In some cases the ‘missing’ field had a name and we could verify that […]
