Want query not to require data in fields.

[KayCee]

I am using Access 2000

I have three tables I am using to create a query that will be used for a data entry form. Table one - Client List, Table two - Services, Table three - Due Dates.

I want to create the form with room for up to 12 services to be entered on the same form, so I created the Services table with fields for 12 services including fields for other information that would go along with each of the services (promise date, due date, ext date) Each of the service fields and corresponding fields are numbered 1 - 12. The Service fields are look-up fields that are linked to the Due Dates table The due dates table only has two fields - it lists each of the different services and the due date for that service if there is one.

The trouble I am having is that unless a value is entered in all 12 service fields the record will not show in the query, even though I have set the table properties for each service field as Required:No and Allow Zero Length: Yes

Some clients may require only one service - how do I get this bugger to work?
Can some one help me? Thank You

[KayCee]

KayCee here again -

I have never used the SQL, but I wanted to add the SQL from the query if that helps. This is for just 4 services not all 12.

SELECT SERVICES.[Client #], [CLIENT LIST].[Client Name], [CLIENT LIST].Type, [CLIENT LIST].Partner, SERVICES.[Service 1], SERVICES.[Promise Date 1], [Due Dates].[Due Date 1], SERVICES.[OUT 1], SERVICES.[Ext DONE 1], SERVICES.[Ext Date 1], SERVICES.[e file 1], SERVICES.[Notes 1], SERVICES.[Service 2], SERVICES.[Promise Date 2], [Due Dates_1].[Due Date 2], SERVICES.[OUT 2], SERVICES.[Ext DONE 2], SERVICES.[Ext Date 2], SERVICES.[e file 2], SERVICES.[Notes 2], SERVICES.[Service 3], SERVICES.[Promise Date 3], [Due Dates_2].[Due Date 3], SERVICES.[OUT 3], SERVICES.[Ext DONE 3], SERVICES.[Ext Date 3], SERVICES.[e file 3], SERVICES.[Notes 3], SERVICES.[Service 4], SERVICES.[Promise Date 4], [Due Dates_3].[Due Date 4], SERVICES.[OUT 4], SERVICES.[Ext DONE 4], SERVICES.[Ext Date 4], SERVICES.[e file 4], SERVICES.[Notes 4]
FROM (((([CLIENT LIST] INNER JOIN SERVICES ON [CLIENT LIST].[Client #] = SERVICES.[Client #]) INNER JOIN [Due Dates] ON SERVICES.[Service 1] = [Due Dates].Service) INNER JOIN [Due Dates] AS [Due Dates_1] ON SERVICES.[Service 2] = [Due Dates_1].Service) INNER JOIN [Due Dates] AS [Due Dates_2] ON SERVICES.[Service 3] = [Due Dates_2].Service) INNER JOIN [Due Dates] AS [Due Dates_3] ON SERVICES.[Service 4] = [Due Dates_3].Service;


Thanks again

[NeoPa]

KayCee,

The way to use an RDBMS is not to handle multiple entries in a single record - although it seems possible.
It will all end in tears.

...But your problem is that all your JOINs are INNER JOINS. I would expect that LEFT JOINs may work for you.

[KayCee]

Thank you NeoPa for looking at this with me. Sorry I do not know what RDBMS stands for. What is the best way to set this up - I don't need any tears.

I tryed the LEFT JOIN, but it populates my query with all of the Clients and thier information from the 'Client List' Table.

[KayCee]

Just want you to know I will be on holiday until Monday next week. I look forward to getting back to this then. Have a great weekend.

[NeoPa]

An RDBMS is, like Access, a Relational DataBase Management System.
I was thinking of the JOINs to the various copies of the [Due Dates] table. Sorry I should have been clearer.
The best way to set this up.
Well, only you know all the answers as only you have all the questions, but I would certainly think about keeping one service item per record.
I can't go into more detail really, but think about it. Think about what you want out of it.
I'm afraid that our role here is more to provide pointers to solutions rather than specific solutions themselves (except for the less complicated questions perhaps).
However, there are some links provided at the top of the Access forum to sites which can help you better understand the concepts involved. (Links to useful sites)

[MMcCarthy]

FROM (((([CLIENT LIST] INNER JOIN SERVICES
ON [CLIENT LIST].[Client #] = SERVICES.[Client #])
INNER JOIN [Due Dates]
ON SERVICES.[Service 1] = [Due Dates].Service)
INNER JOIN [Due Dates] AS [Due Dates_1]
ON SERVICES.[Service 2] = [Due Dates_1].Service)
INNER JOIN [Due Dates] AS [Due Dates_2]
ON SERVICES.[Service 3] = [Due Dates_2].Service)
INNER JOIN [Due Dates] AS [Due Dates_3]
ON SERVICES.[Service 4] = [Due Dates_3].Service;

The fact that you are using four instances of [Due Dates] in the above query indicates to me that your relationships between Services and [Due Dates] is incorrectly designed.

Can you provide a field list for both of these tables and explain the relationship between them. At a guess I would say you have the relationship backwards.

Mary

[KayCee]

Thank you NeoPa and Thank you Mary - I have made some adjustments based on the infrmation you privided. This is what my SQL looks like now:

SELECT [SERVICES].[Client #], [CLIENT LIST].[Client Name], [CLIENT LIST].[Type], [CLIENT LIST].[Partner], [SERVICES].[Service], [SERVICES].[Promise Date], [Due Dates].[Due Date], [SERVICES].[OUT], [SERVICES].[Ext DONE], [SERVICES].[Ext Date], [SERVICES].[e file], [SERVICES].[Notes], SERVICES_1.Service, SERVICES_1.[Promise Date], [Due Dates_1].[Due Date], SERVICES_1.OUT, SERVICES_1.[Ext DONE], SERVICES_1.[Ext Date], SERVICES_1.[e file], SERVICES_1.Notes, SERVICES_2.Service, SERVICES_2.[Promise Date], [Due Dates_2].[Due Date], SERVICES_2.OUT, SERVICES_2.[Ext DONE], SERVICES_2.[Ext Date], SERVICES_2.[e file], SERVICES_2.Notes, SERVICES_3.Service, SERVICES_3.[Promise Date], [Due Dates_3].[Due Date], SERVICES_3.OUT, SERVICES_3.[Ext DONE], SERVICES_3.[Ext Date], SERVICES_3.[e file], SERVICES_3.Notes, SERVICES_4.Service, SERVICES_4.[Promise Date], [Due Dates_4].[Due Date], SERVICES_4.OUT, SERVICES_4.[Ext DONE], SERVICES_4.[Ext Date], SERVICES_4.[e file], SERVICES_4.Notes, SERVICES_5.Service, SERVICES_5.[Promise Date], [Due Dates_5].[Due Date], SERVICES_5.OUT, SERVICES_5.[Ext DONE], SERVICES_5.[Ext Date], SERVICES_5.Notes, SERVICES_6.Service, SERVICES_6.[Promise Date], [Due Dates_6].[Due Date], SERVICES_6.OUT, SERVICES_6.[Ext DONE], SERVICES_6.[Ext Date], SERVICES_6.[e file], SERVICES_6.Notes, SERVICES_7.Service, SERVICES_7.[Promise Date], [Due Dates_7].[Due Date], SERVICES_7.OUT, SERVICES_7.[Ext DONE], SERVICES_7.[Ext Date], SERVICES_7.[e file], SERVICES_7.Notes, SERVICES_8.Service, SERVICES_8.[Promise Date], [Due Dates_8].[Due Date], SERVICES_8.OUT, SERVICES_8.[Ext DONE], SERVICES_8.[Ext Date], SERVICES_8.[e file], SERVICES_8.Notes, SERVICES_9.Service, SERVICES_9.[Promise Date], [Due Dates_9].[Due Date], SERVICES_9.OUT, SERVICES_9.[Ext DONE], SERVICES_9.[Ext Date], SERVICES_9.[e file], SERVICES_9.Notes, SERVICES_10.Service, SERVICES_10.[Promise Date], [Due Dates_10].[Due Date], SERVICES_10.OUT, SERVICES_10.[Ext DONE], SERVICES_10.[Ext Date], SERVICES_10.[e file], SERVICES_10.Notes, SERVICES_11.Service, SERVICES_11.[Promise Date], [Due Dates_11].[Due Date], SERVICES_11.OUT, SERVICES_11.[Ext DONE], SERVICES_11.[Ext Date], SERVICES_11.[e file], SERVICES_11.Notes, SERVICES_12.Service, SERVICES_12.[Promise Date], [Due Dates_12].[Due Date], SERVICES_12.OUT, SERVICES_12.[Ext DONE], SERVICES_12.[Ext Date], SERVICES_12.[e file], SERVICES_12.Notes
FROM (((((((((((((((((([Due Dates] INNER JOIN ([CLIENT LIST] INNER JOIN SERVICES ON [CLIENT LIST].[Client #]=[SERVICES].[Client #]) ON [Due Dates].[Service]=[SERVICES].[Service]) INNER JOIN SERVICES AS SERVICES_1 ON [CLIENT LIST].[Client #]=SERVICES_1.[Client #]) INNER JOIN SERVICES AS SERVICES_2 ON [CLIENT LIST].[Client #]=SERVICES_2.[Client #]) INNER JOIN SERVICES AS SERVICES_3 ON [CLIENT LIST].[Client #]=SERVICES_3.[Client #]) LEFT JOIN [Due Dates] AS [Due Dates_1] ON SERVICES_1.Service=[Due Dates_1].Service) LEFT JOIN [Due Dates] AS [Due Dates_2] ON SERVICES_2.Service=[Due Dates_2].Service) LEFT JOIN [Due Dates] AS [Due Dates_3] ON SERVICES_3.Service=[Due Dates_3].Service) INNER JOIN SERVICES AS SERVICES_4 ON [CLIENT LIST].[Client #]=SERVICES_4.[Client #]) INNER JOIN SERVICES AS SERVICES_5 ON ([CLIENT LIST].[Client #]=SERVICES_5.[Client #]) AND ([CLIENT LIST].[Client #]=SERVICES_5.[Client #])) INNER JOIN SERVICES AS SERVICES_6 ON [CLIENT LIST].[Client #]=SERVICES_6.[Client #]) LEFT JOIN [Due Dates] AS [Due Dates_4] ON SERVICES_4.Service=[Due Dates_4].Service) LEFT JOIN [Due Dates] AS [Due Dates_5] ON SERVICES_5.Service=[Due Dates_5].Service) LEFT JOIN [Due Dates] AS [Due Dates_6] ON SERVICES_6.Service=[Due Dates_6].Service) INNER JOIN (SERVICES AS SERVICES_7 INNER JOIN [Due Dates] AS [Due Dates_7] ON SERVICES_7.Service=[Due Dates_7].Service) ON [CLIENT LIST].[Client #]=SERVICES_7.[Client #]) INNER JOIN (SERVICES AS SERVICES_8 INNER JOIN [Due Dates] AS [Due Dates_8] ON SERVICES_8.Service=[Due Dates_8].Service) ON [CLIENT LIST].[Client #]=SERVICES_8.[Client #]) INNER JOIN (SERVICES AS SERVICES_9 INNER JOIN [Due Dates] AS [Due Dates_9] ON SERVICES_9.Service=[Due Dates_9].Service) ON ([CLIENT LIST].[Client #]=SERVICES_9.[Client #]) AND ([CLIENT LIST].[Client #]=SERVICES_9.[Client #]) AND ([CLIENT LIST].[Client #]=SERVICES_9.[Client #])) INNER JOIN (SERVICES AS SERVICES_10 LEFT JOIN [Due Dates] AS [Due Dates_10] ON SERVICES_10.Service=[Due Dates_10].Service) ON [CLIENT LIST].[Client #]=SERVICES_10.[Client #]) INNER JOIN (SERVICES AS SERVICES_11 LEFT JOIN [Due Dates] AS [Due Dates_11] ON SERVICES_11.Service=[Due Dates_11].Service) ON [CLIENT LIST].[Client #]=SERVICES_11.[Client #]) INNER JOIN (SERVICES AS SERVICES_12 LEFT JOIN [Due Dates] AS [Due Dates_12] ON SERVICES_12.Service=[Due Dates_12].Service) ON [CLIENT LIST].[Client #]=SERVICES_12.[Client #];

However, the query populates all of the service fields with the information that is entered in the first service field box.

The Due Date table has a field for the Service Name (Primary Key) and the Due Date, and the Services table has fields for Record number (Autonumber / Primary Key), Client #, Service, Promise Date, OUT, Ext DONE, Ext Date, e file, and Notes.

Again my intention is to create a form that has space for up to 12 services to be entered for 1 client. If they have more than 12 services we would enter additional forms. I will also need to create reports that will print based on one of the 65 different services. - I hope I am on the right track now.

Thanks

KayCee

[NeoPa]

I too hope you're on the right track.
Pricipally because, if you wanted me to check through your SQL I WOULD cry.
I am a grown man but that wouldn't stop me.

[MMcCarthy]

I too hope you're on the right track.
Pricipally because, if you wanted me to check through your SQL I WOULD cry.
I am a grown man but that wouldn't stop me.

Now Adrian don't be such a Wus.

Seriously though KayCee

My instincts all say that a query of this size is not a good idea and you should look at using multiple subforms, maybe on a multi tabbed page form.

Mary

[KayCee]

Sorry to scare you with that last SQL and Thank You again for the help on this. I sure am learning a lot!!!! I have been looking at subforms as you suggested and I think that a subform in a datasheet view will be very helpful here.

Here is the glich I have run into. I have created a query "Clients with services" as the staring point for the main form. I would like it to return just one record for each client # that has services, but I am getting records equal to the number of services. The only fields in this query are: Client #, Client Name, Type, and Partner.

Here is the SQL:
SELECT [SERVICES].[Client #], [CLIENT LIST].[Client Name], [CLIENT LIST].[Type], [CLIENT LIST].[Partner]
FROM [CLIENT LIST] INNER JOIN SERVICES ON [CLIENT LIST].[Client #]=[SERVICES].[Client #];

Thanks again in advance.

[MMcCarthy]

Your main form should just contain Clients. Adding services causes multiple records and as such services is the first level of subform required. However, if these are just duplicate records you could use SELECT DISTINCT or SELECT DISTINCT ROW to eliminate the duplicates.

Mary

Sorry to scare you with that last SQL and Thank You again for the help on this. I sure am learning a lot!!!! I have been looking at subforms as you suggested and I think that a subform in a datasheet view will be very helpful here.

Here is the glich I have run into. I have created a query "Clients with services" as the staring point for the main form. I would like it to return just one record for each client # that has services, but I am getting records equal to the number of services. The only fields in this query are: Client #, Client Name, Type, and Partner.

Here is the SQL:
SELECT [SERVICES].[Client #], [CLIENT LIST].[Client Name], [CLIENT LIST].[Type], [CLIENT LIST].[Partner]
FROM [CLIENT LIST] INNER JOIN SERVICES ON [CLIENT LIST].[Client #]=[SERVICES].[Client #];

Thanks again in advance.

[KayCee]

Thanks Mary - I have changed the Client # to pull from the Client List rather than the Services table and added DISTINCT to the SQL. It worked, however I am now unable to add additional records in my form. Do you know what will fix that?

[MMcCarthy]

Thanks Mary - I have changed the Client # to pull from the Client List rather than the Services table and added DISTINCT to the SQL. It worked, however I am now unable to add additional records in my form. Do you know what will fix that?

OK If you take Client# from Client List you don't need distinct as I assume the Client# is unique in Client List. Removing Distinct should make it updateable i.e. you can add a record.

[KayCee]

I tryed the SQL with out DISTINCT and I still get a record for each client equal to the number of services. In the design on the query I still have the Client # field from the Services and the Client List tables linked. If I remove the link I get a record for every Client in the Client List. I only want one record for each client that has a service (s). Any other suggestions?

[MMcCarthy]

I tryed the SQL with out DISTINCT and I still get a record for each client equal to the number of services. In the design on the query I still have the Client # field from the Services and the Client List tables linked. If I remove the link I get a record for every Client in the Client List. I only want one record for each client that has a service (s). Any other suggestions?

Try this ...

 
SELECT [CLIENT LIST].[Client #], [CLIENT LIST].[Client Name], [CLIENT LIST].[Type], [CLIENT LIST].[Partner]
FROM [CLIENT LIST] INNER JOIN SERVICES ON [CLIENT LIST].[Client #]=[SERVICES].[Client #]
WHERE [SERVICES].[Client#] Is not Null
GROUP BY [CLIENT LIST].[Client #], [CLIENT LIST].[Client Name], [CLIENT LIST].[Type], [CLIENT LIST].[Partner];
 
 

However, I don't know if it will be updateable. Your only option in this case may be that you will need to open a new Data Entry Form when you want to add a new client. Which makes sense if you are only viewing clients that currently have services.

Mary