Getting started with searching the data file.

The Tratte Data Base is a large list of names with relevant information attached. Each record is an office-holding event, an individual who was ‘seated’ in office, or was rejected for the reason indicated by the RDRAW code. Computer searches of the file make it possible to make discrete selections from the list and to arrange these selections in different orders that enhance their meaning. The most common discrete arrangement is an "office-holding history" showing offices held by an individual in chronological order. In Series 10 (Birth Dates) a record for the birth date of the individual may also appear so that his "office holding history" will begin with his date of birth. To assist users, we have pre-arranged seven different search procedures. Each will generate an SQL (Standard Query Language) search program to permit you to search the file merely by filling in the appropriate boxes. The last procedure will also permit you to input a raw SQL search program of your own for more advanced searches or statistical analyses. Further information is in text files: the Historical Overview, Citation Instructions, Notes on the coding and editing of the data file, and the Short Codebook, Note on Names, List of surnames found in the Tratte Data File, List of first names, Results of drawing (RDRAW) codes, Dates of purses (DPURS), List of occupations and List of origins. Before proceeding further, you should read the Historical Overview carefully, and print out the Short Codebook and RDRAW codes for your own reference. You can also print out the other documentation files, including this one (but printing out the List of Surnames will require many pages--about 40). You can also save the text files as 'html' files on your computer for further reference (with your browser File -> Save As).

Series: The data file is arranged in three series: 05=office holding records for the Tre Maggiori (the executive offices) 1282-1532; 06=records for elections of the Mercanzia and Guild consulates for the years 1393-1421, 1429-1444, 1465-1474 and 1480-1498; 10=Birth registrations, that is, birth dates beginning in some cases in the 1380s-1390s and extending to 1530. Click boxes at the bottom of the search screens will permit you to choose one series, or to search all three series together or in combination. The Tre Maggiori series is the most complete. Since there are few or no records in the Guilds and Births series before the 1390s, if you are interested in the period before 1390 you should limit yourself to the Tre Maggiori.

We anticipate that users of the file will have some knowledge of Florentine History, and a moderate amount of computer skill. The search procedures should work well on any recent Internet browser. The procedures were tested mostly on a PC using Windows. (Speed will depend on your computer, connection and browser.)

Detailed instructions for using the seven procedures are contained in separate sub-pages below . They can also be called up from the "Help" screen within each procedure.

Note on printing and downloading data. We permit you to print out, or download in a spreadsheet format, small discrete parts of the file for use in your own research. (Please refer to the Citation Instructions.) The different procedures will produce results screens of different widths. The most common are a 7-column screen, a 12-column screen, and a 24-column screen (all columns). You should be able to print out the 7-column and 12-column screens on your printer using the ‘print’ option of your Internet browser and the options that may be available with your printer. At a minimum reduce the left and right margins to ‘0’ (Print Setup), and set your printer (Properties) to print in ‘landscape’ mode. When viewing the results screens on your computer monitor, if you find that all the information for one record is not contained on one line you can use the ‘decrease font-size’ [View] option of your browser to reduce the size of the font slightly so as to correct this problem.

You will not be able to print out the 24-column screen (all columns)--it will be too wide. However, you can download the 24-column screens (and any of the others) in a format that you can open with your own spreadsheet program (such as Microsoft EXCEL). To do this, decide on, or create, a directory where you plan to store the data. Click on the blue Download to Spreadsheet line at the bottom of the results screen, and follow the instructions that will appear (you will have to decide on a file name and directory location). The data will be downloaded as a ‘.php3’ file (this preserves the variable names as a header in the first row so that you will know what the columns of data are). Set your computer browser to open ‘php3’ files with your spreadsheet program. You will need to adjust the column widths in the resulting spreadsheet (names should have a 13 column width, others can have less), and then you can use your spreadsheet’s options to scale the output to a width appropriate to your printer, or you can further sort or edit the data as you wish. (In the second results screen of the 'Information about a family or individual' procedure, you need to click the [Select all Boxes] and one of the [Display] boxes before downloading.)

Note on Result Set Limits. The number of records possible in a result set has been set as a default to 300. This is to avoid lengthy downloads, and it will be sufficient for most result sets. If you see that the number of records in the result set equals the result set limit, you will need to increase the Limit to 1000 or more (in the box provided), and resubmit the query so as to receive all of the records.

You can click on one of the blue lines below to go to the section of this document that has detailed instructions for each procedure.

Search for information about a family or individual in the data base.

Display the appearance of a family name in the Tre Maggiori in graphic form.

Display the appearance of a family name among consuls of different guilds in graphic form.

Print out sorted lists of family members for constructing a rough genealogy.

Find out who was in office in the Tre Maggiori at a specific date.

Find out the consuls of different guilds in a specific year.

Display a list of individuals meeting user -defined criteria, or a Raw SQL query.

Maps. We have constructed three maps for 1320-40, 1420-50 and 1515-30 with name-lists attached to help you see what districts of the city, and purses among the major and minor guilds in the later two periods, surnames were associated with. The name list for the 1320-40 map was constructed from surnames seated as priors in the six Sestieri of the city. The name lists for 1420-50 and 1515-30 were constructed from surnames seated as Gonfalonieri di Compagnia (the only office selected by Gonfalone) for the sixteen Gonfaloni. You can click on a district number on the map to see what group of families appear in proximity in that district. There are also alphabetical lists for each period so that you can easily find the district (and purse) of each surname (some surnames had more than one affiliation). The underlying outline map is based on the Bonsignori map of 1584 and obviously includes some features (most notably the Fortezza da Basso [built in 1534-35] and the interior wall in the southern third of the S. Spirito quarter [built by Duke Cosimo I in the 1550s]) that did not exist at the time of the Republic. In the 1320-40 map some of the street grid outside of the 1173-75 second circuit of walls did not exist yet (the third circuit was completed in 1333). In 1320-40 and in 1420-50 the piazza in front of Palazzo Pitti was not there (although it was there in 1515-30). (The boundaries of Gonfaloni were approximated on the basis of the reconstruction of G. Carocci [L’Illustratore Fiorentino. 1909: 81-89] and on notations in early sixteenth-century censuses.)

