Getting two fill in percentage fields to update totals in a form

[brookbam]

OK. First it's been a long time (25+ years) since I've played with Access. Second, I think what I want to do might not be good form. Third...I *think* I was a member here back in my previous life when I was building Access databases and asking questions. Different life back then...so here goes.



I'm trying to keep track of stamp collections I'm buying. Here are the major fields

First the rundown of the form and query fields in parenthesis: Keep in mind these companies charge a percentage at a certain point at the totals. Later on there is a new kink in the gears.

InvoiceAmount (Amount of Invoice before taxes and any fees)
BuyerPremiumPercentage (Fillable amount on the form...usually 0, .05, .10, .20...in rare occasions it's other numbers)
BuyerPremiumTotal (Calculation total based on BP: [InvoiceAmount]*[BuyerPremiumPercentage]
Tax
Subtotal (Calculation sub total of SubTotal: [InvoiceAmount]+[Tax]+[BP])
Shipping
Total (the final total of all the above Total: [InvoiceTotalAmount]+[shipping_added]+[Tax]+[BP])

I guess that pretty much explained the query setup too. Those are all in one query called qry_invoices.

That may not be the prettiest thing ever written...but it works fine. But...

Tonite I found a company that charges 5% for credit card purchases.

THAT needs to be a fillable percentage on my form right after the Subtotal and then figure out another Sub_total...unless there is an easier way. Every time I try something, Access kicks back a "Circular reference cause by alias ‘CCpercentage’ in query definition’s SELECT list" error.

Now I know I could just put a total and track it that way but these companies charge my credit card sometime for the total for the sale and then a separate total for shipping. Others charge once for both. I'm also trying to track all the individual fees. That is why I'm trying to break it all down and NOT take the easy way out.

I'd really be thankful for any assistance and I hope I made my problem clear. This has me stumped and at a standstill right now. I used to be able to write SQL and some VisualBasic...not anymore. Hoping to keep this in the query...but I am open...
Mike

[brookbam]

Can a moderator or someone smarter than me delete this post. Dang I'm having issues tonite. At least my attachment is easy to see now.
Mike

[brookbam]

Here is a screenshot of my form. The red box is where I want to insert my credit card percentage charge and then another subtotal then the final total with shipping after that. Like I said...everything works great until I had to have that second fill in the blank credit card percentage block tossed in.

[Micron]

See "How to Attach Files" at the top of the page so you can keep more of your hair
Many/most developers will tell you that it's bad form to store calculations. I'm not sure of what your underlying problem is, thus it's pretty much impossible to suggest anything specific, but I do understand how you can create a circular reference. Don't know what you did to create one other than to say something involved in the calculation becomes updated by the calculation, creating a never ending loop of calculations. To fix that, consider sentence #2 above.

Your db may need a table of charges to identify all types. As you discover a new one, you add a record for the type - you don't add another field to handle it. If that would be your approach, maybe you need to look at db normalization - can't say for sure. Also impossible to say much about query design at this point because it goes hand in hand with how you want to design your forms. One way might be to use a main form for invoice details and totals but have a sub form for charges (totals calculated in main form based on subform records). That would be the most flexible - 2 charges, 2 subform records; 4 charges, 4 subform records. You'd never need to add a query field or a form control or bind that control to a new field. Many ways to skin the proverbial cat.

[brookbam]

Thanks Micron,
I went to do an update to my reply...and then I had two replies...and then I had two attachments except one was huge...and it was all downhill from there. Accessforums.net isn't my first forum rodeo I've attended. But it sure looks like it. LOL.

I'm not sure what I did...but I got the whole thing working. I kind of had a hazy memory that it was bad form to do calculations in a form but this thing might have a couple hundred records in it...maybe 300. Depending on how many stamp collections I buy and how long I live. I'm 63 now and I'm planning to live to 150....so....

And I'm following what you are explaining. I started to go that route (and tried again tonite to totally rebuild it and start over...) but I could not get it figured out in my head how to lay it out. I tried a few times then just cobbled the query together to get going. If it wasn't for the pictures in the auctions to match up with all the buys...I would have just done this in an Excel spreadsheet and been done with it. And yes, the pictures are stored as text files with the pics stored off Access and on the hard drive. I KNEW that was not good form!!!
Thanks for the suggestions!

I'm hoping I won't get another curve ball tossed at me.

I'm open to more suggestions too!
Mike

[ArviLaanemets]

Based on your posts, a possible database structure will be:
tblCompanies: CompanyID, CompanyName, ...;
tblPercentageypes: PercentageTypeID, PercentageTypeName, PercentageTypeSign (PercentageTypeSign can have values 1 for adding or -1 for distraction);
tblCompanyPercentages: CompanyPercentageID, CompanyID, PercentageTypeID, PercentageValue;
tblInvoices: InvoiceID, CompanyID, InvoiceDate, ...;
tblInvoiceRows: InvoiceRowID, InvoiceItemInformation, ItemQuantity, ItemPrice.

The form you are asking about will be a single form displaying selected invoice information, and having unbound controls where totals for InvoiceAmount, for different/summary percentages, for tax totals, and for overall total are calculated (NB! You can have them also as bound controls, and calculate them in source query of form, but then the form will not be editable!).

The invoice form will have contionuous subform(s) based on tblCompanyPercentages, [and on tblInvoiceRows]

[Micron]

This is a bit of a head scratcher:

And yes, the pictures are stored as text files

I think you mean that image file paths are kept in a table and the image files are loaded into an image control as required.

I think in the above the invoice line items table is missing its foreign key (InvoiceIDfk). I would omit the word "row" everywhere as it's redundant to me. I like names to be short. In a query one of the above would be like tblCompanyPercentages.CompanyPercentageID. tblCompPercent.PercentID tells me that PercentID comes from the table of company percentages so some redundancy there too IMO. But that's all about personal choice; not to say any other way is wrong.

[brookbam]

This is a bit of a head scratcher:
I think you mean that image file paths are kept in a table and the image files are loaded into an image control as required.

I think in the above the invoice line items table is missing its foreign key (InvoiceIDfk). I would omit the word "row" everywhere as it's redundant to me. I like names to be short. In a query one of the above would be like tblCompanyPercentages.CompanyPercentageID. tblCompPercent.PercentID tells me that PercentID comes from the table of company percentages so some redundancy there too IMO. But that's all about personal choice; not to say any other way is wrong.

Yes...that is what I meant..."image file path" is in the database and the image files are loaded into an image control as required.

And I know I made the names wayyyy too long. I don't know why I did not shorten them right away because typing those long names in the queries because I forget to copy and paste is faster. I'm going to try to clean some stuff up here now that I have the thing mostly working to...well..it works to my satisfaction...but I still want to clean it up.

I might even try another one laid out like ArviLaanemets posted. I'm kind of following that one.

I'm laid up for ten days until after the first of Sept so I nothing but time to play with this.
Mike

[orange]

brookbam,

'I'm open to more suggestions too!'

For consideration:
Describe to readers in simple, plain English the process you are trying to support with this database.
I recommend you build a data model on paper and some sample scenarios. Get the model designed to support your process(es) before getting to physical database. See "stump the model".

I recognize you have/buy stamp collections. I see Companies and Invoices etc, but I don't see any specific collection or stamps??

Here is a design approach from Barry Williams' old site (no longer active):

Steps in a Top-Down Approach :-

1. Define the Scope as the Area of Interest,(e.g. the HR Department in an organization).
   
2. Define the "Things of Interest",(e.g. Employees), in the Area of Interest.
3. Analyze the Things of Interest and identify the corresponding Tables.
4. Consider cases of 'Inheritance', where there are general Entities and Specific Entities.
   For example, a Customer is a General Entity, and Commercial Customer and Personal Customer would be Specific Entities.
   If you are just starting out, I suggest that you postpone this level of analysis.
5. At this point, you can produce a List of Things of Interest.
6. Establish the relationships between the Tables.
   For example, "A Customer can place many Orders", and "A Product can be purchased many times and appear in many Orders."
7. Determine the characteristics of each Table,(e.g. an Employee has a Date-of-Birth).
8. Identify the Static and Reference Data, such as Country Codes or Customer Types.
9. Obtain a small set of Sample Data,
   e.g. "John Doe is a Maintenance Engineer and was born on 1st. August, 1965 and lives at 22 Woodland Street, New Haven.
   "He is currently assigned to maintenance of the Air-Conditioning and becomes available in 4 weeks time"
10. Review Code or Type Data which is (more or less) constant, which can be classified as Reference Data.
    For example, Currency or Country Codes. Where possible, use standard values, such as ISO Codes.
11. Look for 'has a' relationships. These can become Foreign Keys, or 'Parent-Child' relationships.
12. You need to define a Primary Key for all Tables.
    For Reference Tables, use the 'Code' as the Key, often with only one other field, which is the Description field.
    I recommend that names of Reference Data Tables all start with 'REF_'.
    For all non-Reference Data Tables, I suggest that you simply assign an Auto-increment Integer for each Primary Key.
    This has some benefits, for example, it provides flexibility, and it's really the only choice for a Database supporting a Web Site.
    However, it complicates life for developers, which have to use the natural key to join on, as well as the 'surrogate' key.
    It also makes it possible to postpone a thorough analysis of what the actual Primary Key should be, Which means, of course, that it often never gets done.
13. Confirm the first draft of the Database design against the Sample Data.
14. Review the Business Rules with Users,(if you can find any Users).
15. Obtain from the Users some representative enquiries for the Database,
    e.g. "How many Maintenance Engineers do we have on staff coming available in the next 4 weeks ?"
16. Review the Results of Steps 1) to 9) with appropriate people, such as Users, Managers,
    Development staff, etc. and repeat until the final Database design is reached.
