Setting up form for Multiple options in same field 2

[plantfinder]

I'm trying to figure out how to set up a form (and the corresponding tables & relationships) for my business so that I can create a "Job Order". The service type, date, quantity and other information would be in a subform datasheet view for each service provided. The problem I'm having is trying to figure out how to best deal with the "price" or "quote" field.
I have separate pricing for retail and commercial clients and also want the option of being able to have the flexibility to occasionally come up with a different pricing that is neither of the above. Most of the time however it would be limited to a list of of retail and commercial clients

I am not a programmer and do not know code but have been able to do a lot with macros etc.

Thanks

Mike

 

[jrbarnett]

On your clients table, include a field indicating whether this should have retail or commercial price attached to it (one field, maybe one character - C, R or N), fill in the details and make it mandatory.

Next, you want to create a productprice table which has a primary key of a product and a price type, plus the actual price for that category of business.

eg
ProductID PriceType Price
1 C £100
1 R £150
2 C £1000
2 R £1500

etc

You don't want to keep the "price" in the product table if there's different ones for different categories of people.

This deals with the flexibility for each client and type.

On the job order table, have some code (or a macro if you like) in the new record function that pulls the default price for the product ID where the category is the type for the selected client.

You can't bind this to a formula, because otherwise you couldn't make manual changes - it needs to be read into a variable and assigned to a control which can then be saved to disk with the rest of the record.

If there isn't already one, I would also add some sort of notes field on the order to explain any custom pricing - agreements with the client, bulk discounts etc. This can be just a plain text (255) or memo field.

John

 

[fneily]

Here's a little different view:
First, from the first sentence of your post, tables are ALWAYS created first. Then Normalize them. Forms, reports, queries will then fall into place. You might want to read:
Fundamentals of Relational Database Design

http://r937.com/relational.html\

I'd do this: Assuming all retail customers get the same price. Same for commercials.

tblClients with fields ClientID (primary key), other client info fields

tblMarkup with fields MarkupID, Retail%, Commercial%

tblService with fields ServiceID, other service info fields, BasePrice

tblTransaction with fields TransID, ClientID, ServiceID, MarkupID, DateOfService, Quantity, MarkupUsed, Notes

So you have a single place for a base price of a service. Then the retail and commerical prices would be calculated from a markup percentage. Again kept in a single place.

Now a client can have more than one service and a service can be given to more then one client. A many-to-many relationship. Not allowed in relational databases like Access. So you create an intermediate table.
tblTransaction table is known as a junction table. At minimum it contains the primary keys of tables you're connecting. It also contains any COMMON fields.
The field MarkupUsed can then be empty when you use a common markup from the tblmarkup or it can store any "on the fly" markup. And, from the above post, a note field explaining the reason for the different markup.

Now, connecting these tables in a query, you can get alot of analysis done.

 

[fneily]

Just noticed my tblMarkup in incorrect. tblMarkup should look like MarkupID, Percentage. So, for now it just has:
Ret .10
Com .15

This way, it's a small table that can be changed very easily in the future. Or added to, maybe records for friends, family, etc.