The name list for 1515-30 is a bit misleading. Not only are there fewer names than in 1420-50 (because the period of time was half); the Gonfalonieri di Compagnia (Captains of the Militia) were a rather menial office not sought after by the more opulent families. If a surname does not appear in the list it may be that the name was not seated in that period. This affects the name lists more seriously in 1515-30 than in 1420-50. Although the Medici appear as Gonfalonieri di Compagnia in 1420-50, not one appeared in this office in 1515-30 (and ‘Medici’ is thus not in that name list, although they were clearly still in S. Giovanni, Leon d’Oro). Similarly, a branch of the Gondi family (whose home Gonfalone was S. Maria Novella, Leon Bianco in 1420-50 and 1515-30) had moved to S. Croce, Bue in the mid-fifteenth century, where Giuliano di Leonardo Gondi commissioned a sumptuous palace from Giuliano da Sangallo in Piazza S. Firenze. That branch of the Gondi held other offices from S. Croce in the early sixteenth century, but none of them appear as Gonfalonieri di Compagnia from S. Croce, Bue.

Search for information about a family or individual in the data base.

This procedure permits you to search for records for a family or individual in the data base by filling in boxes. When you click on the procedure you will receive a ‘query’ screen with four columns and seven rows under the title ‘Search for records where’: the first column has ‘SURNAM1’ in top row, the second an ‘=‘ sign, the third is blank; the fourth has the word ‘and’.

Click on the down-arrow button at the right of ‘SURNAM1’; you will see the variable names SURNAM1, NAME1, NAME2, NAME3, NAME4, and SURNAM2 repeated. Clicking on any of these variable names will permit you to change the variable being searched in the first row. The same will happen for the remaining rows of that column. Click on the down-arrow button at the right of the ‘=‘ sign. There is a list of other symbols that you can also choose, but for this procedure ‘=‘ is all you need. There is no down-arrow button at the right of the third column. Clicking on the down-arrow button to the right of ‘and’ in the fourth column will produce the word ‘or’. This column permits you to do Boolean searches (and/or); you can click to highlight one or the other.

Enter the SURNAM1 MEDICI (upper or lower case) in the top row of the third column, or any other SURNAM1 from the List of Surnames (you can use your browser’s ‘copy’ and ‘paste’ option from the List to the Query Screen to be sure you have the spelling right). In this procedure you have to enter a name in the first row of the third column. If you were searching for NAME1 you would change SURNAM1 to NAME1 in the list (down arrow) for the first column of the first row. As an example, try SURNAM1 MEDICI. Check the box for "Tre Maggiori" at the bottom of the screen (it is already checked as the default--it can be un-clicked if need be) Then click on the [Submit] button.

You will now receive the first results screen, an index of all records with the SURNAM1 MEDICI in the Tre Maggiori arranged alphabetically by NAME1 (the given name) and NAME2 (the first patronymic). In the first column under the heading ‘SER’ code numbers will appear (05, 06, 10) indicating in which series that combination of NAME1, NAME2 and SURNAM1 appears--these codes will remain the same irrespective of which series you are searching The display will also tell you the number of records in the series you are searching for that combination of NAME1 and NAME2 (if no NAME2 was present that cell will be blank), and the first and last date (YDRAW) when the name occurred the series. There is a blue Search line at the end of each record that will permit you to see more detail for all of the records with those names. There is also a check box, at the right, and there are more click buttons at the bottom of the screen. The check boxes and the buttons permit you to select two or more names and view the combined records for them.

For instance, you will see an AVERARDO MEDICI (1309-1473) with no NAME2, followed by three AVERARDOs with NAME2s. You could guess that some of the records of the first AVERARDO (one of the Medici’s early progenitors) might be those of AVERARDO di BERNARDO (1441-1496). You can click the check box to select the two names, and then click on the [Display results] button at the bottom of the screen and resort the records by YDRAW. It does appear that one of the records for AVERARDO (in 1473) probably belongs to AVERARDO di BERNARDO. You can check the boxes for all the records for AVERARDO di BERNARO and also the record for AVERARDO in 1473. Then use the [Display results] button at the bottom of the screen to display just the corrected list for AVERARDO di BERNARDO. Click on the [Resort by YDRAW] button to get the records back into the proper order. (If you then do a second search on the first results screen remember to clear the check boxes you previously checked. There is a [Uncheck all boxes] button to help you do this].)

Also, at the bottom of the screen, you will see a box with the heading "Previous Query as Raw SQL". This is the SQL program your initial query generated. It is there to give you examples of SQL queries, and to help you determine any errors you made when filling in boxes in the initial query screen.

Let us look for Cosimo di Giovanni di Bicci [de] Medici (the Pater Patriae). The results screen shows the Medici who appeared in series ‘05’, the Tre Maggiori. Scroll down the Medici until you reach COSIMO [di] GIOVANNI (1416-1468) and then click on the blue Search line at the right. This will lead you to the second results screen with seven columns of information. It was designed to help you further sort out family members.

You will notice that your search for COSIMO [di] GIOVANNI actually produced two Cosimo di Giovanni’s: Cosimo di Giovanni di Bicci, and Cosimo di Giovanni di Cosimo (his grandson). The records you searched for are sorted in chronological order, and the OFFICE and RDRAW codes are at the right. You can see that Cosimo di Giovanni di Cosimo was drawn for the 8 Priors in 1459 but was found to be under-aged (RDRAW=05); he was drawn again for the 12 Buonuomini in 1460 and was found to be dead (RDRAW=09). You know that Cosimo di Givoanni di Bicci died in 1464; apparently his name was still in the purse when he was drawn for the 16 Gonfalonieri di Compagnia in 1465. The last record in the set is ambiguous. Both men were dead by then, but you don’t know whether the last record is Cosimo di Giovanni di Bicci or Cosimo di Giovanni di Cosimo. [Of course you could have tried to simplify this by specifying more completely in the first Query screen SURNAM1=MEDICI [and] NAME1=COSIMO [and] NAME2=GIOVANNI [and] NAME3=BICCI. But the problem here would be that at least once (in 1438) the scribes of the Tratte didn’t record the second patronymic (we assume it must have been he), so that by asking for all three names you would have missed that record. It is better to get too many records, and then exclude some, than not to get all. Another reason for beginning any search with a surname is, of course, that you may not know the patronymic of the person you are looking for. But by looking down the list of names, and dates, you should be able to find him.]

To select only the records for Cosimo di Giovanni di Bicci, click a check mark into all the boxes for him in the right margin (assuming that it was he in 1438), and then go to the bottom of the screen. You have two valid options: the [Display Checked Items [same columns]] or the [Display Checked Items [all columns]] buttons. Clicking on the first button will display the same columns you already have, but just for the records you assume are for Cosimo di Giovanni di Bicci; checking the second button will display all 24 columns of information for these records. (You may also need to click on the button [Resort by YDRAW] to return the records to the right chronological order when moving from screen to screen.)

You also need to interpret the RDRAW codes for Cosimo di Giovanni di Bicci. ‘01’ means that he was seated in the office; ‘06’ that he was ill or otherwise unable; ‘41’ is the indication ‘non juravit’; he apparently didn’t accept the office; ‘43’ means that he was ‘in specolo’ (tax arrears). Tax arrears might seem strange for Cosimo de’Medici. We thought there might be a coding error and rechecked Cosimo’s records, but this is how the Giornali of the Tratte reported it.

For other details of Cosimo’s office-holding history go to the 26-column display for Cosimo [all columns], and look at the TELEC codes. Here ‘1’ means that a man initially seated did not complete his term, ‘5’ means an ordinary election, and ‘6’ means an out-of-term or ambiguous election. Cosimo was drawn twice for the 16 Gonfalonieri di Compagnia in 1430, once with the RDRAW code ‘01’ (seated), but with the TELEC code ‘1’ (did not complete his term), and once with the RDRAW code 41 ‘non juravit’ and the TELEC code ‘6’. This seems to have been a double entry in the Giornali--Cosimo may have "rejected" the office. He was drawn again for the 16 Gonfalonieri di Compagnia in 1450, with the RDRAW code ‘01’ but also the TELEC code ‘1’. In this case there was no double entry, but (as you can discover from the "Find out who was in office at a specific date" procedure for 1450) he was immediately replaced in that office by someone else. Cosimo may have avoided the office of Gonfaloniere di Compagnia--the only other times when he was still alive that he is reported to have been drawn for it (in 1436 and 1440) he was found to be ’in specolo’.

Now use the back arrow of your browser to return to your initial query screen. Click check marks into the two boxes for ‘Guilds’ and ‘Births’ (leaving the check mark for ‘Tre Maggiori’ in place) and resubmit your query. You will notice that more names and records appear in the results screen than did previously, and that the values for limiting years [MIN(YDRAW) and MAX(YDRAW)] have also changed to some extent. The series codes in the first column will be more various. A 05 06 10 indicates that the combination of names appears in all three series, an 05 alone means that it appears only in the Tre Maggiori, an 06 alone that it appears only in the Guild elections, and a 10 alone that it appears only in the birth records.

Scroll down the Medici again until you reach COSIMO [di] GIOVANNI. You will now see from his Birth record (SER=10) that Cosimo was born in 1389 (on 10 April as you can see from the 24-column display), and from the Guild records (SER=06) you can see that he was drawn 14 times in guild elections, 10 times for the Arte del Cambio (OFFICE=23) and 4 times for the Mercanzia (OFFICE=06). However, for various reasons, he was "seated" among the consuls of the Arte del Cambio only twice (in 1420 and 1436), and he was never "seated" in the Mercanzia. Of course, you need to remember that the series of Guild elections is fragmentary, and that registers do not exist for a good part of Cosimo’s lifetime (the years 1421-29 and 1444-65 are missing), so Cosimo may have been drawn other times in Guild elections (in fact he was drawn for the Arte del Cambio six times between 1465 and 1480 after his death). With regard to Cosimo’s grandson, COSIMO [di] GIOVANNI [di] COSIMO, you can see that a Birth record for him is missing. We know, however that Cosimo’s grandson was born in 1456 and died in 1459 so that it is unlikely that much more could be found out about him from the data file, even if records for Guild elections existed for the period of his brief lifetime.

Let us try another example: Lorenzo di Piero de’ Medici (The Magnificent). Go back to the first results screen you just created (for Tre Maggiori, Guilds, and Births all together), scroll down the Medici until you reach Lorenzo [di] Piero, and then click on the blue Search line. Again, you will see that you retrieved two Lorenzo di Piero’s (Lorenzo di Piero di Cosimo [The Magnificent] in 1449-95, and Lorenzo di Piero di Lorenzo [his grandson, the Lorenzo known as the Duke of Urbino] who was ‘in specolo’ in 1513). Another curious fact appears from the RDRAW codes for Lorenzo di Magnificent--when drawn for office in the Tre Maggiori (SER=05) he always proved to be too young to be ‘seated’ (RDRAW=05). You need to remember that fathers sometimes entered the names of their very under-aged sons in scrutinies. The minimum age for holding the office of Gonfaloniere di Giustizia was 40; for most other offices it was 30. You can see from his birth record (SER=10) that Lorenzo the Magnificent was born in 1449, and the earliest purse his name was in when he was drawn for the Priorate in 1454 (as you can see from the 24 column display) was the combined purses of 1444/1453 (it must have been the purse of 1453, when he was four years old). When he was first drawn for the Priorate he was only five. When he died in 1492 he was only 43; just above the minimum age to be seated as Gonfaloniere di Giustizia.

