Dataset details: CRDA/19/DS/1

This dataset was transferred to NDAD by HM Customs and Excise on a CD-ROM on the 26th October 1998.

The transfer is a snapshot copy of the Beer duty dataset as at October 1998, incorporating data gathered as required under the 1979 Alcoholic Liquor Act.

The dataset provides brewer details such as name, address, reference, and name of contact in brewery. It includes the names and references of old and new collections or regions, e.g. 'Central England', 'North West'. It also provides details of brewers' returns, amount of duty paid, quantity of beer on which duty is paid, deductions as a result of spoilt beer and drawback. It also provides information such as the names of brewers' business partners, brewers' VAT registration number, bank account numbers, and their location geographically.

There is detail on the class of beer brewed:

• Class A: bulk beer brewed on the same or adjacent premises at which it was produced
• Class B: Packaged beer held on the same or adjacent premises at which it was produced or packaged
• Class C: Bulk beer which was produced elsewhere
• Class D: Packaged beer which was produced elsewhere

The dataset indicates whether a brewer submits a weekly return, as well as how much beer is delivered for consumption in bulk hectolitres. It refers to the brewer's status, detailing whether it is a company limited by guarantee, an education establishment, in receivership, a limited company, or whether it has a sole proprietor. A brewer's registration status is also noted, for example whether the brewer is only a holder of beer or both a holder and producer. It also deals with returns by brewers including under-declarations and over-declarations in previous periods, the amount of duty claimed back because of spoilt beer and the amount of duty reclaimed as drawback including the sum of amounts claimed as spoilt beer and drawback.

It deals with EX46(VAT) which brewers are required to pay, giving brewers' details, their collection ID and VAT registration number. This also includes information concerning account tax type, amount of VAT paid, and total amount paid by each brewer as well as other general information concerning payment of the VAT. Other information includes time periods within which the returns are to be collected.

The Beer Duty system as transferred to NDAD comprised a Microsoft Access (version 2.0) database. The transfer consisted of two files, whose original names were beerduty.mdb and beerduty.ldb. The .ldb file was used to store locking information. 1

Two of the tables used in the Beer Duty system were found contained in 'attached' or 'linked' tables. 2 Data in the attached table remains in the external database, but it can be accessed and manipulated within the current Microsoft Access database. As these tables were not contained within the original beerduty.mdb file, they were transferred to NDAD as Microsoft Word documents (version unknown).

The tables in CRDA/19/DS/1 were created by NDAD using the File Export function in Microsoft Access. The tables in beerduty.mdb were exported by NDAD in comma separated variable (CSV) format. The attached tables received in Word format were copied by NDAD into Excel, version 97, and saved in comma separated variable (CSV) format.

Access to the Beer Duty dataset is restricted under the Finance Act, 1989, section 182. An LCI for a closure period of 75 years has been granted.

Documents relating to the Beer Duty system 1998 snapshot have been transferred to NDAD, and can be consulted via the Dataset Documentation Catalogue.

The dataset consists of 18 tables. Six main tables contain the majority of the data:

• Brewers Addresses
• Brewer Details
• Brewer Return
• EX46 VAT
• EX46VAT Addresses
• Registered Addresses

The Archive Brewer Return and Archive EX46VAT tables were designed to hold archived records in the original system.

The remaining ten tables are lookup or reference tables. Three relationships between tables were defined in the original system. These are all one-to-one relationships. The BrewerAddresses and RegisteredAddresses tables are related by the BrewersRef field. Similarly, the BrewerDetails and BrewerAddresses tables are related by the BrewersRef field. The NewCollection table and the BrewerAddresses table are related by the CollectionID field. A diagram that shows these three relationships is available.

The dataset comprises the following table(s):

The data has no known constraints on its reliability. The two tables designed to store records that have been 'archived' do not hold any significant data. One of these tables holds one record and the other no records.

A small number of discrepancies were found in three tables. In the Brewer Details table (reference CRDA/DS/19/1/2) the following discrepancies were found:

In the Brewer Return table (reference CRDA/DS/19/1/3) the following discrepancies were found:

In the EX46VAT table (reference CRDA/DS/19/1/4) the following discrepancies were found:

The number of records in each table of the original Access database as transferred were compared to the number of records in each comma separated file that was created by NDAD. The numbers corresponded exactly.

Checks were made on the transformed data files and no discrepancies were found.

Some 30 simple queries were performed on a selection of fields in the 6 main tables in the original Access database file. These queries were repeated on the transformed data, and in all cases gave results which were consistent with the original queries. Most of the checks performed counted MISSING or NON-MISSING data as many of the fields are variable length strings.

1. For every database .mdb file it generates, MS Access creates a .ldb file to store locking information. Such a file is required while a database is open in a multi-user environment. See Microsoft Access Relational Database Management System for Windows Version 2.0: User's Guide, Microsoft Corporation, 1994, p.102.

2. In a Microsoft Access database, an attached table is a table in another external database. Microsoft Access Relational Database Management System for Windows Version 2.0: User's Guide, Microsoft Corporation, 1994, p.102.