Tables :: How To Design Price List Table (Lookup)

Mar 25, 2013

I have to design a price list table which is currently done in Excel.

The table looks like this :

[Headers]

Product Group | Layers | (this are quantity bands) 0-100 | 101-500 | 501-1000| etc
Test1 | 2 | £10(normal) £9(special)

The quantity band currently is fixed to 5 bands but would need to be flexible. There are also 2 prices for each of the quantity band (normal/special)

At the moment my table design looks like this:

ID
fkSupplierID
fkProductGroupID
txtLayers (value list)
intMinQty
intMaxQty
curNormalPrice
curSpecialPrice

This works quite well with the query to return price based on product group, layer and order qty. However I am not very sure if this is the best way to design this. I am just thinking about maintenance - for example when the supplier puts in a price change or when the quantity band changes. The current format (quantity band) is based on a major supplier but in the future we would like to adopt this for any supplier.

View Replies


ADVERTISEMENT

Tables :: Lookup Wizard In Table Design

Aug 28, 2013

I've designed a database at work to collate information about locations around the world that are contaminated by conflict and military activities. I'm struggling with the use of the lookup wizard to populate some fields in one table from another table.The database is ultimately meant to be used to identify contaminated sites in various countries and also to be a source of data for an online interactive map. As such, it needs to hold a fair amount of information. I figured that it would be normal that some fields and tables would be connected to one another. So, for example:

Country table is looked up by the conflict table to provide the names of countries participating in a conflict. To do so I used the lookup wizard. Similarly, the Site information table is looked up by the Site contamination event table to provide the names of contaminated sites. The Site contamination event table. Then the Site assessment table looks up the Site contamination event table to provide the name of contaminated sites. The relationship between these three table is intended so that at any given site multiple instances of contamination and their subsequent assessment can be recorded. This is where I started noticing problems. When I tried to input some fields into the Site assessment table, specifically the 'Site name', it would only display the primary and foreign keys in the drop down menu but not the 'Site name'.

- Have I messed up by relying on the lookup wizard in my table design? This seems to be the consensus in this and most forums (I checked another thread in the 'Tables' forum here). That said, I've seen some people making a distinction between using 'Lookup tables' and 'Lookup fields within a table', but given my relative newbyness I'm struggling to see the difference!

- If the answer is yes then what approach should I take to achieve the same aim (having multiple tables that feed information to one another). Initial research seems to suggest putting lookup/combo boxes in my forms. I'm dabbling with that at the minute but so far the results haven't been as desired.

I've attached a zip file with a screenshot of my relationships diagram to give an extra idea. Note that the relationship between the 'Site assessment' table and 'Site contamination event' table isn't showing up as I removed while trying to troubleshoot, but it is supposed to be one-to-many from 'Site contamination event' to 'Site assessment'.

View 14 Replies View Related

Tables For Price List?

Jan 22, 2005

Hi all,

I started a new thread, because it is a new subject even though is related to my Price List thread, I hope is OK.

If someone can please take a look at my attached Excel Price List (particulary the factors sheet) and give me an opinion of how my tables should be created.

I cleaned the Price List was too big to attach and it will be easier to be understood, in its entirety is kind of all over the place, exactly why I need to make simpler in Access for another user to update if I am not around.

I am below including what I think the tables should include, but not sure exactly how they should be, please be reminded that I am un unexperienced newbie.
----------------------------------------------------------------------

tblSuppliers

SuppliersID
SupplierName
SupplierCode

tblMarkup

MarkupID
MarkupName
MarkupAmount


tblTaxes

TaxID ------Do I need This?
TaxName
TaxAmount

tblExtras

ExtrasID
ExtrasName
ExtrasPrice
SupplierID

tblDiscounts

DiscountID
DiscountName
DiscountAmount
SupplierID
----------------------------------------------------------------------

Thanks for any help,
Emilio

View 6 Replies View Related

Price Lookup

Oct 13, 2007

I am creating a db that handles orders.

My problem is that the products being sold are so at different prices depending on the customer. (e.g I am selling 100 burgers to Mcdonalds for £50 whereas to Burger King I am selling them for £35).