However, Lorenzo was also drawn seven times in Guild elections, twice for the Mercanzia (in 1472 and 1481 when he was too young), twice for the Arte dei Mercatanti (a different guild from his grand-father’s), twice when he was seated (in 1482 ane 1487) and three times after his death. The guild elections, though, are missing (in 1474-1480) for a part of Lorenzo’s lifetime. As for his grandson, the Duke of Urbino, there is no birth record in the data file, so all we know is that he was ‘in specolo’ when he was drawn for the office of Gonfaloniere di Giustizia in the Tre Maggiori in 1513.

Birth records exist for about 80 per cent of individuals who held office after the early fifteenth century, but about 30 per cent of the Birth records are for individuals who later never held office. Some individuals registered their births more than once (sometimes declaring different dates); we removed the later duplicates when the information was the same but kept those where there was a difference. If you see an individual who from their Birth and office holding records seems to be doing something at an improbably early age, look to see if another family member with the same NAME1 and NAME2 was not still active after the birth of the first.

A final Medici example--go back to the first results screen (for Tre Maggiori, Guilds and Births combined) and scroll down the Medici until you reach Giovanni di Bernardino (1325-41) and Giovanni di Bernardo (1340). You already looked at an example (Averardo and Averardo di Bernardo) where records needed to be combined because one was missing a patronymic. In the case of Giovanni di Bernardino and Giovanni di Bernardo we did not standardize the name ‘Bernardino’ into ‘Bernardo’, although the two individuals appear to be the same. Clear any check boxes you may have checked in previous searches, and check these two names. Then go to the boxes at the bottom of the screen and click [Display results]. Click [Resort by YDRAW] to return the records to chronological order. You will need to remain attentive to such variants of names that have to be adjusted.

For more advice about names look at the Note on Names in the Note on the coding and editing of the data Remember that you can search for two variants of names at the same time (SURNAM1=X, or SURNAM1=Y in the first query screen). Try out the examples for the Niccolini/Sirigatti, the Dietisalvi/Neroni, the Migliorotti, and the Buonarroti-Simoni discussed in the Note on Names. Remember that some surnames may also be disguised as patronymics. Thus in searching for a particular individual you should also do a search for his NAME1 and NAME2, but you have to be careful of the implied SQL syntax of search commands when you do this. To look for individuals without a surname, whose NAME1 and NAME2 may be either Giovanni [di] Bernardo or Giovanni [di] Bernardino, the correct syntax for filling in the boxes is: Search for records where SURNAM1=" " (three blanks enclosed in quotation marks--the query for a blank name) and NAME1=GIOVANNI and NAME2=BERNARDO or SURNAM1=" " and NAME1=GIOVANNI and NAME2=BERNARDINO. Or, better still, do this command leaving out SURNAM1=" " to get all cases with that NAME1 and NAME2 in the data file regardless of whether they had a SURNAM1 or not.

Further note: You cannot display all of the 12 or 24-column records at once for all individuals with a given surname from this procedure; the SQL program will not be able to handle that many records at one time. However, you can display all the 12-column records for a given surname from the "genealogy" procedure, or either the 12 or 24-column records from the "user-defined" procedure.

These examples should give you sufficient insight to proceed further with the first procedure on your own.

Display the appearance of a family name in the Tre Maggiori in graphic form.

This procedure permits you to get a quick over-view of the chronological span of appearance of a surname among those seated in office: was it an old family?, a new family?, a family appearing often in the Signoria?, or a family appearing mostly in the Collegi? The procedure is quite straight-forward. Type a SURNAM1 from the List of Surnames into the box provided, and click on the [SUBMIT] button. The SQL program will count the number of appearances of the SURNAM1 in the different offices by 10-year periods of YDRAW, and provide row and column totals. This procedure can only handle one SURNAM1 at a time.

Display the appearance of a family name among consuls of different guilds in graphic form.

This procedure works in the same way as the previous one for the Tre Maggiori. The table shows the number of appearances of a surname "seated" among the guild consuls for the Mercanzia and twenty of the twenty-one guilds (there are no data for the Arte dei Giudici e Notai), for the four periods where data exist during the fifteenth century (1393-1421, 1429-1444, 1465-1474 and 1480-98). We found it surprising that some family names were distributed quite broadly among different guilds (and even between major and minor guilds), a phenomenon meriting of further exploration and research. The procedure can only handle one SURNAM1 at a time.

Print out sorted lists of family members for constructing a rough genealogy.

Since patronymics of most individuals are present in the data file, it is possible to arrange lists of family members in such a way as to provide raw materials for making a very rough family genealogy. Only information for males in relatively mature years is available, but the patronymics may permit you to infer the existence of some ascendant kin who are not actually present in the file. You will have to make your own judgments and construct the genealogy yourself.

You should be cautioned that this will not work well for large families with many lateral branches because of the likelihood that names and patronymics were repeated in lateral branches, and you will not know which. It would be better to use one of the published genealogies of Luigi Passerini in Pompeo Litta, Familie Celebri Italiani (Milan: 1819-1875) [Albizzi (vol. 11), Aldobrandini (4), Buonarroti (2), Buondelmonte (3), Capponi (10), Da Uzzano (11), Guiccicardini (2), Machiavelli (3), Medici (2), Panciatichi (9), Pazzi (9), Pucci (10), Soderini (9), Strozzi (4), Tornabuoni (3), Valori (1), Vettori (3), Villani (6)], or one of the genealogies published separately by Luigi Passerini [Alberti (1870), Altoviti (1871), Corsini (1858), Ginori (1876), Niccolini (1870), Panciatichi (1858), Rucellai (1870)]. However, the data file may be useful for constructing rough genealogies for small and not-well-known families.

