Invoicing / Inventory

[spikepl]

Option 1:

You have a subscription for something, you pay monthly. When they change the price, your next bill goes up.

Option 2:

When you get a printed invoice at your supermarket, that's option 2. Even if they change the prices tomorrow, the invoice in you pocket remains the same,

 

[focus12321]

Please note I 'm not asking what "can be true", beciaseu taht is not exclusive. I'm asking how the prices are determined : the entire and complete story.

We bid jobs. Sometimes there is a mark up over cost (which i basically use as a price list).
OTHER Contracts we have, have set pricing on common items.

If a pole cost us $500;

Contract 1: Might be 10% markup. So 500*1.10= 550

Contract 2: Might have a set price 600
(I cant use a 20% mark up because each item may fluctuate.)

 

[focus12321]

Here's a where I basically played back what the poster was saying--- there's a dialog to help the OP clarify his "issue". It isn't the exact issue you are facing, but reading through the exchange may bring out some new ideas. It was along the lines of Customer specific arrangements.

I recall another with custom pricing but I can't find it at the moment. I'll keep looking.

THANK YOU for looking!

 

[focus12321]

This is one of those "My Opinion" things.

I would always store the Invoice Values rather than recreate on the Run. Your accounts must balance and this is the way that allows you to search through the records to find mistakes.

I know that this goes against Normalisation rules but there is a time that one needs to act outside the norm.

Oh I agree, I need invoices to ALWAYS stay the same. I cant make future changes and have it effect already posted invoices.

 

[focus12321]

Here's a link to a thread where I basically played back what the poster was saying--- there's a dialog to help the OP clarify his "issue". It isn't the exact issue you are facing, but reading through the exchange may bring out some new ideas. It was along the lines of Customer specific arrangements.

I recall another with custom pricing but I can't find it at the moment. I'll keep looking.

Oh BOY this is getting close! This is nearly it. ( i think lol). The tblPackageDetail, can I create a rate for each item though? I dont mind having to do a little leg work on the set up of each account. I figure thats a given in my situation.

 

[focus12321]

So you say to the customer: $ 10,000 for this job, and then you sprinkle the markup over the item types ordered until the total fits?

No. A customer will put out a contract to Bid. They'll tell us we anticipate:
100 Electrician hrs
50 Labor hours
50 Widgets
30 XYQ
___% markup on other materials anticipate 10,000 for other materials

We put in our prices next to these items. Along with other bidders. The contracts are sent out by cities/towns/states. These contracts are different, sometimes they have 100 items with set pricing, sometimes no items with set pricing.

 

[spikepl]

It seems to me :

a). an item has a base price (same for all customeres)
b). it can have some fixed price for given customer OR a fixed markup for that customer

So in an order item, the price calculated based on the above would be ListPrice which is then copied over to TenderPrice that can be changed to suit the actiual tender. This is still one orderline/item. Or some variations on this theme.

The prices themselves could e stored as

tblPrices
----------
PriceID
CustomerID
MarkUp
Price
or something along those lines - again depending upon whether you chose option 1 or 2 wrt time variation.

 

[focus12321]

It seems to me :

a). an item has a base price (same for all customeres)
b). it can have some fixed price for given customer OR a fixed markup for that customer

So in an order item, the price calculated based on the above would be ListPrice which is then copied over to TenderPrice that can be changed to suit the actiual tender. This is still one orderline/item. Or some variations on this theme.

The prices themselves could e stored as

tblPrices
----------
PriceID
CustomerID
MarkUp
Price
or something along those lines - again depending upon whether you chose option 1 or 2 wrt time variation.

I'm sorry Im just getting acclimated to access. So are you trying to say use something like:
TblItems
----------
ItemID
ItempriceID
itemdescripttion

TblitemsCUSTOMER1
----------------------------
ItemID
itempricecustomer1

tbleitemsCUSTOMER2
-----------------------
ItemID
itempricecustomer2

Would this work and make all the items still trackable as one item.

 

[spikepl]

No that's not what I'm saying(Hint: if you use enumerations in field names, go and say 5 Hail Marys or some similar penance and read-up on your database normalization - google it). Data grows by adding records not columns! Also, similar items go into same container. You don't have a Monday-wallet, Tuesday-wallet etc?!

tblItems
-----------
ItemID
ItemName
ItemDescription

For each Item and Customer you have one record:

tblPrices
----------
PriceID
ItemID
CustomerID
Price
Markup

(somewhere the baseprice could get mixed into this for easy updates).

 

[focus12321]

No that's not what I'm saying(Hint: if you use enumerations in field names, go and say 5 Hail Marys or some similar penance and read-up on your database normalization - google it). Data grows by adding records not columns! Also, similar items go into same container. You don't have a Monday-wallet, Tuesday-wallet etc?!

tblItems
-----------
ItemID
ItemName
ItemDescription

For each Item and Customer you have one record:

tblPrices
----------
PriceID
ItemID
CustomerID
Price
Markup

(somewhere the baseprice could get mixed into this for easy updates).

:banghead: OHHHhhhh I'm a dumb dumb, something just clicked. For understanding Excel so well I have a long way to go on Access. Thanks for the help. This seems like it should work.... So when I go to lets say an order screen it will go to the tblPrices find the customer and I can pick the item and then it will know the price.