Therefore I have split my 'Products' table into two. A 'Products' table which contains the product name and category. And a second 'Prices' table which contains the Product name, Customer and the price.

I have setup a relationship between the two tables based on product name

How to I go about looking up the different prices depending on which customer I have placed an order for?

can it be done in a table or do I need to create a query to sort it all out.

View 8 Replies View Related

Lookup Table - How To Make More Than 1 Field From Query Appear In The List

Dec 1, 2014

In Access, when using Look Up function, Table/Query, how do I make more than 1 field from the query appear in the drop down list?I will be doing the same thing for students and some other tables. But for this example I am only wanting to show instructors first and last name and their ID number in the drop down.

I am using

Code:
SELECT instructors.first, instructors.last, instructors.ID FROM instructors;
in my Classes table.

What am I doing wrong?

I would like for it to show their instructor first and last name but actually put the ID in the field as I will be using that key for various relationship based functions. Show the first and last name to the person who will be using the database, but actually put the ID in the field so I can use it as a common field across other tables.

View 1 Replies View Related

How To Create A Price List

Jan 18, 2005

Hi all,

Before I start, please know that I am what you call a newbie to a certain extent even though I created in the last 3 years a very complex and efficient database for my business.

This is going to be hard to explain, but I'll try.

The time came to stop creating the Price List in Excel and copy and paste in Access.(mainly to make it easy for other person to maintain and understand)

I know more or less the tables that will need to be created.

