Making an Insurance Replacement DB.... 1

[petrosky]

Hi there,

I am a fairly novice Access user.
I want to create a database that will allow the user to enter in client details, their insurance company, their lost or stolen equipment and have Access enter in the current replacement products and pricing.

Of course these products will change from time to time but eventually the DB will take away a lot of legwork for the user.

My prob is getting Access to look-up the required values and my relationships stucture looks like a bowl of spaghetti.
I have the following tables...

Employees
Insurance Company
Claimant
Goods Lost
Replacement Goods

Is this project simply too big or would anyone have any recommendations as to the relationships I should employ?

Any help is gratefully appreciated.

Peter.

 

[Philly44]

The relationship I would suggest is as follows:

ClaimentID ->Primary Key, One to Many relationship with InsuranceCompany, Goods Lost, and Replacement Goods. These raltionships will make it easier to references all the information about a specific claiment without having to enter them more than once in the system.

In your employees table make sure to have an AutoNumber field called EmployeeID and use that as the primary key.
Create a query for this table(include all relevant fields(employeeID, name), try to follow that order.

I would guess you have a "Data entered by" field in one of you tables(this is where the employees query comes in). On the form that is being used to enter data place a combo box and set its is RowSource to this query and its RecordSource to EmployeeID and set its bound column to 1(if you only see numbers when you run this form and not names then look at the query in design view and count how many fields are used in the query before it and then subtract 1 from that number and set the bound column to that) the reason you subtract one is bound column numbers start at 0.

If you have any problems just let me know

 

[petrosky]

Hi Philly44,

Thanks for your help on this, but I really need to know how Access is going to "look-up" and display the appropriate replacement camera when the user selects the goods lost/stolen from a list/combo box type situation.

Thanks in advance,

Peter.

 

[Philly44]

A list box can be looked upon similar to a spreadsheet. Theres are rows and columns in a listbox. Basing the listbox on its own query you can choose what informaton is displayed in the box but thats necasserily what it will be indexed as.

ex: You have a query that "looks" in the following fields
ClaimentID,ClaimentName,GoodsLostID,ReplacementID,ProductName ClaimentID and ClaimentName would be in the Claiment Table,GoodsLostID would be in the Goods lost Table and ReplacementID and ProductName would be in the ReplacementGoods.

The way a listbox could be setup to handle this is as follows:
Set the column number to the number of fields referenced, in this case 5. Then go to the ColumnWidths property and set it to 0";0";0";0";1". What this does is rather than displaying the entire result of the query it only shows the name of the product(camera). Now you will have to play around with the fields so its accessing the right info.

I hope this clears things up a little. If not just let me know