17. Define User Scenarios and step through them with some sample data to check that that Database supports the required functionality.

Good luck.

[brookbam]

brookbam,

'I'm open to more suggestions too!'

For consideration:
Describe to readers in simple, plain English the process you are trying to support with this database.
I recommend you build a data model on paper and some sample scenarios. Get the model designed to support your process(es) before getting to physical database. See "stump the model".

I recognize you have/buy stamp collections. I see Companies and Invoices etc, but I don't see any specific collection or stamps??

Here is a design approach from Barry Williams' old site (no longer active):

Steps in a Top-Down Approach :-
Good luck.

Thanks!
That was a great refresher! You all are actually reminding me of stuff from 25 years ago! I used to make a list of items that I wanted in my database and tried to break them down in the tables I thought they would go in. I would then make a rough sketch with the relationships and how things should kind of work together and go from there. On this one...I thought there really wasn't much there....it's been 25 years since I'd really done anything on databases...and I was bit too impatient to start.

Right now I'm going back through and shortening the names and the data paths for the pics. I currently have that path stuck in C: drive in Users and my username and then Documents..on and on and on..I have my Stamp Collection on a dedicated drive now. I shortened the path by a third. I'm also going through and shortening my field names. Next I am going to sit down and relook at an outline for my database and see where I can improve it a bit more based on what orange posted and all the other contributors posted to my fine thread.