The procedure is quite straight-forward. Type a SURNAM1 from the List of Surnames in the box, check the boxes for Tre Maggiori, Guilds and Births, and click on [Submit]. You will receive the first result set called ‘Sort by NAME1’. This will be all records for that surname sorted by NAME1 by NAME2 by YDRAW--the information for each individual, with the times when they ‘flourished’ provided by the YDRAW dates. You need to check to be sure you did not receive more records than the pre-set Set Limit allowed (300 records). If you did, increase the Limit to 1000 or more and re-submit.

When you then click on the button marked [Sort by NAME2] you will receive the second result set, which will sort the names by NAME2 by NAME1 by YDRAW. This will give you the sons of a common father. You will have to interpret relationships to the second and third patronymics (NAME3 and NAME4). You can print out the two lists (set the right and left margins from your browser [Page setup] to ‘0’ and the print mode of your printer [Properties] to Landscape Printing), or you can download the two lists to your hard disk and use them with your own spreadsheet program. For the early fifteenth century you may be able to identify further family members by consulting the Online Catasto of 1427 (but remember that surnames in the Catasto are truncated after 10 characters, while they have 11 characters in the Tratte).

As an example we attach a rough genealogy of the Giandonati family made with the help of this procedure, and with additional names added from the Online Catasto.

Find out who was in office in the Tre Maggiori at a specific date.

This procedure attempts to provide you with a list of men "seated and occupying" all the offices of the Tre Maggori in any year (or any month of any year) during the 250-year history of the Republic. It is a computer simulation, and thus may not be perfectly accurate in every case. It takes into consideration the fact that offices had different terms of different lengths (six terms of two months per year for the Gonfaloniere di Giustizia, Notaio, and Priors; four terms of three months for the Buonuomini, and three terms of four months for the Gonfalonieri di Compagnia--See the Historical Overview). Some "rounding" of dates was required to standardize the different days of the month when men entered office. Before 1343, men seem to have entered office about the 15th day of the month. After 1343, the Gonfaloniere di Giustizia, Notaio, and Priori entered office on the first day of the months and they were elected the last days of the month before they entered office; the Buonuomini continued to enter office on the 15th day of the month, a few days after their election; the Gonfalonieri di Compagnia entered office about the 7th day of the month after their election in the last day of the previous month. As well, some office holders were elected "late", or were elected in the middle of terms to replace other office holders.

We "rounded" dates as follows. Each set of office holders was assigned a "term" (the 1st to 6th term in the year for the Signoria; the 1st to 4th term for the Buonuomini, and the 1st to 3rd term for the Gonfalonieri di Compagnia). The first term of the year for all offices was assumed to begin on January 1st. All dates in the file have been adjusted to the modern system of beginning the year on January 1st. A new variable (YEAR) was created to account for the fact that some men drawn for office in December of the previous year (YDRAW) actually served almost all of their term in office in the following year. Men drawn for office in mid-to-late December were assigned to the first term of the following year. The assumption that all terms began on the first of the month creates problems in the result sets before 1343 for all offices, and after 1343 for the Buonuomini and Gonfalonieri di Compagnia. Before 1343, dates in the data file (YDRAW) are the dates when men entered office. But when they entered office on the 15th of the month, or occasionally even before, we represented their term as beginning on the first day of the following month (and the following year for men selected in November-December). After 1343 dates in the data file (YDRAW) are the dates when men were elected for office, usually a few days before they actually entered office. We have counted their term as beginning in the month after their election. Thus after 1343 for the Gonfaloniere di Giustizia, Notaio, and Priors, the month dates are exact (they were elected the last days of the previous month and were counted as having entered office on the first day of the month after their election [and of the following year for those elected at the end of December]). For Buonuomini, the names listed in December, March, June, and September are those of men completing their term in that month; the men who replaced them on the 15th of the month are counted as entering office at the beginning of the following month. For Gonfalonieri di Compagnia, the men counted as being in office in January, May, and October were those who actually entered office about the seventh day of the month, the men in office the first week of the month are those completing their term; they are listed for the previous month. For the other months of the year (February, April, July, August, October, November) the attributions are exact; all office holders had either entered office on the first of the month or were still serving in terms that had begun in previous months. For greater clarity the results sets for this procedure include the original dates of DDRAW MDRAW YDRAW so that you can see when individuals had actually been seated (before 1343) or elected (after 1343).

Men entering office "late", or who replaced others, will be represented as being in office during the term of their colleagues. You will notice that in some terms there may seem to be too many persons in office (too many men, for instance, from the Santa Croce quarter serving as prior). Look at the TELEC codes. A ‘5’ is a regular election; a ‘1’ is an office holder who did not complete his term; a ‘2’ is an office holder elected to replace an office holder who did not complete his term; a ‘6’ is an ambiguous election--a "special election", or someone elected "late", or someone elected to replace another who was not specified. Sometimes there may be too many men and all the TELEC codes are ‘5’. In these cases, is it likely that someone had replaced someone else, but this was not specified in the Giornali of the Tratte. If there are not enough men, a record may be missing from the data file or from the Giornali (in a few cases we have entered blanked records here [TELEC=9]).

With these adjustments and cautions in mind, the procedure works well for months except in years when there were extraordinary substitutions in office (1343, 1378--the Ciompi; 1527--the Last Republic). For these years you may not get good results from displaying men in office by month; to sort things out better look at the list of office holders for the entire "year".

The procedure works in a straight-forward manner. You have two options: a list of the men "seated and occupying" offices in a given year, or a list of men "seated and occupying" offices in a given year and month. To use the first, type in a year between 1282 and 1532 in the box marked YEAR=, but leave the pull-down menu for months un-highlighted. To use the second type in a year in the box marked YEAR=, and highlight a month in the pull-down menu for months.