My problem is how to store calculated fields in a table (I know I really shouldn't, but how else can I accomplish this), since all the costs and selling prices are the result of complex formulas.

If this information is not enough to understand my question please let me know.

Thank you for any help.
Emilio

View 10 Replies View Related

General :: Lookup List In Table From Query In Split Database

Sep 14, 2012

i have developed an application in access 2010 . and split into front and back end . now i want to add more tables in back end and i need to define lookup list in table definition from the query presently in front end . when i get into lookup list and query builder doesnt show front end content ... how to solve this problem ?

View 2 Replies View Related

Tables :: Joined Two Tables - Unable To Filter / Lookup On Second Table?

Feb 18, 2013

I have an Access Table with about 28,000 Automobile dealerships across the country shown. I've joined a new/small phone contact table to this to keep up with our phone contacts with the dealership and followup efforts. When I search/filter on the dealership table all is fine. However when I search/filter on the phone contact table with a few test entries, I get nothing at all. I supposed that after joining the tables, I'd be able to do a search on the field named follow up date and find/filter today's date or other dates and locate which dealerships to contact when the correct date arrives. But nothing.

View 14 Replies View Related

Tables :: Two Fields In A Table / Link To Same Lookup Table

Mar 14, 2013

I have a table "Product" and in this table I have two fields "StoragePlaceID1" and "StoragePlaceID2". Both these fields link to the look-up table "StoragePlace"

*Attached Image "Product_StoragePlace" from the Access Relationship Window"*

When I want to add a new Product from my inter face i get the error you can see in the Attached Image "Save_Error".I think the problem is that the Relationship is defined as One-to-Many,there a way to define the relationship as Zero-to-Many.

View 5 Replies View Related

Tables :: DLookup Between Two Tables - Calculate Product Price?

Jul 30, 2014

I'm trying to do a lookup between two tables, whereas table A includes the product ID and table B includes the price. Is it possible to create a field in table A with a DLookup function on the product ID to get the product price from table B?

Whenever I try to type in the DLookup function, it does not calculate.

View 2 Replies View Related

Tables :: Populate Lookup Table With Data?

Nov 22, 2013

I have problems understanding lookup tables, especially how to populate them with data. I'm working on an exams database, and have many such tables...

Table for Students
Table for Subjects
and a lookuptable for studentsubjects

Table for Classes 1, 2, 3, 4
Table for Subjects Eng, Math, Geo, Chem... 11 subjects in all
and a lookuptable SubjectClasses

Having inserted data for all subjects and all classes, should I then go ahead and Insert data for SubjectClasses? There will be 44 items!

Even more appalling, studentsubjects table will have (no of students * 11) for my small school, it will be 1,760

View 8 Replies View Related

Tables :: Add To A Table With Lookup Does Not Find Item

May 16, 2014

I have several tables which have an indexed, no dup field. When inputting a entry that is not in the referenced table, how can I be taken directly to the input form for that field.

View 1 Replies View Related

Tables :: Sorting Table Field With Lookup

Aug 12, 2015

I have created a lookup in a field in a table:

select id, bike from tblbikes.
Column count 2
Width 0,3

When I try to sort the table by bike:

I receive the following error: Type mismatch in expression.

Is there some way to sort a field with a lookup.

View 3 Replies View Related

Tables :: Lookup Table Based On Relationship

Nov 25, 2013

I attached a screenshot with notes that describes my problem.

View 2 Replies View Related

Tables :: Lookup Value Lists In Table Fields?

May 16, 2013

I know experienced DB developers say never to use a lookup in a table for a foreign key and instead to use it on the form level. For this reason, I am going through my tables and removing lookups from the table level now.

But how do y'all feel about lookup value lists? (so the list is typed-in instead of looking up a table value)

If you think they shouldn't be used then what should I do instead? Should I make a table for the handful of values and link with a FK field?

Or is there a better way? I would rather not have to make a million tables for these short, stable value lists.

View 2 Replies View Related

Tables :: Updating A Table In Design View?

Jun 5, 2013

I have created a database table with 100+ fields with data. I now need to insert an additional 33 fields that will have a static default value between 1 and 33. I have already inserted the Line # field in the table between every 6 fields and gave it a default value. I now would like the existing database to update and reflect the new changes that were made for the new inserted fields.

View 1 Replies View Related

Tables :: Correct Database / Table Design

Jan 15, 2014

I am still new at database design, and cant quite come to terms with my project and access way of doing things.

I have to keep a register of people who participate in projects. The projects can be of two different kinds. BUT (here comes the tricky part) The projects are being evaluated on three different indicators, with each one of these having 4 measurements, in the range of 4-0. That was a quick introduction. Now let me break it down in parts.

The people:

I have made a Uniqe identifier (Social Security number (PK))

First Name
Last Name
Department (This can be 4 different departsment) made a drop-down menu type.

The Projects:

Unique identifier (Project ID (PK))
Social Security number
Project Type
Start date
End date

Project type:

Unique identifier (TypeID (PK)
Project type (Cti / Regular)

How might i design this the best way, so i can combine the people with the projects there on. And which type.

There can only be one person, but he can be on many projects. These projects can vary in type. My problem is ensuring there connected proberly.

Furthermore, once the basic design is made i need to make evaluations based on their performance if they are on the projecttype "Cti".

here i need 3 x this:

Evaluations:

Objective (range 0-4)
Baseline reading (range 0-4)
Midway reading (range 0-4)
End reading (0-4)
Success = Yes/NO (here i will do a End reading <= Objective formula).

That was a rather long list, but i have sat working on this in three whole days, and im getting a little fed up with not knowing up-and-down.

View 3 Replies View Related

Tables :: Possible To Automatically Lookup Data In A Table Without A Query?

Jun 18, 2013

A field in a table can be populated by a lookup up but it has to be done manually or with a form.A "new" table can be created with a query that matches the data.

Is it possible to skip these steps and create a field that automatically populates with the data from another table based on other common data?I can do this in Excel but not sure it can be done in Access.

View 1 Replies View Related

Tables :: Lookup Queries For Data Entry - One Table To Another

Oct 8, 2014

I am creating a table for data entry. Three fields in the table are going to be Firstname, Lastname, and Address. I want the choices for data entry to be read from a master table which contains first and last names and town of residence.

That being said I would like the choice of Lastname to be all last names from the master table, and the choices of Firstname to be those from the master table but are limited to having the Lastname as entered in the previous field, finally I want the Address field to be limited to those records which match the lastname and firstname. I have been playing around with lookup queries for each of the fields to no avail.

View 1 Replies View Related

Tables :: How To Switch Lookup Table To New One And Preserve Old Data

Oct 17, 2013

We have a lookup table that has a list of CLIN numbers and their costs. The contract that governs those CLIN numbers and costs will be changing to entirely new numbers. Unfortunately, I still need to have the old and new CLIN numbers linked to the other tables.

Will I need to merge all the CLIN numbers into one lookup table, or can I do it from two lookup tables?

View 5 Replies View Related

Tables :: Requery Combobox Lookup On Table Datasheet

Feb 20, 2014

When designing a table I've created a field and set its lookup properties to display a combobox with a row source that returns a DISTINCT set of values already entered into the field.

After a row insert or row update the combobox needs to be required to ensure its list is complete.

If I create a form to display my datasheet this is easy. But I'd prefer to enter data directly into the table datasheet directly. I need to enter simple data into about 20 different tables and I'd prefer not to create 20 forms unless it's really necessary.

The lookup wizard generated entries similar to those I'd previously created manually, except the wizard generated a couple of extra settings that appeared briefly that were not part of the regular set of lookup tab properties. These additional settings referred to 'update propagation'. Once they'd disappeared I couldn't see any way to get them back..

(I am using Access 2013)

View 3 Replies View Related

LookUp Tables For Dynamic Set Of Attributes: Set A Pointer Or Use A Generic LookUp?

Jul 24, 2007

I'm wrestling with the issues; in other threads, it became apparent that because I could not know ahead of time what I will need to know about a given entity, I will use a table to enumerate attributes that is applicable for a given entity.

However, the stumper is that what if an attribute should conform to a set list of values? Since they are dynamic, I would have problem predicting what I will need to be able to lookup, and am even don't know whether I will need a one-many lookup or many-many lookup.

I thought that generic lookup table with a table listing "classes" of lookup would allow me to have one big generic lookup table while using "classes" to act like virtual tables so I can then set the query to appropriate "class" to return just right set of values.

But as I thought about it, I ran into some issues which is pulling me toward the crazy idea that I should have freestanding tables, and use a field in tblAttribute to give me the table's name so I'd know which free-standing table it points to, and have the necessary key to lookup the values within that table.

Even though my gut instincts tell me that I shouldn't be going against the conventions of database design (who the frick goes around creating free-standing lookups?!?), I'm simply not sure how I can use a generic lookup table to hold all information.

For example, suppose I was given a list of values that has its own categories. Since the former design allows only for two level (lookup and lookupclass), where am I to insert that extra level?

Furthermore, I found myself needing a set of virtual keys to reference a certain "class" of lookups for report purposes. That means I need an extra field in my lookup table than I originally anticipates. What if I find myself needing one more field that just won't fit the generic lookup table?

So does anyone have suggestions on how we would create a placeholder for a lookup table that will be made just in time?

View 4 Replies View Related

Queries :: Item Without Price Or Duplicate Price

Jul 29, 2013

I have a table called Books, in that table there is 4 columns ChapterName, Auther, ITEM, Price.

Each book has a item number, and each book has a few records with the same data, just the first column is different where its the ChapterName, each book has a price, but only once, meaning in the first record of each book it will be a price in the column price

Now I want a Query where i can get which book dont have a price at all, and which book has more than once a price, how can i do that?

View 1 Replies View Related

Tables :: Select Table Lookup And Auto Fill In Another Field

Apr 10, 2014

i have a user permission table.that consists of PermissionPK, UserFK, CompanyFK. I also want the username to be automatically filled in?So when a user ID is filled in on the table, it also fills in what that UserID's Username should be?As i need both the UserId and Username text for code that looks at the Environ username.

View 2 Replies View Related

Tables :: Split Database - Table Field To Lookup Values From A Query?

Jul 6, 2015

I have a split database and need a field (Combo type) in the table to lookup values from a query in the front end. How do I do this as it doesn't see the querys because the front and back end are split?

View 3 Replies View Related

Tables :: Converting Excel Table For Lookup Of Values Based On Row And Column Headers

Sep 3, 2014

how data is best structured in Access.I have a table of values (for instance: weight) and I need to be able to look up a weight based on the column header (age) and row header (height).How is this sort of data best structured and accessed in Access?

View 12 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved