Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2013
    Posts
    1

    Unanswered: Adding the amount of blanks in specified fields

    Hi there!

    I'm rather new to access and database apps in general, I've been trying to read up but I'm still rather confused on how I should approach this.

    1) I have 10 fields (Session 1 to 10) which specify the date where my customers attend their sessions.

    2) If the customer has not attended the session, the field is blank by default.

    3) I'm trying to create a table field to calculate all the blank fields. i.e. the number of sessions a customer has yet to attend.

    4) I can't seem to figure out the correct expression.

    Really appreciate it if anyone could spare a few moments to help me out.

    Thanks so much!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could try something like:
    Code:
    SELECT IIf(IsNull(Session1),1,0) + IIf(IsNull(Session2),1,0) + ... AS SumOfUnattendedSessions
    FROM ...
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    That will work, buts its a workaround for a bad design
    having 10 columns of what is essentially similar datain a single row is never a smart call

    if all 10 columns were say sample readings taken at the same time, then fine
    as they are 10 sessions then your design is causing complications that Sindho has come up a solution

    instead I'd be looking at something like the attached file
    you have a table for:-

    Customers (just stuff about the customer, their name, contact details and so on) if yuy have multiple addresses then create a sub tabel for CustomerAddresses
    SessionTypesfor now you may have jsut the one row defining a block of 10 bookings that make up this session/membership type. No Bookings stores the number of days/bookings/sessions that this type can participate in
    CustoemrContracts holds details of what you have contracted with the custoemr. it coudl include payments, any special requests and so on,but it muyst identify the customer (through CustomerID) and the SessionType (through SessionID) so that you know which customer made a booking and what type sessions they want (IE the number fo days in their contract with you) The runs from tells you when the contract runs frm, the runs to tells you when the contract expires (So you could say have a contract which rins for 3 months for upto 10 sessions)
    CustomerSessions is where you store details of an actual customer session it identifies the contract (And defactor from that the customer) and it also includeds the datetime of the appointment (SessionDate) which is in the primary key, so you cannot double book the same appointment for the same customer. you could expand the information here to include other stuff. ferinstance you would probably create this row when booked so you might want something about if the customer turned up or completed the sessions. say we added another column called 'Completed', a boolean (yes/no) column

    why would we want to do this

    well
    1) retrieving the number of sessions a customer has done is straightforward
    select count(CustomerSessions.ContractID) from CustomerSessions
    WHERE CustomerID = 35 AND Completed = true

    2) You are no longer tied to 10 sessions per customer, you can find out how many sessions a customer has used
    you know how many sessions they are entitled to through the CustomerContracts AND SessionTypes tables.

    3)if you want to find out todays sessions
    SELECT Customers.Name, CustomerSessions.SessionDate from Customers, CustomerSessions
    JOIN Customers on Customers.ID = CustomerContracts.CustomerID
    Join CustomerContracts on CustomerContracts.ID = CustomerSessions.ContractID
    WHERE DATE() = format('#yyyy-mm-dd#' = CustomerSessions.SessionDate)

    The db world talks of normalisation, in essence store one piece of information once
    dont' store multiple customer names, store information in discrete tables. if ever you see yourself repeating the same sort of data ( a classic example is Session1, Session2.... then its nearly alwasy an example of a non normlised design
    Fundamentals of Relational Database Design -- r937.com
    The Relational Data Model, Normalisation and effective Database Design
    Attached Files Attached Files
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •