Nz statement in query criteria error (1 Viewer)

[crosseydlobster]

I have a query [QRY_register] that pulls from a few different tables and my goal is to include Null fields in the query. Therefore I have used an Nz statement in the criteria of my query to convert the Null values to zero… but I cannot get it to work.

The query is named QRY_register and it pulls from TBL_customer, TBL_Photo1Dates, TBL_Photo2Dates, TBL_Photo3Dates and TBL_PhotoshopDates

The query relates dates and times for photography classes. All dates are Long Date data types and all times are Medium Time data types.

If you open FRM_customers and search for any of the three customers; Jane Doe, John Doe and Baby Doe you will find the class dates they are registered for. When you press the confirm button for that customer’s record it runs a macro which opens RPT_confirm, sends the report as an email attachment and then closes the report. This only works for Baby Doe because as you will see this is the only record which is registered for each of the four photography classes. In other words none of the fields are null for this record. However, Jane and John Doe are not registered for each of these dates meaning that it produces a Null for those fields.

This is the error message I get: "An expression you entered is the wrong data type for one of the arguments".. The report is running from QRY_register and this is where I am attempting unsuccessfully to use the Nz statement in the query criteria to include those records with Null fields. I would love any insight! BTW this is still a work in progress. Thanks!

 

[NickHa]

That's a lot of ANDs in your query!

Picking out the first test from the WHERE phrases:

(TBL_customer.Photo1Register)=Nz([TBL_customer].[Photo1Register],0)

... I am puzzled by the logic here. The same field is present on both sides of the test, one with Nz + square brackets and one without. What are you trying to achieve here? How does the result from that (and subsequent similar tests) work in context?

 

[crosseydlobster]

I am obviously not approaching this properly and I am unsure of myself on this. I used the design view to build the query allowing Access to write the SQL.

My goal here is to allow RPT_confirm to show which photography classes a customer is registered for. This report is being used as a confirmation email. If a customer is only registered for one of the classes this should be shown on the report, whereas the classes for which the customer is NOT registered should be blank on the report.

I have been unsuccessfully attempting to use QRY_register to power RPT_confirm. I would be happy to approach this differently if you have any suggestions.

 

[NickHa]

The root of your problem appears to be lack of normalisation. Inserting fields in the customer record for every possible course makes for complexity such as you are experiencing.

In essence, you have four elements for this part of your database:
1. Course details (name, description etc.)
2. Customer details (name, contact methods etc.)
3. Session details (date, start time, duration)
3. Course registrations which links the above tables.

I would suggest separate tables for each of these elements. Put an independent key (a number, not a date) in each table (probably an AutoNumber field).

If you think of your tables in something like a pyramid arrangement, then as you go lower into the pyramid, you link up to tables at the upper levels or across to tables at the same level. This is the basis of one-to-many and many-to-many relationships.

I think the course and customer tables are at the top of the pyramid; they hold the relevant information which describes static characteristics - i.e. no course information in customer details.

The next level is the sessions table. This table would contain:
1. a foreign key which points to the course description table.
2. a date field which gives the session instance
3. the session start time
4. the session duration (but see below).

If all of the sessions for a particular course have the same duration (e.g. 2 hours), then consider putting this in the course table because it is fixed. If the duration varies, then put it in the sessions table. I would use a duration, rather than an end time because that is easier to manage in many respects. The end time is the start time plus the duration.

Once you have the customers, courses and sessions defined, you go on to handle the registrations. Essentially, you are allocating customers to sessions at this level.

The registrations table would contain foreign keys to link the information:
1. a foreign key to the sessions table (which indirectly gives the course table link)
2. a foreign key to the customers table
3. a location, such as building and room details (which could also be a FK to a locations table - and probably should be)
You could put in a FK to the courses table here if you don't like the indirect link via sessions. That's a matter of personal choice and convenience.

Each of the tables would have as much information as you need to hold for each element's attributes. The point is that everything is static in an individual record, but the variations come from having multiple records for each entity.

If you search within this forum for normalization (or via other search engines on the web), you will find various contributions with insights to the process, so I shan't repeat them here.

So, to get back to your report, the box below 'Your Registration' in RPT_confirm can be made into a sub-report, which picks out the records for the current customer in the registrations table and each registration entry picks out the relevant session details. It soulds a bit daunting, I know, but once you work out the relationships it will become clearer.

Now I've made your head hurt, I'll leave it to you to digest and come back with further questions once the pain has eased. Good luck!

 

[crosseydlobster]

This all makes beautiful sense. Thank you so much for the direction! I will digest it all and put it to work. Thanks again for the great explanation my quality of life just improved!

 

[crosseydlobster]

Well I believe I am in better shape than before. I would like a bit more of your guidance however. I am sure it has to do with the structure of the database. When you open FRM_customers and search for say 'Jane Doe' the goal would be to sign Ms. Doe up for a different class date for each of the different photography classes. However when you do so it does not add a separate record to registration_TBL. Instead each time you select a date for the different classes it overwrites the record. Can you offer any suggestions?

 

[NickHa]

Yes, good progress!

The problem with FRM_customers is that the name does not match the function it is trying to serve, which is causing confusion. If you named this FRM_registrations, then it would make more sense in context.

If you look at this from the top down, the purpose is to register a customer to a session, or to list existing registrations. Now, you can say one session has many customers, or you can say one customer has many sessions - either way, you have a one-to-many view for your data. So the question you must consider is which part of the information must be on the main form and which on the subform. Let's stick with the way you have it, which is one customer with many sessions. The main form in this case would have the customer details and the subform would list all of the sessions for which this customer has enroled.

The structure of the subform is important here. Instead of having four subforms, you need only one. The default view needs to be 'continuous forms' ('Default View' on form's 'Format' property tab) and the detail section must be no greater than one row of controls in height (so that you end up with something like the DataSheet view when you open it).

The Row Source for this subform would be something like

SELECT registrations_TBL.CustomerID, 
       classdetails_TBL.ClassDate, 
       classes_TBL.ClassName 
FROM classes_TBL 
     INNER JOIN (classdetails_TBL 
                 INNER JOIN registrations_TBL 
                       ON classdetails_TBL.ClassDetailsID=registrations_TBL.ClassDetailsID) 
            ON classes_TBL.ClassID=classdetails_TBL.ClassID;

Note I have spread this over multiple lines here for clarity, but you would have this on a single line in practice. You can see that this query returns the customer Id, class date and class name. The customer Id is used to link the main form (parent) with the subform (child) and the subform will show just the date and class name in each record.

Now, when you choose a customer in the main form, all sessions for that customer id will be listed in the subform.

I hope this is enough to get you on the right path, but if not, feel free to seek clarification.



A couple of general points:

• When designing a form or report, describe in plain english what it will do (function) and how you will use it (context) - write this out in bullet form. That will help you to work out which information you need. With practice, this becomes second nature, but it's difficult when you do it the first few times.
• For each table you have, start with one form per table for just data-entry purposes. This is not a real-world situation, but it gives practice in the basics. Once comfortable with this, expand the functionality to enable two or more tables to be maintained by a single form (e.g. sessions in your case which links classes to customers). This is reflecting the business functionality more closely.
• When searching for records, I suggest using combo boxes to aid the user select from existing records, rather than relying on exact match of names which may or may not exist and which might be mis-spelt. In your case, I would suggest a combo box with its row source as "SELECT LastName, FirstName FROM customers_TBL ORDER BY LastName, FirstName;", so that the first names are listed within last name. There are ways to search on characters keyed (e.g. "do" would find all "Doe (plus any other names beginning with those letters)", which is (possibly) better, but beyond the scope of this topic.

 

[crosseydlobster]

I have been playing around with your suggestions and have a few questions. When you refer to the 'Row Source' of the subform, do you mean the 'Record Source' or are you referring to the 'Row Source' of the combo box on the form? I assumed you meant the 'Record Source' of the subform.

I used the SQL you provided and added one more field from the registrations table. I also used the 'Continuous Forms' view and created the combo boxes for each of the photo classes on the subform following your formatting advice.

It works but it is not pretty. If you take another look at what I have you will see that the user is able to select the dates for each photo class but you must do so on a separate line. I will admit that I have never used 'Continuous Forms' before so I may be misunderstanding its intended structure here. By the way, thanks for the added advice on some of the other aspects of my DB. I will absolutely take these things into account as I begin cleaning it up in the final stages. Thanks so much for all of your input!

 

[NickHa]

It works but it is not pretty

You're right - it' not pretty!

When you refer to the 'Row Source' of the subform, do you mean the 'Record Source' or are you referring to the 'Row Source' of the combo box on the form? I assumed you meant the 'Record Source' of the subform.

Yes, I did mean the Record Source of the subform.

I may be misunderstanding its intended structure here

Yes, you're missing the point with the subform - perhaps I have explained it badly, in which case my apologies.

You have the basic structure now (with the continuous view), but it's still inappropriate in layout. You need to get away from the idea of having each course listed on the subform (in any guise), because each time you add a new course, your form design must change (not to mention the usability issues as you have seen).

If you paste this query in the Query design (SQL view), the resultant datasheet is what you should be aiming to reproduce in your subform.

SELECT classes_TBL.ClassName, classdetails_TBL.ClassDate
FROM classes_TBL 
    INNER JOIN (classdetails_TBL 
        INNER JOIN registrations_TBL 
        ON classdetails_TBL.ClassDetailsID = registrations_TBL.ClassDetailsID) 
    ON classes_TBL.ClassID = classdetails_TBL.ClassID
WHERE (((registrations_TBL.CustomerID)=1));

This is essentially the same query as before, but I have constrained the selection with the WHERE clause to choose CustomerId 1 in this case - that is where the linked field between the parent and child forms comes in. In effect, you are telling the subform to show all records for a specific customer as shown on the main form. Over time, as the numbers enrolling increase, the subfom will eventually overflow its space, in which case you start scrolling. You can overcome this in a variety of ways, such as ordering by date in descending order, excluding past dates etc. (in the query).

I also used the 'Continuous Forms' view and created the combo boxes for each of the photo classes on the subform following your formatting advice.

I suggest you use text boxes on the subform, rather than combo boxes. I appreciate that you need to allocate new classes to customers, but you could do this by having additional combo boxes (main form?) to do this. It's not wrong to use combo boxes in the subform, but it brings additional management problems.

Thanks so much for all of your input!

You're welcome.

By the way, there are many possible solutions to you requirements, so don't limit yourself to simply following my input. Once you have the basic ideas in place, you can rearrange things to suit your specific needs.

 

[crosseydlobster]

Thank you so much. I have been working feverishly on my database with all of the info you have provided. Things are going great thanks to you. Thanks for the wonderful intro to normalization and continuous forms!

 

[NickHa]

I had been wondering what had happened ... Glad to hear it's all working out.