Microsoft access help

[Harry_manback]

right im doing some work with access at work and its doing my nut.

im struggling to do something. im going to give and example and if anyone knows how to do it id be very happy.


say if i my first field is called 'MAKE' and lists ford and renault etc etc.
my 2nd field is called 'MODEL' how do i make it so that wheb make = ford only the focus and fiesta etc are available in the MODEL list, and not clio and corrola etc.


cheers.

if you can talk me though it im on msn

helmutvs@hotmail.com


ta.

 

[172.com]

SELECT Model FROM [table-name-here] WHERE Make = 'Ford'.

Your DB needs normalizing, but you'll learn that craft later on.

 

[Harry_manback]

yeh, but that doesnt make the next list show on ford models does it!!!

 

[172.com]

Uh yes.

 

[Harry_manback]

no, i have 2 fields, when make is ford i want to only see ford models in the next drop down menu.

if it says renault in the first i want it to filter out the rest and leave me with clio laguna.

i dont think what you said does that.

 

[172.com]

It does.

 

[Harry_manback]

how the hell does it know which make, makes which cars?

surly itll just be

MAKE----- MODEL
ford----- fiesta
renault----- focus
citroen----- mondeo
pugeot----- clio
----- laguna


hows it gonna know that ford only make the first 2?

 

[172.com]

Woah, woah. The make and model aren't consistent across rows?

You either need to make 'Make' repeat across each row e.g:

Make | Model | Other
Ford | Focus | other fields here
Ford | Fiesta | other fields here
Renault | Clio | ""
Renault | Megane | ""

​

Or have two tables e.g:

'Make' table:
ID | Name
1 | Ford
2 | Renault

'Model' table:
ID | MakeID | Name
1 | 1 | Fiesta
2 | 1 | Focus
3 | 2 | Clio
4 | 2 | Megane.
​

The best method is the two-table (lookup table) method.

The query for the 1st example is the same as I've already posted. The query for the second is:

SELECT Model.Name FROM Model LEFT INNER JOIN Make ON Model.MakeID = Make.ID WHERE Make.Name = 'Ford' (or you could use WHERE Make.ID = 1)

 

[Harry_manback]

what the hells a query, i just have 2 box's

it just lists the same all the time.

 

[Harry_manback]

do i used the query for my form??

 

[Harry_manback]

i have this view, this isnt made any easier by office 2007.

 

[biojo]

firstly wheres the relationship
and

wtf is make id if you was linking though ID`s rarther then make name , u`d need to call it Make ID ect

 

[172.com]

i have this view, this isnt made any easier by office 2007.

Sorry for the delay, just got home from work.

Not used Access in anger in donkey's years. Not sure what you need to click/access to be able to paste the query in but from that view you should be able to do it:

In the top pane under 'Table 1' click on ID and keep you mouse down and drag it onto 'make id' (it's a good idea not to use spaces in table/field names), hopefully this should create a join. If you get any options for the join click on 'right'' or 'all from right' or anything similar. In the bottom pane in the left-most 'Criteria' box (under the table 1 column) enter:

WHERE make = 'ford' or;
WHERE id = [id for ford here]
​

I'm making some assumptions of how Access works here, but hopefully you should find the above useful.

 

[Harry_manback]

firstly wheres the relationship
and

wtf is make id if you was linking though ID`s rarther then make name , u`d need to call it Make ID ect

i honestly have no idea what a query is.

so what a relationship is.... i have no f**king clue.

it seems pretty simple to me, i just aint sure how i make the make on one page corispond to the make on another.

 

[Harry_manback]

is this better

 

[172.com]

No, the ID will be the make number. The first table only needs ID and Make.
The second table needs ID, MakeID and Model. The join (like you have above) needs to go from ID in the first table to MakeID in the second.

 

[Harry_manback]

tell you what
do it
then send me it.


i ant got a clue.

im getting mixed upbetween the relationship and where i add the row source on my field properties.