Help with School Project...

[MattD23]

Hi,

I am responsible for creating a database for a school project, and am having some problems relating the two tables of the database together. Please keep in mind that I have no prior Access experience, and know very little about the program. Here is my situation, any help would be greatly appreciated:

Taking the perspective of a product vendor in a store (i.e. selling soda to a grocery store), I am trying to create a database which allows me to run a query for authorized products in certain stores. I have a products table which has all available products the company sells, and then I have a Stores Table for the different stores which carry the company's product. I want to know how to set up and join each table so that if a certain product is not authorized in a certain store, it will not show up in a query.

Again sorry for the shallow description, any help would be greatly appreciated.

Thanks,

 

[ajetrumpet]

I want to know how to set up and join each table so that if a certain product is not authorized in a certain store, it will not show up in a query.

I would set a one-to-many relationship between them first. Product (prod. table) = ONE, Product (stores table) = MANY

To get the query done, use the grid, it will be helpful. Your criteria for such a need is something like...

WHERE ["Authorized"] = No (or, for a checkbox field, 0)

 

[rsmonkey]

Something like this? look it the query design view and see how its been created. If you would like to filter by store you can simply put the store name into the 'Criteria' section in the query view of the appropriate column. Hope this helps...

 

[MattD23]

I am doing the project on my work (Pepsi Bottling Group). I have included an attachment of how far along I am. Currently I only have the two tables, and neither is related. From what I understand, I need to add columns to the product table, to authorize each product with each store. Any help with setting this up would be greatly appreciated.

 

[ajetrumpet]

Matt,

You can do this by adding columns to either one of your tables. You have 156 different products listed, but in all reality there is only like 20 or so products, all with 4 or 5 different sizes possible. I would eliminate the "package" field from the products table and put it in a separate table of it's own. People sometimes do this to lookup values in the field. This is called a lookup table. The products table would then only consist of the product name and ID, if you want one. The store table too, might only consist of the store name and store ID, if applicable. You said you wanted to list the store's in which the products are authorized. To go about doing this, I would say if depends on how many stores you have.

You need a way to identify this in the tables, so maybe you can create new fields in the "stores" table as "yes/no" fields for each product as each field name. That way, the checkbox will indicate weather or not that product is authorized at that store. Conversely, you can do it the other way around too, and assign the different store names to fields in your "products" table the same way, but I don't think that is a good idea, because a customer base is much more unstable and subject to growth a lot more than a product base.

Another alternative too would be to create a separate table altogether and list the stores and all the products and weather or not they are authorized at each one. To join something like this, I would guess a "one-to-one" relationship would be needed between this table and the store table.

Just some suggestions for you...

 

[MattD23]

ajetrumpet,

Thanks for all your help thus far, it is truly appreciated. I took your advice and added the names of the stores to the product table and used a check box to identify whether or not a particular product is associated with a particular store. By doing this however, I don't really see a need in the stores table, as the store names are already part of the product table. I need to have at least 2 related tables somehow. Also, when I try to run a query, I can run a query to find which products are authorized in a particular store, but can't get the package size to show up in the query. Here is how I am typing it in SQL view:

SELECT [Flavor, Package]
FROM [Product]
WHERE [Food 4 Less] = -1;

When I try to run the query, it asks to enter the parameter value for package.

I have included an attachment, maybe you could look it over and tell me if I inserted something wrong?

Thanks a bunch,

Matt

 

[ajetrumpet]

SELECT [Flavor, Package]
FROM [Product]
WHERE [Food 4 Less] = -1;

When I try to run the query, it asks to enter the parameter value for package.

If the above SQL is exactly the way you wrote it in the query, that is not the correct way to write the statement. Access SQL SELECT statements are written like...

SELECT table.field, table.field, etc...

This is just a "flavor", if you will, of SQL writing. If you want SQL in Oracle, for example, you write this (I think)...

SELECT field, field, etc... (no "[b]table.[/b]" needed)

But then again, Oracle is supposed to be the best... Your statement is right, just written incorrectly. When you change it, you should see all authorized products at the "Food 4 Less" store.

I don't think there is anything wrong with the setup that you have now. There is only one thing I question....

** The actual products are the drinks, not the combination of the drinks and sizes. This is why I suggested initially that you put the sizes somewhere else and look them up when needed, but it obviously doesn't have to be that way. I am just naturally thinking about the expansion of this, if that were to happen.

