General Question regarding Setup Access Artikel_Db (1 Viewer)

silentwolf

New member
Local time
Today, 04:40
Joined
Jun 12, 2009
Messages
26
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: 28
  • ArticlePrices.JPG
    ArticlePrices.JPG
    52.6 KB · Views: 28

arnelgp

once i caught a fish alive...
Local time
Today, 19:40
Joined
May 7, 2009
Messages
13,267
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

New member
Local time
Today, 04:40
Joined
Jun 12, 2009
Messages
26
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 )
 

Users who are viewing this thread

Top Bottom