Access 97 - Query about a Query

  Blott 07:08 04 Feb 03
Locked

Using Access 97.

I have two tables named MAIN and CREW

MAIN has 4 fields :- ID Date Service Crew

The Key field is ID

the field Crew looks up its value (name) from the CREW table


CREW has 3 fields :- ID Name Number

The key field is ID


I am trying to construct a query to show the details of ALL the records in MAIN, that is :- Date and Service from table MAIN and Name and Number from table CREW.

There is no problem with any record which has a value in field CREW[Name]. However, it is sometimes necessary that field MAIN[Crew]is left blank, in which case I cannot get it to show up in the query.

I should mention that in the query relationship window I have made a relationship between MAIN[Crew] and CREW [ID].

I would be grateful for any advice on this as it is holding up what otherwise seems a very good and quite simple project.



Regards to all,
Blott

  harristweed 10:23 04 Feb 03

In the query design view, double click on the black line that links the two tables. In the box that opens select include all records from 'main' (it should be option number2)

  Blott 06:49 12 Feb 03

Harristweed,

Many thanks for that. Sorry for delay in reply - I've been laid low by the lurgi for a few days.


Yes - I've tried customising those links in the query, but without results. But I am beginning to wonder if I've tried so many things, most involving changing field names and field characteristics that the whole database is just becomeing completely confused (I don't blame it).

When I can next find eight days in a working week I'll wipe the whole thing and start again.

I'll get back to you when I do that, but meanwhile will leave the thread open, as, seriously, it should not be more than a few days.

Again, your help is very much appreciated. I'm not commercial - the one I'm doing now is for my RNLI lifeboat station, and your previous help on merges was used by youth club.


Best regards,
Blott

  Gingermum 07:17 12 Feb 03

Your design is wrong. I am guessing that you have a one to many relationship between main and crew with crew on the one side. I am guessing that Main contains information about when the crew goes out on a job? In this case you don't need to include the field crew. You should have a foreign field in the Main table which is linked to the primary key of the Crew table. In this case your fields are CREW: CrewID,CrewName, CrewNo (? See comments below). MAIN: MainID, CrewID, MainDate (or whatever type of date it is) Service. (Using ID on its own is not a good name for a field, ditto name, date and number. Be more specific with field names. Main is also not a very informative title for a table.) Having done this you set up the relation ship between Crew[Crewid] and Main{Mainid] using Harristweeds suggestions above.
I am also wondering what the number is in the Crew table. If it is the number of the crew then you already have it in the Crew
ID and strictly speaking isn't necessary unless you have some historical reason or other good reason for keeping it but I certainly wouldn't use it for linking.
I have frequently done lots of Access for volunteer organisations. If you need any help please ask again. I'd be quite happy to send you a sample of what you need - but you'll have to wait a couple of weeks as I am away from home for a week.

This thread is now locked and can not be replied to.

How to get Windows 10 for free | How to install Windows 10: There is still a way to avoid paying…

1995-2015: How technology has changed the world in 20 years

Hands-on with the Star Wars fighting drones you can fly yourself

15 macOS Sierra tips | How to use macOS Sierra: Secret tricks and best new features in Apple's new…