Database Problems - Urgent!

[Indy23]

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:

SELECT tblStaff.StaffID
FROM tblStaff
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.

Thanks,
Indy23

[HiTechCoach]

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?

I would do whatever the teacher said to do.

[Indy23]

One of the reasons that I am on this forum is because my teacher just happens to be on leave. I have sought advice from local teachers and that has only made more problems. If you don't have any reasonable advice, I suggest that you should bother posting. I am only in my HSC year and consider this assignment very important.

Thanks,
Indy23

[Indy23]

**Shouldn't**

[HiTechCoach]

You should remember that forum members dedicate their time voluntarily for helping or guiding user's of this site with finding problem solutions. However, the members will not do your homework or work for you without some honest effort on your part.

I understand that starting off in MS Access can be very confusing and difficult, especially when under pressure because of time limitations on your project. Nevertheless, it is of no benefit for you to have someone do the work for you.

Sooner or later there will come a time when you have to show your abilities in person and under pressure and having passed of someone else’s work for your own will not help you at all in accomplishing that task.

One of the most effective ways to learn is to study, analyze, get involved, evaluate, modify, test...and forum members are happy to help you along any of these steps.

[Indy23]

For eligibility to complete the HSC, each student must agree not to plagarise another's work. I have worked on this project for two months straight, which has been difficult, considering that I am studying this subject through distance ed and am aiming to achieve the best marks possible in all of my subjects in order to be accepted into uni.

I am not asking you to "do my homework" but for simple suggestions and aid so that I can fix all of this myself. I understand how a forum works and need you to understand that I am not someone who doesn't put in the work and I believe that you should not be discriminatory, especially based on age.

[HiTechCoach]

One of the reasons that I am on this forum is because my teacher just happens to be on leave. I have sought advice from local teachers and that has only made more problems. If you don't have any reasonable advice, I suggest that you should bother posting. I am only in my HSC year and consider this assignment very important.

Thanks,
Indy23

Indy23,

I was simply answering the first question I came to your post.

In the past 7 years after 30,000+ posts on other Access support sites this is the first time I have every been told my advice was not reasonable. I am sorry that you did not find my answer to your question as reasonable advice.

Since it appears that you don't want my assistance/advice I am going to respectfully back off and let someone else help you.

Good luck with your assignment ...

PS:
I had spent over 15 minutes reading/rereading your post trying to figure out how to help you.

[Indy23]

I'm sorry, I'm just frustrated. I am sick of trying only to have more problems arise. I'm also trying to avoid creating it all over again. Thanks =/

[Allan Murphy]

Re your problem with the drop down boxes.

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

Using the above example you will need code similar to this

SELECT tblStaff.StaffID,[surname] & " " & [firstname] as name
FROM tblStaff
ORDER BY [surname] & " " & [firstname]

This will display a list of staff ids and staff names in ascending order. You will need to hide the first field and bound the first field to StaffID. Look up Combo Box in the help file regarding binding columns to fields etc.

[Indy23]

Thanks for that, I used a modification of that code and couldn't figure out what was going wrong with that until I looked at the column widths, I had it set wrong