There has to be a better way *DELETED*

[Kicker3]

Post deleted by Kicker3

[RalphL]

What you have is a snapshoot of a point in time. You have no history, no details about the customer other than name & phone number.

I think 3 tables are in order: Shed, Customer & join table or line item table that ties the shed to a customer.

[Kicker3]

I'm stumpted. Anyone care to show me how to do what Ralph suggested?

[Himitsu]

Kicker, you first have to really ask yourself what you want it to do, what you could do to make your work easier, ect... Would you want it to be able to print an invoice for your customers? It might be nice to see that... would you like it to keep track of your customers payments each month... What Ralph is suggesting is a layout that is kind of a basis... but if you do it like that, it will leave you with a lot of options in the future. Think of a table as being an area of your solution that is specific to something. Like customers, Sales, inventory, ect... this is how you group it. Everytime you have a new item that has nothing to do with the other items, you should create a new table. But always keep in mind it's relationship. A customers relationship to the product doesn't exist directly, but a customers relationship is to an invoice and an invoice is related to the product. So what ralph is saying is to create a table for your customers, a table for your sheds(you could think of it as an inventory file) then you could make a sales table to sale off the ted time of a shed. Then a join table (sometimes called a lineitems table)to keep track of a specific sale or rent time for that shed. Now depending on how you want to view it,either from the customers angle or the shed angle, they are basically the same. A customers table could be thought of as an inventory table of customers who are going to rent the sheds. The shed table could just contain the 12 sheds, one for each record. That way, you could see all the customers who have rented that shed overtime. And also from the customers table, you could see how many payments, how many sheds, ect... that customer has rented.

First thing is set up your tables. Do you know how to do this? It is wise to name them according to what you will use them for. And, it is a good idea to name the fields in that table related to the table name. So if you had a sales table, and in the table an ID feild, it is good to name it something like "Sales_ID" or "S_ID" and any field in the sales area would have a "S_" infront of it. So when you are working on a layout you can see that all the fields are related to the sales table. Just a little advice to keep it organized.

Create all the tables you think you will need. I would suggest:

customers

invoice

invoice lineItem

shed

shed lineItem

I would use the invoice and invoice lineitems for monthly payments the customers make. And the shed and shed lineitems for the sheds that are available and when they get rented.

Create those and the fields you need for each field. The more specific you are with fields, the better you will be on options in the future. But you can start with the basics. The lineitems are little more tricky. For the shed table, you would want to put all the info about the shed, even size ect... (good point of sale) and the shed lineitem will have almost the same fields but, you want to add the customer ID and name. That will tie that shed use to the customer. We are going to make the shed table like a sales table. You said that they are the same 12 sheds. So, from that table, we will put a portal to the shed lineitems and that way, we can add a customer who is currently using that shed and the start and end dates of his use in the portal. But we need the shed lineitems to keep track of the shed ID as well as the customer ID and name and the date, date renting, start date, end date, ect... Think of it as a kind of contract invoice. You don't need to worry about payments on this table. That is what we will use the invoice table for. The invoice table will be oreinted toward the customer. So the that table's main fields would be customer ID name ect.. and a portal to the invoice lineitem that would keep track of payents. If the customer was never going to have more than one payment a month and never purchase anything like insurance or damage fees, then you could take out the invoice lineitems and just put the fields on the invoice table.

Give it a shot and post up what you make, then we will work on tieing the relationships and the autoenter areas you need to keep it working.

[Kicker3]

Thanks Himitsu,

I'll give it a shot.

[Kicker3]

This is what I came up with but I do have a problem still not workong right. Customers (renters) dont show in sheds layout.

StorageSheds.zip

[Himitsu]

ok, I had a little crack at it. You have to think of what is related from the customer table to the lineItems and from the shed table to the lineitems. Take a look and see what I did. It is a little tricky at first but be sure to look at the lookups and the relationship defenitions ect...

StorageSheds.zip

[Kicker3]

Thanks that will work just fine. Now I 've got some stuying to do.

[RalphL]

I have looked at the file provided by Himitsu. It needs some modifications. The Customer table should not have the fields ShedNumber, ShedFee, RentDue, RentPaid, Overage & Stortage. This information belongs in the Lineitems table where it is. Also the data in the Customer table has one customer listed in 3 records, one record is all that is required. It looks like you are accepting partial payments. It may be necessary to add a payment table. I started to made a sample file and it seemed to me that you need a start date and an end date for the rental period.

[Kicker3]

Yes I have to say its confusing me.

[RalphL]

Can you give us more details on the business and what you want the database to do? No need to build a 50 passenger bus if a motorcyle will do the job.