Combobox - Controlling fill

[graywolf]

Hi there,

I have just started using Access 2003 in the last couple of months and have a couple of problems i would like some help with please for a project i am working on.

PROBLEM 1.
I have a ComboBox (cboSelect) on my form (frmSelectCompany).
I am populating this combobox with [CompanyName] data from my OrderTable.
The problem is if a company has made more than 1 order that company shows up more than once in my combobox, but i only want 1 entry for any CompanyName in the combobox.
Is there any way of limiting the combobox (something like, only putting CompanyName in combobox if it does not already exist)?

PROBLEM 2.
I would like to grab data from a table, but only if a certain field matches a set value.
I was thinking i need to run a loop looking for the value in the table, then if found get that info, but i have no idea how to do this.

Basically i need the calculation below to run in a loop checking a table to see if [SELL PRICE]=[Price]

=IIf([SELL PRICE]=[Price],IIf([SALE QTY] Mod [Quantity]="0",[SALE QTY]/[Quantity],([SALE QTY]\[Quantity])+(([SALE QTY] Mod [Quantity])*[SELL PRICE])),[SELL PRICE]*[SALE QTY])


Any help with either of these problems would be greatly appreciated.

Thanks
Graham

 

[pbaldy]

For the first:

SELECT DISTINCT CompanyName FROM TableName

Not really sure what you're trying to do for the second. Look in VBA help at DLookup and see if it does what you want.

 

[graywolf]

Not really sure what you're trying to do for the second. Look in VBA help at DLookup and see if it does what you want.

Hi there
Thanks for the help.
The SELECT DISTINCT query worked great to solve my first problem.



As for the second problem (sorry i am having problems explaining this properly)...

I have an [OrderForm] that calculates a total of [Quantity]*[PRICE], this works great.
But...I have certain special prices
For example.
If something costs 59p you get 2 for £1
If something costs 39p you get 3 for £1
etc etc (at the minute there is about 6 of these)

I have a table called [SpecialOffers] that has the following fields
[Price] = the selling price (eg. 59p)
[Quantity] = how many you get for the £1 (eg. 2)

This is the calculation i use on my [OrderForm]s textbox that holds the total:

=IIf([SELL PRICE]=[Price],IIf([SALE QTY] Mod [Quantity]="0",[SALE QTY]/[Quantity],([SALE QTY]\[Quantity])+(([SALE QTY] Mod [Quantity])*[SELL PRICE])),[SELL PRICE]*[SALE QTY])

So...what i am trying to do is:
1. Look at the sell price of an item
2. Check in the [SpecialOffers] table
3. See if [SELL PRICE]=[Price]
4. If so, select the fields ([Price] and [Quantity]) from the [SpecialOffers] table and complete the calculation i showed above.

So if a customer buys 2 items at 59p each, the total should equal £1 instead of £1.18



I hope this clears up my problem a little...
Thanks
Graham

 

[Fernando]

Hi there,
for Problem 1:
From what i see (correct me if I'm wrong) your db is not well normalized. Please take the time to normalize it since it will be a real pain in the future (I've experienced this) to maintain this db.
I see that your Orders table has
OrdersTable
OrderNumber as Number - CompanyName as Text - etc...
1 - Microsoft - etc...
2 - Oracle - etc...
3 - Oracle - etc...
....
The headaches will come if some user writes the company name instead of "Microsoft" writes "Microsotf" then when you use the DISTINCT clause you will have those 2 values.

You should separate the client from you Orders table and make a Company table.

tblCompany
fldCompanyID as PK - fldCompanyName as Text - fldPhone ....
1 - Microsoft - ...
2 - Oracle - ...

tblOrders
fldOrderNum as PK - fldCompanyID as FK - etc...
1 - 1 - etc...
2 - 2 - etc...
3 - 2 - etc...

This will your db easier to mantain and you will just have to use a simple Select statement for all Companies.

 

[Fernando]

For Problem2:
I've never never experienced something like that but looking at how Quick Books works maybe this approach would be better:

You will have to have 3 tables, tblItem, tblSellItem, tblGroupItem

tblItem
fldItemID as PK - fldItem as Text - fldPrice as Currency - ...
1 - Candy - $0.3 - ...
2 - Chocolate - $ 0.45 - ..
So here you will have in fldItem the name your suppliers use for the item, and in fldPrice your real price, etc...

tblSellItem
fldSellItemID as PK - fldSellItem as text - fldPrice as Currency - ...
1 - Candy - $0.39 - ...
2 - Dark Chocolate - $ 0.59 - ..
3 - 3 x Candy - $1 - ...
4 - 2 x Dark Chocolate - $ 1 - ..
5 - 2 x Dark Chocolate + 2 x Candy - $ 1.4 - ..

Here in fldSellItem you should fill the name you want the customer to read in the receipt and in fldPrice the price you want to show

tblGroupItem
fldGroupItemID as PK, fldSellItemID as FK, fldItemID as FK, fldQuantity as Number
1 - 1 - 1 - 1 'A Group of 1 Candy
2 - 2 - 2 - 1 'A Group of 1 Chocolate
3 - 3 - 1 - 3 'A Group of 3 Candy
4 - 4 - 2 - 2 'A Group of 2 Chocolate
5 - 5 - 1 - 2
6 - 5 - 2 - 2 'Both A Group of 2 Candy and 2 Chocolate

This table is to know that when you are selling for example fldSellItemID = 4 you are actually selling 2 Chocolates or to say it better 2(fldItemID = 2) for your inventory purposes.