Hi all. I created a database from an Excel import and used the Table Analyzer to determine the recommended splits. The Excel spreadsheet was used to keep track of purchases for a budget. I used the recommended table split choices in the Table Analyzer and ended up with these tables:
- Table 1 (the renamed original import) with ID link and lookups to the remaining three tables for the 5 fields I use) :
- tblCategories (used for my budget categories)
- tblItems (used for the item purchased, date and cost)
- tblSuppliers (the seller of the item)
The last three tables were created with parent and child records. I created a form for data entry and dragged the fields to the form that I wanted to use a dropdown choice to reduce user errors. I first selected ComboBox on the design tab which then walked me through the choices and created the sql statement used in the row source for each of the three last tables. The control source for each is the relevant field name in each table. For example the tblSuppliers has Supplier in the control Source, etc. After some tweaking of the bound columns which defaulted to 1, I got the the form to work. Mostly.
Maybe this is an expectation problem and not a real problem but new records (which in my view should be child records if the parent record already exists) are being created as new records duplicating the parent record with the same name and with a new child with the new data. Here is the sql in the Row Source property:
SELECT [tblCategories].[ID], [tblCategories].[Category] FROM tblCategories ORDER BY [Category];
I do want to create new parents as needed in all but one table (tblCategories). I changed the Limit to List property on that table to Yes but set to no on the the other two. I have looked at what I can but can't figure this out. It may need some sql someplace but I don't know what or where.
If it helps I attached a zip file with a combo box property sheet for Categories and a relationship view for all tables.
I'd really appreciate some guidance!
tmguru
- Table 1 (the renamed original import) with ID link and lookups to the remaining three tables for the 5 fields I use) :
- tblCategories (used for my budget categories)
- tblItems (used for the item purchased, date and cost)
- tblSuppliers (the seller of the item)
The last three tables were created with parent and child records. I created a form for data entry and dragged the fields to the form that I wanted to use a dropdown choice to reduce user errors. I first selected ComboBox on the design tab which then walked me through the choices and created the sql statement used in the row source for each of the three last tables. The control source for each is the relevant field name in each table. For example the tblSuppliers has Supplier in the control Source, etc. After some tweaking of the bound columns which defaulted to 1, I got the the form to work. Mostly.
Maybe this is an expectation problem and not a real problem but new records (which in my view should be child records if the parent record already exists) are being created as new records duplicating the parent record with the same name and with a new child with the new data. Here is the sql in the Row Source property:
SELECT [tblCategories].[ID], [tblCategories].[Category] FROM tblCategories ORDER BY [Category];
I do want to create new parents as needed in all but one table (tblCategories). I changed the Limit to List property on that table to Yes but set to no on the the other two. I have looked at what I can but can't figure this out. It may need some sql someplace but I don't know what or where.
If it helps I attached a zip file with a combo box property sheet for Categories and a relationship view for all tables.
I'd really appreciate some guidance!
tmguru