I am really in awe of the advice I am getting here. I did not expect this.

It is not going to waste.
Mike

[orange]

Good stuff Mike!!

[Micron]

You could post a pic of your relationships if you want feedback when you get that far. Responders will have to understand the process that your db will support so expect questions if you do that. Glad to see that you got something of value thus far.

[brookbam]

You could post a pic of your relationships if you want feedback when you get that far. Responders will have to understand the process that your db will support so expect questions if you do that. Glad to see that you got something of value thus far.

Let me see if I get that far.
I'm still working on the name shortening.
Mike

[brookbam]

Attachment 50696

Let me see if I get that far.
I'm still working on the name shortening.
Mike

Huge update!
Sunday night I needed to do another modification and I could see I was working with a trainwreck. Time to start over. From scratch.

Let me clarify a bit of what I am doing. I am not building a database to keep track of my stamp collection per se...I'm keeping track of my stamp collection PURCHASES that I buy on Stamp Auction Network, eBay, HiBid....etc...I just want to have a history of what I paid for any given lot before it gets broken down either into my own collection or resold. I'll be using a program called StampManage (I think that's it...) which is based on Access and it would have taken me 10 years to build something remotely good enough....

Micron, orange, and ArviLaanemets....you were all huge help in the next steps in reminding my of my 25 year ago self what I needed to do.
Sunday night I sat down with a pen and paper and listed my major tables I needed. The trainwreck database was actually a huge help as it gave me a starting point as I already had data entered so I knew what worked where and what needed improvement. It was at this point I assigned primary keys as needed (that helped a lot further along!...I have no idea how I missed that! I just used the autogenerated ID and AutoNumber primary key fields. I also got relationships setup. Again...that helped. I also added a couple of lookup fields in the database to simplify looking up data.

I got all my field names shortend. I was able to bring two tables over pretty much unchanged.

Started Sunday night at 9pm and had a good portion done by midnight and had most of the data brought over. Today I greatly simplified the costs part of it...I took out most of the calculations and now I just ran through adding a complete purchase and I am going to say it probably cut my input time at least by half if not more.

I might be able to simplify this more if I add a menu system to this....but...eh....I don't want to get tooo fancy now...

I attached a screenshot of a form I put together that brings the four major input screens (SaleID, LotID, Company, Images) I use to get all the info in. This does absolutely nothing but shows the finished results. Of course, once the seller info is in there, then I never have to add it again. The sale number at the top links to the subform sale number to the right which also has all the lots to that sale number and then I can scroll through all the pictures assigned to that sale number. IF...and I am sure it will happen...I'll get the same sale number....I'll have to think of something...Maybe I should put the first two or three digits of the seller in front of the sale. So FIS195. I only have seven listings in there. They would be easy to fix.

I can say it is a LOT easier to do a modification to this thing now! Been there done that! I've been wanting to get this thing done for months now.

I can't believe all the advice I got from a complete newbie on what I thought was a question wasn't going to get but one....maybe two responses.

I've attached a PDF of the "PurchasesF" screenshot I was trying to describe above. I smudged out the personal info in the Company area.
Thanks again!
Mike

[Micron]

I can't believe all the advice I got from a complete newbie

Which one of us is the newbie?

I also added a couple of lookup fields in the database

I hope not table lookup fields. Too late to look at your db tonight but your attachment didn't work for me anyway.