The results screen for a "Year" search will list office holders by term, first the six terms of Gonfalonieri di Giustizia, Notai, and Priori, then the four terms for Buonuomini, and last the three terms for Gonfalonieri di Compagnia. The column titles have been abbreviated so as to show as much information as possible on the page. You will have the OFFICE, QUAR, TPURS, TELEC, DPURS, COM1, NAME1, NAME2, SURNAM1, OCNAME, and the un-rounded values for DDRAW, MDRAW, and YDRAW. As with the other procedures , you can expand from the shortened to the 24-column display.

The results screen for a "Year and Month" search will have the same variables, but only for the men "in office" in the given month (with the qualifications about dates of entry into office indicated above).

Find out the consuls of different guilds in a specific year.

This procedure works in the same way as the previous one for the Tre Maggiori, except that men seated in office in the Mercanzia and the Guild consulates are displayed for an entire year together (for the terms when data exist) rather than for a specific month. The terms in office for the Mercanzia and Guilds were less complicated than for the Tre Maggiori. For the Mercanzia there were four terms per year (beginning in January, April, July and October) until 1477 and thereafter three (beginning in March, July and November); for the guilds for the whole period there were three terms per year (beginning in January, May and September). Terms or individual names (a few seated consuls are unknown) do not appear when there is missing data.

Display a list of individuals meeting user-defined criteria, or a Raw SQL query.

These two procedures permit you to retrieve lists of records from the data file using variables, criteria for selection, and sort orders not contained in the other pre-arranged procedures, through a more flexible format, and also to do some statistical analyses of the data by writing SQL programs yourself. The opening page of this procedure consists of another set of fill-in boxes at the top, and a box entitled Raw SQL query at the bottom where you can enter a Raw SQL query.

User-defined Fill-in-the boxes searches.

You can probably obtain any kind of list you want from the Fill-in-the-boxes searches. You will notice that there are now seven rows of boxes, and that the "Field" box, initially set to the variable SER, gives you access to all relevant variables in the data file. By clicking on the down-arrow, you can change the variable names for any of the rows in the first column. In making a search, you have to fill in a search criterion for the first row in the third column.

In the "Return the following fields" variable list, all the relevant variables are also available. Twelve are highlighted in blue as a default selection (the 12-column display you are already accustomed to). You can add variables to this list, or remove some. But you will need to remember to hold down the 'Control' key on your computer keyboard while you do this; otherwise you will loose the initial configuration, or the modifications you were making of it, and you will have to start all over again using the [RESET] button.

Two variables not listed in the Codebook are available for use here: YEAR and OFFT. YEAR is the corrected year in which individuals dawn for office (YDRAW) in December but who entered office on 1 January are counted as being in office in the following year. OFFT (see example 8 below) indicates the office and term during the year. The first two digits are the code for the office, the third digit is the number of the term (1-6 for the Signoria, 1-4 for the Buonuomini, 1-3 for the Gonfalonieri di Compagnia, 1-4 or 1-3 for the Mercanzia and Guilds).

There are also three boxes for Tre Maggiori, Guilds and Births that permit you to specify which series to search. The Tre Maggiori box is checked initially as a default (but it can be un-checked if you want to search another series). If none of the three boxes are checked you will receive a SQL error message.

There are four boxes in the "Sorted by" line, again with a choice of all variables. You can alter the default sort-order of the data file by highlighting variables here. In many cases the default sort-order (NAME1 BY NAME2 BY SURNAM1 BY YDRAW) will be sufficient for your purposes; in that case you can leave the "Sorted by" variables un-highlighted.

There is also a "Limit the results set to" line. For many searches you will need to increase the Result Set limit, to 1000 (which should be enough to show all the records for a single family), or to more than 1000 for some lists.

The following are examples of lists you can obtain by filling in the boxes.

1) Select all records in the data file with the SURNAM1 CORSINI.

Search for records were:

SURNAM1=CORSINI

(Check all three boxes for Tre Maggiori, Guilds and Births, increase the set limit to 1000 and submit. You will receive the 12-column display. If you want to receive the 24 column display you will need to highlight each variable you want to receive in the Return the following fields variable list on the left [remember to hold down the control key while doing this]. (The [Check all boxes] and [Display checked records (all columns)] buttons at the bottom of the screen will not work when more than 120 boxes are checked. If you try to use these, you will get a results set of only 120 records.) If you specify no sort order (Sorted by) the records will be sorted in the data file's underlying order,

2) Select all records with the SURNAM1 CORSINI appearing in the Tre Maggiori before 1343.

Search for records where:

SURNAM1=CORSINI and YDRAW < 1343

(Check the box for Tre Maggiori and submit.) You will receive the 12-column display and the records will be sorted in the default order.

If you wanted to find records for the CORSINI "seated" in office in the Tre Maggiori between 1435 and 1445, the syntax of the search command is slightly more complicated):

Search for records where:

SURNAM1=CORSINI and RDRAW=01 and YDRAW >= 1435 and YDRAW <= 1445 Check the box for Tre Maggiori and submit.

If you wanted to find records for the CORSINI "seated" in office in both the Tre Maggiori and the Guilds, you would submit the same command checking the two boxes for Tre Maggiori and Guilds (but you would need to remember that Guild records may not exist for all the years you have specified). If you want to know years when the CORSINI were "serving" in office, use YEAR rather than YDRAW.

3) Select all records in the file for individuals in the Tre Maggiori reported to the Notaries.

Search for records where:

OCSTAT=21

(Check the box for Tre Maggiori, increase the set limit to 3200 and submit. You will receive the 12-column display. If you want the 24-column display you will need to proceed as in example 1. Note that the occupation "Notaio" was seldom reported in the original data, except with the COM1 of "Ser"=21, which we interpreted as indicating the occupation of "Notaio". Individuals in the office 02 (Notary of the Signoria] were certainly all notaries; others not in this office may not have been.)

