Solved General Question regarding Setup Access Artikel_Db (1 Viewer)

silentwolf

Active member
Local time
Today, 08:39
Joined
Jun 12, 2009
Messages
545
Hi all,

I am not sure about how I should go about filtering datastructure and setup for easy to use Artikel_Database.

Part of the Db_Structure:(qry_ContGroupDiscount)
  • Each Contact is assosiated to one Pricelist
  • Each Contakt can have serveral Discounts in a specific Article Group (ArtG="Ch","SO","TO" and so on..)
  • So Kont_ID; 1 could have a discount of 8 percent on the group "CH", and 15 percent on group"SO" and so on..
  • Picture is attached on how the relationship for this is build and used for a Query = qry_ContGroupDiscount

(qry_ContactPriceListe
  • Here is the tbl_ArtikelGruppe also included in the query (qry_ContactPriceListe)
  • The other table and those assosiated Fields I guess are just standart with ArtS_Kennzeichen = Article Main Code and so on..
  • And all the references to the other tables
The issue(s)
  • How to set the form up so I be able to
    • Get the price from the contacts "Pricelist" and the for this contact assosiated Price for the article after select contact
    • So Contact_ID-1 orders Article YXZ_152 which is assosiated as Group "CH" and his Pricelist ="Nationale"
  • So Contact_ID = 1 is assosiated to "Pricelist" ="Nationale"
  • This contact likes to order Article=XYZ_152 which is catagoriesed as ArticleGroup="CH"
  • This contact gets on every ArticleGroup Discount of 10 percent of the "Pricelist" ="Nationale"

I hope to have that explained well enough for someone to understand and maybe can give some advice
on how to set up the Queries the best and perhaps how to go about to have a userfriendly form assosiated to this issue.

Currently I got those queries in place and a subform wich is not really working for the Price Calculated after the Pricelist is selected or the GroupDiscount


Many thanks to all and happy Easter )



  • Get only prices from one contact selected in the main form
 

Attachments

  • ArticleGroupDiscount.JPG
    ArticleGroupDiscount.JPG
    33.8 KB · Views: 156
  • ArticlePrices.JPG
    ArticlePrices.JPG
    52.6 KB · Views: 221

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:39
Joined
May 7, 2009
Messages
19,169
best to use form is Single form or Continuous form.
the subform (i think for the product), the product should be made combobox.

select producttable.productcode, producttable.productname, discount.discount from producttable left join discount on producttable.productgroup = disount.productgroup;

so you see the combobox holds 3 columns, including the appropriate discount.
when an item is selected from the combo, you can immediately assign the discount using:

combo.column(2) 'column index is zero based meaning column(0) is actually column 1.
 

silentwolf

Active member
Local time
Today, 08:39
Joined
Jun 12, 2009
Messages
545
Hi anrelgp,

many thanks for your reply!
The problem is tough that there are so many products but I will definitely look into your solution !
Are the relationship so ok as I got it?

Cheers and Happy Easter )
 

silentwolf

Active member
Local time
Today, 08:39
Joined
Jun 12, 2009
Messages
545
Just seen that this thread is still open!

Closing this one as the same a related thread is still open.

Sorry for that!
 

Isaac

Lifelong Learner
Local time
Today, 08:39
Joined
Mar 14, 2017
Messages
8,738
Just seen that this thread is still open!

Closing this one as the same a related thread is still open.

Sorry for that!

I wouldn't worry about it ... I actually prefer the forum style where no conversation is ever "closed", because there is always the potential that someone has something additional, alternative, or in fact better to add - even if years have passed. Quickly closing a question gives off the false impression that the answer which just happened to be liked by the asker (who generally is the least qualified to determine the best answer in the first place), is the best solution - and that often isn't the case......as a hundred "solved" posts on the Excel websites using "Sheet.Activate" will tell you :ROFLMAO:

The best learning I've ever gotten came from skilled people who came along to add "something extra" to a post, long after I'd rushed off to implement the "first & easiest-to-implement thing that seemed to work".
 

Users who are viewing this thread

Top Bottom