Also, you should have a separate table for store data, with either the store name as the Primary Key, or to assign an autonumber. You should have this just in case there would ever be a need to add information relating to a store (address, phone, etc...) Doing this is part of normalizing the data and making full use of the program's "relational" type design (this is just for future reference).

 

[MattD23]

ajetrumpet,

Sorry for bugging you again but now I need to join the TSM table (Sales Managers) with the Stores table so that I can run a query listing all the stores under a certain TSM (by name). I have linked the two tables in the relationships window, but have no idea how to run a query which takes the stores (from the stores table) and links them with the name of the TSM (in the TSM table). Any help would be greatly appreciated.

Thanks again for all your help

Matt

 

[dropshipdvd]

dropship-dvd

Hi,

I am responsible for creating a database for a school project, and am having some problems relating the two tables of the database together. Please keep in mind that I have no prior Access experience, and know very little about the program. Here is my situation, any help would be greatly appreciated:

Taking the perspective of a product vendor in a store (i.e. selling soda to a grocery store), I am trying to create a database which allows me to run a query for authorized products in certain stores. I have a products table which has all available products the company sells, and then I have a Stores Table for the different stores which carry the company's product. I want to know how to set up and join each table so that if a certain product is not authorized in a certain store, it will not show up in a query.

Again sorry for the shallow description, any help would be greatly appreciated.

Thanks,

www hw925shop com are the biggest DVD,VCD,MOVIE,TV SHOW,etc wholesaler in China, hw925shop offer WHOLESALE & DROPSHIP DVD MOVIE BOX SETS all over the worldwide. All of items are high quality and competitive price.
Hw925shop have the professional dropship DVDS service? The dropship service is a no investment,no inventory,no risks,We package your orders and ship them to your customers directly through EMS express with online tracking available.No minimum order.

Product list:West Wing,Veronica Mars,The Lord of the Rings Trilogy,Golden Girls,Dead Zone,Apprentice,Star Trek Enterprise,Star Trek Deep Space Nine,SIX FEET UNDER,Roswell,OZ,NYPD Blue,Nip Tuck,MASH,Married with Children,Grey's Anatomy,Frasier,Everybody Loves Raymond,ER,Desperate Housewives,Deadwood,CSI MIAMI,Charmed,Band of Brothers,friends,Simpsons,Star Wars,Sopranos,CSI,24 HOURS,South Park, THE L WORD,star gate,007,
Baby Einstein,Monk,seinfeld,The Shield,OC,babylon,Queer as folk,Will And Grace,profiler,Alias,ANGEL,STAR TREK VOYAGER,
Star Trek The Next Generation,SEX AND THE CITY,x-Files,Ally Mcbeal,Xena Warrior Princess,STAR WARS,Star Trek The Original Series,Smallville,PROJECT RUNWAY,Medium,FAMILY GUY,Disney 100 Years of Movies,Buffy the Vampire Slayer,Black Adder,Barbie,Baby Einstein Language Nursery,Addested Development,Without A Trace, etc.

All DVDs are officially released Asian version with best quality.
Brand new and factory sealed, come with original English audio
Region free, NTSC/PAL compatible, will play on all region code DVD players.
(All of DVDS are US Version DVDs without Chinese writing on the cover to help you selling easier on ebay )
Please visit our website: www hw925shop com
Email: hw925shop@163.com
MSN: hw925shop@hotmail.com

 

[ajetrumpet]

Matt,

your setup is perfect! All you need to do is utilize the query grid. Access does this sort of thing for you if you have the relationship already set up, which you have.

For general reference, if you have a proper one-to-many setup between two tables, you can query ANY fields from those tables and Access will automatically list records that contain the joined fields. No data will be lost, it will simply be "melded" together to form new records. It doesn't matter what field is joining the two tables, as long as it makes sense (the FK contains one or more records pertaining to its parent PK value). I did the query, and put a parameter value in. All this was done simply by dragging the 5 fields I needed from the tables to the columns in the query grid.

I think you have a good handle on this, you're just scared to use your knowledge...what do you think?

 

[MattD23]

ajetrumpet,

That's exactly what I was trying to do! Thanks a bunch for all your help throughout the last few weeks, it truly is appreciated.

Matt