4) Select all Gonfalonieri di Giustizia seated in office from the Sestiere of Oltrarno, or the Quarter of S. Spirito (the Sestiere of Oltrarno became the Quarter of S. Spirito in 1343).

Search for records where:

OFFICE=01 and RDRAW=01 and QUAR=01 or OFFICE=01 and RDRAW=01 and QUAR=10

Add the variable QUAR to the ‘Return the following fields’ variable list by highlighting it (remember to hold down the control key while doing this so as that the other names will not be lost).

(Check the box for Tre Maggiori, increase the set limit to 1000 and submit. Notice here that you need to repeat the variable names and values for each part of the two implied parenthetical elements. If you specify no sort order, the records will appear in the default order. If you receive unexpected results from this query, look at the Raw SQL statement at the bottom of the screen. It will show you how the program interpreted the query you submitted through the filled-in boxes. If necessary, you can add parentheses to the Raw SQL and re-submit it.)

5) Select all Gonfalonieri di Compagnia seated in office from the Gonfalone Leon Rosso in the quarter of S. Maria Novella; sort by YDRAW.

Search for records where: OFFICE=16 and RDRAW=01 and QUAR=33

(Add QUAR to the Return the following fields variable list [holding down the Control key so as not to loose the other settings]).

Sorted by: YDRAW

Check the box for Tre Maggiori, increase Set limit to 1000 and submit.)

You could refine this search to retrieve all Gonfalonieri di Compagnia seated in office from the Gonfalone Leon Rosso in S. Maria Novella who were selected from the minor guilds:

Search for records where: OFFICE=16 and RDRAW=01 and QUAR=33 and TPURS=03

(Add both the QUAR and TPURS variables to the 'Return the following fields' variable list.)

Remember that no distinctions between major and minor guilds are available for the Buonuomini or Gonfalonieri di compagnia before 1406.

6) Select all individuals seated in office in the Tre Maggiori between 1420 and 1439 who had occupations reported as silk masters, sort the records by YDRAW by QUAR.

Search for records where:

OCSTAT=25 and RDRAW=01 and YDRAW >= 1420 and YDRAW <= 1439

Add OCSTAT to the variable list (holding down the control key so as not to loose the other settings).

Sorted by: YDRAW by QUAR

Check the box for Tre Maggiori and submit.

(Note that you should use the numerical occupation code OCSTAT rather than the individual occupation titles recorded as OCNAME because of variations in spelling in the OCNAME variable. If you did not specify other 'sorted by' variables, the records will be in the default order within the two you specified.)

7) Select members of the Medici family who were "seated" as consuls of the Arte della Lana sorted in chronological order.

Search for records were:

SURNAM1=MEDICI and OFFICE=24 and RDRAW=01

Sorted by: YDRAW

Uncheck the box for Tre Maggiori, check the box for Guilds and submit.

8) Select all individuals drawn (ie. "seen") for office in the Tre Maggiori in a particular year indicating which were "seated" in office and which were "rejected" with a reason for rejection (RDRAW code) for each.

This is a bit more complicated but not difficult. You would want to see the records for each ‘term’ for each office, using the OFFT variable (the code number for the office followed by the term [the first term in the year for the Gonfaloniere di Giustizia would be 011, the first term for the Priors would be 081]. (There were six terms for the Signoria reckoning from when individual entered office: 1=January, 2=March, 3=May, 4=July, 5=September, 6=November. For the Buonsomini there were four terms: 1=January, 2=April, 3=July, 4=October. For the Gonfalonieri there were three: 1=January, 2=May, 3=September. There was a similar progression for the Mercanzia and the Guilds.) You would need to select the year as YEAR (the year when individuals were in office--those selected [YDRAW] in December entered office in January). Use YEAR and not YDRAW when selecting the year in this procedure.

For all individuals drawn for office in 1440 do as follows:

Search for records where YEAR=1440

Modify the ‘Return the following fields’ variable list to include the following variables (some will already be highlighted--remember to hold down the Control key when doing this):

SER DDRAW MDRAW YDRAW YEAR OFFT QUAR TPURS OFFICE TELEC RDRAW COM1 NAME1 NAME2 SURNAM1 OCNAME.

Modify the ‘Sort by’ (or ‘Subsort by’) boxes to sort the data by OFFT QUAR TPURS RDRAW

Check the box for Tre Maggiori, increase the Set limit to 1000 and submit.

This will produce a long results set. The six terms for the Gonfaloniere di Giustizia will come first, followed by the six terms for the Notaio of the Signoria, followed the six terms for priors (distinguishing first in each quarter the individuals "seated" followed by those "rejected for the reason (RDRAW code) indicated for each The type of election (TELEC--a regular election or a substitution) is also indicated. These will be followed by the four terms of Buonuomini and then the three terms for Gonfalonieri di compagnia.

You might want to see just the first term in 1440 for the Gonfaloniere di Giustizia and Priors. Use the same modification to the ‘Return the following fields’ and ‘Sort by’ menus:

Search for records where YEAR=1440 and OFFT=011 or YEAR=1440 and OFFT=081 (This syntax is needed to allow for the implied parenthetical element in the statement.)

Check the box for Tre Maggori and submit (you may not need to change the Set limit).

For all individuals drawn for the Consuls of the Arte della Lana in the first term of 1440:

Search for records where YEAR=1440 and OFFT=241

Un-check the box for Tre Maggiori, check the box for Guilds and submit.

(In viewing the results of these searches, you need to remember that for some terms records exist in the file only for men "seated" in office (see the Note on coding and editing) and sometimes the only person "seen" was "seated", but sometimes many individuals had to be "seen" before the proper number were "seated". Also notice that the YDRAW for the first term will be the year before the YEAR requested because individuals selected in December entered office in January.)

Raw SQL queries.

The box at the bottom of the page will permit you to submit a Raw SQL query. This allows you to do some simple statistical counts of records in the data file. We cannot give you detailed help with SQL (Structured Query Language), besides providing you with the names of some available manuals. If you need further assistance with SQL ask someone at your computer center. But you can look at the schematic representation of the general logic and syntax of SQL at the bottom of this page. You can also look at the Raw SQL programs that appear at the bottom of the results pages for many of the other procedures.

Manuals: Martin Gruber, Understanding SQL (SYBEX: San Francisco, 1990--ISBN 0895886448); C. J. Date and Hugh Darwen, A guide to the SQL Standard (Addiston Wesley: Boston, 1993--ISBN 020155822X); Martin Gruber, SQL Instant Reference (SYBEX: San Francisco, 1993--ISBN O782111483); Ben Forta, SAMS Teach Yourself SQL in Ten Minutes (Sams Publishing: Indianapolis, 2000--ISBN 0672316641).

We have added one further variable not listed in the Codebook or the other procedures that you can use here. YEAR10 is the YEAR variable rounded into decades (1280s, 1290s, 1300s, 1310s, etc). It will permit you to group totals into 10-year periods.

You cannot use the Search, the [Select/Unselect records] or the [Display columns] buttons at the bottom of the page with Raw SQL queries. A limit of 300 records in the results set will be entered automatically. If your results set exceeds this limit, increase the LIMIT in the Previous SQL box and resubmit.

The following are sample queries that you can submit as Raw SQL queries. The data set name is TRATTE. Variable names can be typed in either upper or lower case). The ‘count(variable name, or *)’ aggregate function will be the most useful one here:

1) Count the number of times that particular SURNAM1s appeared among men seated in the office of prior between 1420 and 1433.

