Access 97 - Query about a Query

  Blott 07:08 04 Feb 2003

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,

  harristweed 10:23 04 Feb 2003

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 2003


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,

  Gingermum 07:17 12 Feb 2003

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.

Elsewhere on IDG sites

iMac Pro review

Illustrator Charles Williams on how to create magazines and book covers

iMac Pro review

Les meilleures prises CPL (2018)