Multiple item form from query to table

[Jkwb]

I have been using Access for about a month or so and wrote a pretty good program to do quotes and invoices for the company I work for.



Now I am trying to augment my database to have a price list. Because I wanted the price list to be of record and saved, I used a form with a subform. The main form taking the price list header info price list ID, price area, and date and putting into tblPricelist) and using the sub form to table to load the actual price list info (product, price) and putting into tblpricelistsub.

What I am trying to do is to be able to bring the entire price list as it is and be able to update (or not) the price of all or some of the items and then have it save as a new entry into the tables. I am trying to do this without having to use a sub form and lookup every product all over again (500 products).

Is there a way to do a query to bring up the entire price list, turn that into a form, and then be able to update the prices that would in turn create another entry into the price list and pricelistsub tables?

your help is appreciated!!

[June7]

What?? That is all very confusing. If this is a db for quotes and invoices, how were you functioning without a pricelist?

What is tblpricelistsub for?

New entry into what tables (plural?)?

Certainly a query can be the datasource for a form so what do you mean by 'turn that into a form'?

Update prices in what table and again what would be the new entries?

Would you care to provide db for analysis and sharing? Follow instructions at bottom of my post. There was another poster earlier today asking about this type of database.

[Jkwb]

The original database was just for quoting and invoicing. I had pricing within the product table for reference but everything was essentially costed and priced per order.

OK, let me ask this a different way. I would like a price list for 500 different items, for 10 different geographical pricing areas, that can be not only updated, but all price lists previously created are retained. Does that make more sense?

when I put together the original db I had difficulty in putting together a quote of one customer with multiple items. This is when I discovered (somehow) the form and sub form and I was able to save quotes and then save all the lines items to the quotes in a different table and then able to query to print the quote or invoice.

i just attempted the same thing here and if I was willing to physically drop down all 500 items and input a new price it would work. I was thinking that if the form was already populated with all the items and prices for a particular price list and a blank spot to input a new price was there too. And then for it all to save as a new pricelist (and retain past pricelists) and be able to query it for printed price lists.

i really appreciate your help! I know it must sound confusing but maybe this helps.

if you still need this part of db I can send, although right now it is little more than a few tables.

[orange]

The attached came from StackOverflow and may be of some help with your design.

[June7]

Consider this table for Product prices.

ID	Name	Price	Region	Active
1	Hat	15	A	No
2	Hat	20	A	Yes
3	Shoes	30	C	Yes
4	Belt	12	A	Yes
5	Belt	15	B	Yes

Table has all prices ever used for all products. Only Active=Yes prices will be made available for new records. Then the question becomes 'Save the ID or the price into other tables?' If the ID is saved then associated price is retrieved by joining tables in query.

[Jkwb]

That could work...Although i am not sure of the mechanism to update. How would I get entire price sheet to pull upon a form and be able to update them individually without having to lookup each one individually?

[Jkwb]

The attached came from StackOverflow and may be of some help with your design.

Thanks. That is essentially how I did the quoting. The only difference is I put a true/false in the quote for sold and added a couple of fields for invoice number, etc., instead of separate invoice field. For some reason with all the variables I had it got hung up when doing the massive query to print the actual quote.

[orange]

I have seen issues with people storing the Price of an Item in the Product Table. I wrote this is a different post and part of it may be useful to you.

It's much like the Cost of a Product. If you store the cost in the Product table, then how do you change the cost without affecting all previous Sales?? In an Order or Order Detail you can identify the Product and the Cost at the time of the Sale (such a cost could reflect a discount; a preferred Customer price; a clearance Sale; damage Goods mark down...) and would not be associated with Product generally.

[June7]

I guess I just don't understand what you mean by "price sheet to pull upon a form and be able to update". What needs to be updated?

You create a quote by selecting products? Only the active product records would be made available for selection.

[Jkwb]

June7, I appreciate your patience.

i am not talking about the quote, but rather a price list. I'd like the full pricelist to populate a form (prob through a query) with current prices and allow me to update which prices I need to, and then save as a new price list with current date but maintain old with its original date and pricing.

[June7]

If by 'update' you mean add new price records and then set the old ones as Active=No, that is a simple matter of binding a form to Products Price table. Say you want to add new record for 'Hat' because of price change. Filter the form to all Active=Yes 'Hat' records - assuming you have only one type of 'Hat' product this should be one record. Set that record to Active=No and then add the new record for 'Hat' with Active=Yes. This retains all previous 'Hat' records. Now can build a report that shows all Active=Yes prices. Or use the Active=Yes criteria to present a list of products in a combobox on other forms. Could have a field for DateInactive instead of the Yes/No. If the field is null then the record is active. The date field would allow recreating the price list in place at any time in the past.

[orange]

I'm not following the "price list" either. Are you trying to maintain a complete history of Prices for each Product/Item? If so, are you keeping a record for the DatePriceMadeActive and DatePriceMadeNotActive? These are questions, not suggestions. Perhaps a little more detail on WHAT Price list is, its purpose etc would clarify things.

[Jkwb]

Thanks for the replies.

In a nutshell, I have a table of products (tblProd) that I would like to create price listsfor. I created another table (tblPricelists) that links to another (tblPricelistssub). TblPricelist has a field to link the tblPricelistSub, a link to the tblHubs (pricing geography) and date. In the TblPricelistsub I have a link to the tblProd and price.

Right now I can easily create a new price list with the form / sub form relationship but it requires me to pull down 500 different items initially and place the price. With a new price date it stores and I can reprint by date through a query easily.

what I am trying to do is see if there is a way to display the entire pricelist in a form (yes, there is through the query) but then to be able to change individual prices if desired and have it save as another record in both tblPricelists and tblPricelistssub. I want to avoid having to physically drop down each item and update the price or enter the same price so that it all will print out for that date...that would take forever for 10 pricing hubs and 500 items each.

Example I would pull it up for an individual hub

Pricelist for Hub 1

Prod Date Current Price New Price

1 01/01/2013. 23.00. Blank
2. 01/01/2013. 45.00. Blank

so on and so forth. The default number in new price might be current price, allow it to be changed, and then update the entire file with the new date.

does this make any better sense?

[orange]

Can you post a dumbed down version of the database? (no personal/confidential info, and enough records to show issues not the whole database necessarily)

The general message I'm getting is that there are a number of Hubs, and the current Price for a Product can be different at different Hubs. You also want to record a History of Product Prices by Hub.


From experience if you can build a data model that reflects your business, and keep that model current whenever you make changes, you can test that model with any proposed changes. I would encourage you to work with your data model and make it represent what you need. I would not be using forms and subforms before building a model of the situation. However, there are many ways to accomplish things, and I am providing these comments for your consideration. I also realize that many "new developers" with Access tend to jump in with tables and it isn't until they run into a complex query that the word Normalization appears.

It may be time to look at some of the underlying concepts and techniques of database as you restructure your own database. I'm sure it would be an excellent learning opportunity.


Principles of Relational Database (short and very good)
http://forums.aspfree.com/attachment...2&d=1201055452

Entity Relationship Diagramming http://www.rogersaccesslibrary.com/T...lationship.zip

Video tutorials:
If you google for videos "normalization langer" you will find a series of free videos by Dr.Art Langer. These are quite good for learning by Watching/Listening
rather than reading.

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