How would you set this up?

[cowboy]

I have a customer table that stores the customers information. I want a list of services that I want to offer to be provided. I want my user to be able to input how many hours will be assigned to a type of service. Then I want a report that prints out only the services that hours have been assigned to, and I want the description of the service to be displayed.

I have a CustomerInfo table that is linked to a Bids table by a CustomerID. What are the next steps?



Thank you for any help in advance.

[orange]

I suggest you read this artic le and work through your "info" to get a diagram of the things of interest and how they relate.
http://www.rogersaccesslibrary.com/T...lationship.zip

[cowboy]

Ok, I read the article and it is very good, but I still cant grasp where I am getting stuck. I will explain the situation.

As you can see in the first picture that I have my tables linked, the Bids table has several fields that are designed to hold the number of hours a job will spend on a specific task. I have named the fields so I can recognize which one is which. I want to be able to produce a form that will print out a description of the services that have some time assigned to them.

As you can see in the second picture it is a table with the service descriptions that are very long and a short name.

The problem I am coming up with is figuring out how to associate the services with the hours stored in the Bids table.

In the third picture you can see a query I have put together. This is the best way I have put together to get out what I want, although I feel there is a better way to do it. You can see I have put an SQL expression in 3 of the columns to test my method. The code I am using is as follows:

Expr1: IIf(IsNull([DebrisLawn]),Null,"Small tree limbs, sticks, leaves and debris associated with landscape activity shall be removed from the surface of the lawn.")

This cant be the best way to do this, can you point me in the right direction?

[orange]

Your first issue is your table structure. If you get the tables and relationships established, the other stuff (queries, forms, reports) becomes a lot easier. Your tables are not Normalized to 3NF.
You are dealing with Customers, Services and Bids.
A few questions:

Do you deal with 1 customer at a time?
A customer may have 0 or more phones? Do you really care what type it is? If so, why?
Or is it that a Customer may have 0 or more PhoneNumbers?
A Customer may have 0 or more email addresses?

You could relax true 3NF and for a Customer you could allow fields/attributes for
--AlternatePhoneNumber (only 1 of these per customer)
--AlternateEmailAddress (only 1 of these per customer)

You offer a Number of Services
A Customer may ask you to Bid on Providing 1 or more Services
When you Bid on Services , the Bid is for 1 Customer
the Bid is for 1 or more Services
A Bid conceptually is much like an Order, and can contain 1 or more "LineItems" (Services in your case)

When does a Bid become an Order for work or a Contract?
Does the Customer have any opportunity to modify the "order" (bid) eg remove an item, replace an item etc?
Do you deal with Payments?
You have done a good job identifying your Services. Are there more more Services? Are the Services you have all at the same level of detail?
You don't want your Services to be at different levels eg, a) Build a house, b)install a door, c)oil the hinge
Ideally your services will be at the same level of "detail"/"work involved".

Do you offer any Products?

You are way ahead of yourself in trying to set up expressions.

You might want to write a 4-5 line description of what you specific business is/ will be. You'll be surprised at how helpful that will be to building your tables and relationships, and the interface.

There is an example of all the steps involved in a series of free videos. The theme/subject is Customer, Order, LineItems and Products.
The videos are available here. I recommend that you watch them; bookmark the site and go back and review as you need to.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

[cowboy]

Do you deal with 1 customer at a time?
A customer may have 0 or more phones? Do you really care what type it is? If so, why?
Or is it that a Customer may have 0 or more PhoneNumbers?
A Customer may have 0 or more email addresses?

Yes, 1 customer at a time.
Just an example; husband may have 0, 1 or 2 phones in this situation and a wife may have 0, 1 or 2 phones. The reason the phone type is there is to identify home phone or cell phone to help with contacting. Same concept with the email addresses.

When does a Bid become an Order for work or a Contract?

After they are handed a bid on a sheet of paper and approve the cost.

Does the Customer have any opportunity to modify the "order" (bid) eg remove an item, replace an item etc?

No, the bidder will talk with them about what they want done, come back home and put the amount of anticipated labor hours for each individual task. I do not expect to have additional items.

Do you deal with Payments?

Not with Access.

Are the Services you have all at the same level of detail?

Yes.

Do you offer any Products?

Nope.

I watched all 6 of those videos and I believe the problem I am engaging is a 0 to many relationship, which I have never heard of before today. I understand what it is now, and in the end of the 6th video he goes over the concept, but he does not explain how to store the information. If you could explain that little bit or have another video, I think I am almost there.

[orange]

Are you saying you think your tables are set up for your "business"?

[cowboy]

no, I think if I understood how "0 to 1" or "0 to many" relationships were designed in tables I could figure out the rest.

[orange]

Ok watch this video
http://www.youtube.com/watch?v=lXAGQ8vmhCY

[cowboy]

I already watched that video, I found that while watching the other 6. I just watched it again and it didnt help :-(

[orange]

The subject is Cardinality.
It starts at about 4:00 on this video
http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming



0 to many at 7:15 on http://www.youtube.com/watch?v=q1GaaGHHAqM

http://en.wikipedia.org/wiki/Cardina...data_modeling)

starting at slide 26 here
http://www.google.com/url?sa=t&rct=j...XwxzXD1MZ-N5Qg

[pbaldy]

I'll try to say the same thing in a different way. Having a field for each service in your bids table is not normalized. It should be a related table with fields like BidID, ServiceID, HoursQty. If I wanted 3 services, my bid would have 3 records in that table. When you reported on that, you would automatically only get those 3.

Having them as fields will be more trouble than it's worth in the long run. What happens when you add a new service? In the normalized database, your user adds a service to the services table through the handy form you build for that purpose, and it flows through the application. You never have to touch a thing. In your design, you have to modify the table, queries, forms, reports, etc.

[cowboy]

Bingo, you do it again pbaldy. I was not thinking about a record for each occurrence I was trying to figure out how to put them all together. I knew my set up was not correct or normalized, I just didnt know how to fix it.

If I wanted 3 services, my bid would have 3 records in that table. When you reported on that, you would automatically only get those 3.

was all I needed to hear from the beginning. Sorry to have caused so much trouble with such a simple problem.

[pbaldy]

Happy to help. I think you'll be happier in the long run. Strict normalization would call for the same thing in your customer table.

[cowboy]

Ok, I set up my current relationship to be like the first picture. The second picture is showing how I am currently inputting each service assigned to a bid. It is a continuous subform that has a combo box for selecting the service and a text box storing the labor.

Is there a way to make the input method more like a table / datasheet?

[pbaldy]

How would a table/datasheet be different than that, which looks pretty simple?