Help needed involving updating records

[adam.grendell]

Hello all,

It's been quite some time since I worked with Access, and unfortunately, it's not exactly like riding a bike. I've forgotten a whole bunch of stuff.

I am wanting to create a db for invoices and price quotes for the business that I work at. We currently use some dinosaur of a program that looks like it was made for Win98. The issue is that there is NO SECURITY, and my boss is a tightwad, so if I want this, I have to do it myself. No budget for me .

I was trying to wrap my head around the design, and I figured I would need a few different tables, one of them being the inventory, or items that would be listed on said invoices. My question is:

If I wanted to change the price of one of the items for a certain customer, how would I be able to do that without updating the price for all the other existing records that have that particular item?

I'll be more than happy to give you more information if needed.

Thanks a ton in advance,

Adam

 

[GinaWhipp]

Well, we're getting ahead of oursleves. We first need to get those tables up and running. This should get you started...
http://www.access-diva.com/dm12.html

 

[adam.grendell]

Thank you for the speedy reply!

Well, as for the website you listed, that is a very detailed system of tables, which is WAAAAY more than I would need. Overall I'm only going to need 5 tables max. 6 if I create a login splash form.

But using the example listed, let's say I opened up the tblInventory object, and I made an item. If I have (for example) a vacuum cleaner for sale at 99.99, and it stays that way for a year, but the price increases. If I change the price of that item to let's say 129.99, will it retroactively change the price on all invoices that have that listed item? Or is there a way to make it so that the price is 129.99 on all FUTURE invoices that are created?

 

[GinaWhipp]

No, you do not want to do that. This is the one time we break the rules of normalization and store the Price in the Purchase Orders Detail table. Then the Price charged never changes but the price going forward is whatever it's set at in tblInventory.

As for the example, you can delete as many tables as you want, however, before you say *I only need 5 tables* you need to make sure you're storing the data correctly. Even for a simple To Do List database you could end up needing more than 5 tables.

 

[adam.grendell]

Well, I'm currently working on getting everything the way that I want it. I think my biggest holdup was all the different relationships on the example you showed me. While I do think that I need more than a few tables, I don't need quite as many different fields OR tables. So instead of hacking and slashing away at the example you gave me, I'm going to design my own in a format similar to that one, but simplified enough that it works the way I need it to. I'll give you a reply or a message when I get it done letting you know if I have an issue, or if it works like a charm. Thanks again for your help and advice!

 

[GinaWhipp]

Just remember two things...

1. You are creating the Bucket not the water
2. The Data Model has nothing to do with the way the data is displayed only with the way it is stored.