MS Access 2007 Database

[ArrestedDevelopment]

Hello fellow databasers,

I'm new to the game, and I have a (basic) sample database which I have been developing as part of some marketing recommendations for my local DIY store which is struggling at the moment. I've got all the data in to the database tables, its making it USEFUL that I've hit a wall with.

I'd provide some screenshots but its not a single part of the database that I'm stuck on, its really the whole thing. Does anyone have any general tips on how to make the info more accessible and user friendly through forms, reports and queries.

Meeting with the store on thursday and I'd like to be able to show them some examples of how they can manage their data better... at the moment I wouldn't disrespect them by showing what I've got so far!

Any help really appreciated

Thanks in advance,

Michael

 

[Phildaburn]

Welcome to the forum, Michael.

Can you please tell us the names of the tables, and describe how they're related to each other so we can better advise you. Also, are you planning this as an inventory system? an order processing system? or a comprehensive, all-in-one system?

Thanks.
Phil...

 

[ArrestedDevelopment]

Thanks for the reply Phil,

While it is an all-in-one database, I must stress it is basic, just so I can give the company a rough idea of what they can do. It is a powertool rental database.

I have four main tables:

Customers, Bookings, Inventory(Tools) and Employees.

The bookings table has many to one relationship with each table; to Customer ID in the customer table, Item ID in the Inventory Table and Employee ID in the Employee ID table (all the primary keys) Primary key in booking table is Booking ID.

I have also created another table "Models" containing "Rental Fee Per Day" which is related to the inventory table.

I have created general forms and queries for each table but, I can't seem to get to the point where the database is useful. The data in the database is all only representative data, so there is no issue in me sending it to someone to look at themselves and make any suggestions.

Regards,

Michael.

 

[SydneyGeek]

The Customers form should have a Bookings subform. So should the Inventory form.
1. Create a Datasheet (or Continuous forms) form using the Bookings table. Call it sfmBookings.
2. In Design view on the Customer form, drag and drop the icon for sfmBookings. IMMEDIATELY click the Properties, go to the Data tab and check that the master and child field properties have Customer ID in them. If not, add the detail but it should be automatic.
You can play with the layout to make it look better, but the two forms are now in sync. Add a new booking, and it will be linked to the customer.
For the Product ID use a combo box.
The Product ID combo should have Product ID and a Product name field, as well as daily hire rate.
In the Properties set the following:
Bound Column -- 1
Column Count -- 3
Column Widths -- 0cm;5cm;0cm

If you have a Hire Rate field in the Bookings you can push the price from the combo with a bit of code.
In the AfterUpdate event of the combo, put something like;

me.[Price] = Me.[Product id].Column(2)

Where Price is the field that you are pushing the value to. Note that the columns statr at 0 so Column(2) is the third column. If you haven't created code for an avent before see the tip here.

Denis