SELECT SURNAM1, COUNT(*) FROM TRATTE WHERE ((YDRAW >= 1420 AND YDRAW <= 1433) AND OFFICE=08 AND RDRAW=01 AND SER=05) GROUP BY SURNAM1

This will produce a list of surnames followed by a count of the number of times each surname appeared during the stated period. Of course, some office holders had no surname. You can download the results screen to be read into your own spreadsheet program and sort it, for instance, by the number of occurrences of the surname.

You can substitute the variable YEAR for the variable YDRAW, and different dates (for instance >= 1434 [> means ‘greater than’, >= means ‘greater than or equal to’] and <= 1445 [< means ‘less than’, <= means ‘less than or equal to’].

To make a list of SURNAM1s seated in office from the minor guilds in the same period the syntax would be:

SELECT SURNAM1, COUNT(*) FROM TRATTE WHERE ((YDRAW >= 1420 AND YDRAW <= 1433) AND OFFICE=08 AND RDRAW=01 AND TPURS=03 AND SER=05) GROUP BY SURNAM1

For a list of SURNAM1s seated in office from the major guilds the syntax would be:

SELECT SURNAM1, COUNT(*) FROM TRATTE WHERE ((YDDRAW >= 1420 AND YDRAW <= 1433) AND OFFICE=08 AND RDRAW EQ 01 AND SER=05 AND (TPURS=01 OR TPURS=02 OR TPURS=99)) GROUP BY SURNAM1

Remember that the TPURS designations for the minor guilds are missing between 1379 and 1399 (see the Historical Overview).

2) Count the number of times silk masters were seated in the Tre Maggiori in 10-year periods.

SELECT YEAR10, COUNT(*) FROM TRATTE WHERE (OCSTAT=25 AND RDRAW=01 AND SER=05) GROUP BY YEAR10

For the number of wool masters seated, substitute ‘OCSTAT=24’ for ‘OCSTAT=25’. Remember that occupations were not always reported, and that they were reported more frequently for men in the minor guilds than for men in the major guilds.

3) Count the number of men selected or seated in the Tre Maggiori in 10-year periods.

SELECT YEAR10, COUNT (*) FROM TRATTE WHERE SER=05 GROUP BY YEAR10

To count the number of men rejected in the Tre Maggiori because they were ‘too young’ in 10-year periods, the syntax would be:

SELECT YEAR10, COUNT(*) FROM TRATTE WHERE RDRAW=05 AND SER=05 GROUP BY YEAR10

For the number rejected because they were in ‘specolo’ (tax arrears), substitute RDRAW=43; for the number rejected because they were dead, substitute RDRAW=09. Remember that in some periods only the names of men "seated" in office are available in the file. You could substitute YDRAW or YEAR for YEAR10. You will notice that in years just before scrutinies when new purses for made (or in periods, such as the 1420s when a scrutiny had not occurred for some time) many were rejected because they were ‘dead’. In years just after scrutinies many were rejected because they were ‘too young’. The number in tax arrears increased in years just after tax surveys (1427, 1480).

4) Make a list of occupations listed for men drawn for office in the Arte della Seta (whose occupations were reported) sorted by the occupation code (OCSTAT).

SELECT OFFICE, OCSTAT, OCNAME, COUNT(*) FROM TRATTE WHERE OFFICE=25 AND SER=06 GROUP BY OFFICE, OCSTAT

(Rather various occupations that sometimes seem strange for the guild were reported for men in Guild elections--we think this may occasionally result from coding errors. However, of course, some were rejected in the drawings because they were deemed not to belong to the guild in question. To filter these out, you could use the following statement):

SELECT OFFICE, OCSTAT, OCNAME COUNT(*) FROM TRATTE WHERE OFFICE=25 AND RDRAW=01 AND SER=06 GROUP BY OFFICE, OCSTAT

You could repeat this for other guilds. If you want to see all the guilds together, substitute SER=06 for OFFICE=25 (the most occupations will appear in the Mercanzia, which you might want to exclude [AND OFFICE <> 06]).

In all Raw SQL queries, if your query exceeds the Result Set limit, change the 300 that will be added automatically to 1000 and resubmit.

The following is a schematic representation of the general logic and syntax of SQL commands.

You can use the symbols:
+, -, *, /, = ,<> [not equals]
< [less than] <= [less than or equal to]
> [greater than] >= [greater than or equal to]