Will it require an entry for each items price for that customer? Say a customer only has 1 item that is a "contract" price and everything else is "list price".

 

[jdraw]

Here is the other post I was trying to find. It's been a while, but it may have some ideas worth investigating. It could require a lot of discipline to maintain.

The OP had a specific issue and we worked through the dialog and his requirements to come up with a solution that worked for his situation. I would not start off recommending this approach, especially where they clear the books each month. There needs to be some sort of auditability to some level just to know how business is from one month to the next, one year to the next, or one customer to another, inventory turnover etc.. But there may still be some ideas in the thread that are worth reading about and considering.

 

[spikepl]

The precise shaping would be determined by the workflows you have. You could maintain a baseprice:

tblBase
---------
BasePriceID
ItemID
Price

and use it to copy into the actual prices for all customers, or maintain a link to them from the table tblPrices, so that unelss soemthing else is done, the price is the base price.. Again all depends on the actual workflow,

 

[focus12321]

The precise shaping would be determined by the workflows you have. You could maintain a baseprice:

tblBase
---------
BasePriceID
ItemID
Price

and use it to copy into the actual prices for all customers, or maintain a link to them from the table tblPrices, so that unelss soemthing else is done, the price is the base price.. Again all depends on the actual workflow,

That could work, (i think). As long as the price maintains the base price until its overwritten, and not just a simple copy paste. How would I set up a link, thats only until overwritten per item??

(thank you for your help, I truly appreciate it, nice to know theres people out there just looking to help others)

 

[Frothingslosh]

:banghead: OHHHhhhh I'm a dumb dumb, something just clicked. For understanding Excel so well I have a long way to go on Access. Thanks for the help. This seems like it should work.... So when I go to lets say an order screen it will go to the tblPrices find the customer and I can pick the item and then it will know the price.

Will it require an entry for each items price for that customer? Say a customer only has 1 item that is a "contract" price and everything else is "list price".

1) For someone who knows Excel but not Access, you're doing fine. That idea is the single largest stumbling block to using Access intelligently.

2) Question: Not to but in, but is it possible for one customer to order items multiple times at different prices over the life of the database? It might be necessary to add an intermediary table, "Projects" or something like that. I bring this up because I can certainly see contract pricing varying year-to-year based on things like scope of work and the like.

 

[spikepl]

I suggested TenderPrice as the final price to be paid. The value of BasePrice could be copied (yes, copied) into that field, or teh value of Priceor BasePrice*Markup, so a price exists until changed manually or by some other process.

Try to set up an example with just a few items and work it to see if that is what is needed. First on paper and then with tables/queries.

Notice that this solution does not have dates on prices, so any new price overwrites old and the entire history is stored alone in the past invoices (which may or may not be a good thing).

 

[spikepl]

@Frothingslosh - so far OP has chosen not to introduce time, but let prices overwrite, so that any price is the current price valid until the next current price

 

[Frothingslosh]

@Frothingslosh - so far OP has chosen not to introduce time, but let prices overwrite, so that any price is the current price valid until the next current price

Fair enough.

 

[focus12321]

1) For someone who knows Excel but not Access, you're doing fine. That idea is the single largest stumbling block to using Access intelligently.

2) Question: Not to but in, but is it possible for one customer to order items multiple times at different prices over the life of the database? It might be necessary to add an intermediary table, "Projects" or something like that. I bring this up because I can certainly see contract pricing varying year-to-year based on things like scope of work and the like.

2 ) Answer: a few contracts do have year1 price, year2 price, year3price. So it may be beneficial to add some type of "project" but not sure how id like to see it implemented. I save old invoices on pdfs and they go in our accounting software so we wouldn't lose track of invoice amounts.

Any suggestions to simplify this?

(Btw this is only my 2nd day with access but I need to get to a point of "proof of concept" pretty quickly, even if its just conceptually)

 

[Frothingslosh]

Honestly, it could be as simple as inserting a contract table between your clients and your invoices. Or it could be hellishly complicated, depending on how you wanted to set it up.

I simply brought it up because that's the kind of thing you REALLY want to consider up front rather than having to shoe-horn it in 3/4 through the project.

First thing is to sit down and write down what you want to do and what to track. Once you have that, the next thing is honestly to just draw out what each table will contain and how they will be related to each other, even if it's just on pen and paper. Everything else builds from that, honestly.

 

[focus12321]

Honestly, it could be as simple as inserting a contract table between your clients and your invoices. Or it could be hellishly complicated, depending on how you wanted to set it up.

I simply brought it up because that's the kind of thing you REALLY want to consider up front rather than having to shoe-horn it in 3/4 through the project.

First thing is to sit down and write down what you want to do and what to track. Once you have that, the next thing is honestly to just draw out what each table will contain and how they will be related to each other, even if it's just on pen and paper. Everything else builds from that, honestly.

If you could only see the pad of paper in front of me. Exactly what Ive been doing. LOL Thanks. I dont mind going in and updating pricing throughout the contract, its only a few of them and only happen a couple times. Id be more worried about keeping the old invoice prices the same.