Okay, so I've got an assignment in which i have to modify a database that i have already created. I am using microsoft access 2007. It is a sales database for a sports organisation. It has 6 tables - tblStaff, tblSupplier, tblSales, tblSalesItems, tblCustomers and tblStock, to record inventory, sales details, staff and customer details. I have temporarily removed the relationships because my teacher said to edit all of the ID fields from autonumber to text. Should i do that? From autonumber it will now read C001, C002, etc. but it will have to be inputted and i am not sure that's a good idea.
It has 6 forms but we only need to worry about 3 - frmMain, frmSales, frmSalesSubForm. In the sales form, there are 3 combo boxes and none of them are working. I can't figure out why. Each of the combo boxes has to have a drop down list of values from the ID field of the table. So, the combo box labelled 'StaffID' has to have a list of all the staffIDs from the table. For staffID, the control source is that field in the table, the row source reads:
WHERE (((tblStaff.StaffID)=[tblStaff]![FirstName] & " " & [tblStaff]![Surname]));
all of these combo boxes will not display the list and a data mismatch error is displayed when i click on them. They are all formatted in the same way.
Okay, now that sales details have to be entered, in tblSales i have SaleID, SaleDate, CustomerID, and StaffID. For the items that are purchased, there is tblSalesItems containing the fields SaleID, ItemID and Number. First of all, I want to normalise it so that if a number of DIFFERENT items are purchased, SaleID doesn't have to be entered everytime an item with a different ID is purchased.
Second, i think i need a calculated field but i don't know where to put it. In tblStock, there is a field called CurrentAmount. If that item is purchased, that amount will change. So, CurrentAmount = CurrentAmount - Number (tblSalesItems). What do i do? I am terrible at SQL.
Alright, last problem. when i have put the database back together, i have to write a query to test the functioning of one of the relationships, but everytime i write any SQL it doesn't work. Any suggestions?
I can probably send the database if this is unclear.