OK, here's the straight skivvy: We can't tell you without knowing your business rules. But I can tell you how to figure this out for yourself.
1. Read up on the topics of NORMALIZATION so you don't commit too many tabular abominations. (It's an easy, common, and pesky mistake that lots of folks make when getting started.)
2. Get a dry-erase board and a box of sticky-note pads. You might already have the board. Sticky-note pads are cheap and you can use the leftovers in your office.
3. On your board, write table names. Using sticky-notes, write some entries that will become individual records in each table. Populate your tables.
4. Draw lines between tables that are related to each other, naming the nature of the relationship. I.e. you mentioned yourself that you would have an Invoice table and a Customers table. There's a candidate for a relation right there.
5. Perform (by hand) a few operations of each type you anticipate. Remember the "old programmer's rule" - if you can't do it on paper, you can't do it in Access. Think about not only the immediate task - storing the data, but also the later tasks - creating reports you want.
Now, as to your tables...
Customer table: Good Idea
Stock table: Good Idea
Warranty table: Probably a good idea if you have only a few possible warranty contracts to select. If every sale has a slightly different warranty, this should be in the Invoice table or the Cars Sold table.
Invoice: Good Idea needing exploration.
An invoice should contain a customer ID and a Stock ID, plus date and other things about the sale. If my guess is right about warranty, you would also enter the warranty-type code number here. But just for shoots and giggles, what if someone buys more than one car? Does your business rule require you to write two invoices? If NO, then this table needs to be divided in two parts - one-to-many relationship, where the customer ID is in the master invoice part and the car being sold is in the invoice detail part.
Cars Sold table: EITHER this is redundant data OR you just named the "many" side of the situation for which INVOICE is the "one" side of the same thing. (In which case there is a relationship between cars sold and the invoice number under which that sale was recorded.)
Now, as to some of your questions:
I would be grateful if someone can let me know if all this can be done in a database. i have managed bits
Yes, it can, for the most part. But some of what you want might not be trivial. My best advice is to pick some element and focus on that. Use old Julius Caesar's methods - Divide and Conquer! Works every time. (Also is part of the Edward Yourdon Top-Down-Program-Design method, which is highly respected.)
Now, I'll probably shock some of my colleagues here, but... your BEST BET is to start with the Form Wizard to build your forms. As you gain experience and courage, you will sneak in using Design mode and customize the heck out of everything. I still do it that way today 'cause the wizard can build a basic form faster than I can. But I can make it prettier. And once I've got the framework, I can make it jump through my own hoops.
Also, if anyone can let me know how best to arrange the relationships, i would be extremely grateful.
Once you have identified the data you will be keeping, examine each table. Where you have something that is guaranteed unique, you can use it as the primary key. For instance, VIN on a car should be unique. But if you don't want to use that, then for every table, include an autonumber field (that's one word, not to be confused with auto number 'cause you're in auto sales.)
Autonumber fields CANNOT be assigned any meaning other than as a unique number that won't ever be duplicated in that table. You CAN use the autonumber as an INVOICE number but be aware that if you ever cancel an invoice, the number sequence doesn't reset to re-use the number you STARTED to use. I.e. gaps will appear in autonumbers that allow you to back out of saving a record. So you CANNOT base business decisions on the consecutive values of an autonumber field. You can only base RELATIONSHIPS on such fields.
Now, how do you arrange them? In the tables where the item is defined fully, the autonumber field becomes the Prime Key for that table. In any other table, all you need is a LONG field to hold that same number as the FOREIGN key. For instance, the Cars Sold table would not repeat data for the car if it is already in the stock table. All you would do is store the Prime Key of the selected Stock table in the Cars Sold table as a pointer that says, in effect, "That's the car I just sold."
3. When car is searched for, use a form to specify search criteria and find matching cars that appear on the form.
Not trivial. Look up the topic "Cascading Combo Boxes" in this forum for some lengthy discussion on how to do this sort of thing. (I'm assuming you will do searchs on multiple fields at once.)
4. Need a form to view any past searches made
This is tricky and requires reference to another aphorism: You can't recall what you don't remember. To remember a search, you must store the data of the search. I would perhaps want to shy away from this one. Access is likely to be fast enough to just REPEAT the search
5. Need to be able to view cars sold - in a form
Or a report, makes no difference. Your "Cars Sold" table - regardless of its parent or child status - is how you would approach the problem. This table tells you the stock ID of every car sold. So you would base a report off this table or a query of this table.
You would need lots of tables JOINed through queries. For instance, an Invoice joins to the Cars Sold table through invoice number. A car sold joins to the Stock table through Stock number. An invoice joins to a customer through a customer number. So read up on JOIN issues (including the queries section of this forum.)
Call it "Cars" - or if several words, don't have spaces in table names ie - "CarDetails"
