Results 1 to 10 of 10

Thread: Database issue

  1. #1
    Join Date
    Mar 2009
    Posts
    16

    Unanswered: Database issue

    Thanks to all who helped so far.

    I have two tables. One table has combo boxes of names and a date. when I click next for a new day everything clears.

    I have another table for comments.

    All these categories are on one form. So everytime I click next day on the form I want the comments field to be linked to that day and person. When I press next day comments box will clear.

    But I am not sure how to link this comments with the rest.

    This is my last problem to fix, btw I am not an expert at DB as you can tell.


    Thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Can you be more precise please?
    -What data model to you use (structures of the tables involved in your problem) ?
    -What kind of relationship exist between the two tables?
    -How is the form you're talking about?
    -What do you mean with "one table has combo boxes" ? Tables are data structures and cannot have combo boxes nor any other type of controls, so please explain what you mean.
    -What categories are you talking about and what does "click next day on the form" means? Is there a "next day" button? if yes, what's the code behind it?

    Maybe you should provide an example of your database (or at least some data definition and the code used on the form), so it would be easier to help you.

    Have a nice day!

  3. #3
    Join Date
    Mar 2009
    Posts
    16
    Quote Originally Posted by Sinndho
    Can you be more precise please?
    -What data model to you use (structures of the tables involved in your problem) ?
    -What kind of relationship exist between the two tables? none yet
    -How is the form you're talking about?
    -What do you mean with "one table has combo boxes" ? Tables are data structures and cannot have combo boxes nor any other type of controls, so please explain what you mean.

    Sorry i meant FORM


    -What categories are you talking about and what does "click next day on the form" means? Is there a "next day" button? if yes, what's the code behind it?


    Maybe you should provide an example of your database (or at least some data definition and the code used on the form), so it would be easier to help you.

    Have a nice day!
    Hi
    This is a an appointment database from 6am to 8 pm in half hour slots with three available slots per half hour.

    After clicking it adds new data
    I dont have much experience with databases,I have probably designed it wrong.

    Over all you have a Name and surname as comboboxes and comments as a text box on a form for all the times mentioned. I have given each combobox its own data source, so I have added 97 Names and surnames on the data table(This is probably bad deisgn). When I went to add 87 comments text boxes it would not allow me to do this beause I was going over the the table limit of 255. This is why I decided to make a new table with 87 comments of data text categories and assign them to my form 97 text boxes on my form. But this is not working.

    Thanks


    I will send you the database ifneeded.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The first thing you should do would be to normalize the data model. For instance, you could create a table for Persons, a table for Times and a table for Appointments, like this:

    Table Appointments:
    ------------------
    AppointmentID, AutoNumber
    AppointmentPersonID, Long
    AppointmentTimeID, Long
    DateOfAppointment, Date/Time
    AppointmentComment, Text 255

    Table Persons:
    -------------
    PersonID, Autonumber
    Name, Text 50
    Surname, Text 50
    PersonComment, Text 255

    Table Times:
    -----------
    TimeID, AutoNumber
    Time, Date/Time
    TimeComment, Text 255
    In the table Appointments, AppointmentPersonID contains a pointer to the table Persons and AppointmentTimeID contains a pointer to the table Times.

    That way, every piece of data is stored in one and only one place.

    You then create a query grouping data from the three tables, using the relationships described above :

    Code:
    SELECT Appointments.AppointmentID, 
    	Appointments.AppointmentPersonID, 
    	Appointments.AppointmentTimeID, 
    	Appointments.DateOfAppointment, 
    	Appointments.AppointmentComment, 
    	Persons.Name, 
    	Persons.Surname, 
    	Persons.PersonComment, 
    	Times.Time, 
    	Times.TimeComment
    FROM 	Persons 
    	INNER JOIN Appointments ON Persons.PersonID = Appointments.AppointmentPersonID) 
    	INNER JOIN Times ON Appointments.AppointmentTimeID = Times.TimeID;
    Now you can build a form based on this query.

    You'll find in attachment a quick and very simple example of what can be done this way. Simply open the F_Appointments form.

    Have a nice day!
    Attached Files Attached Files

  5. #5
    Join Date
    Mar 2009
    Posts
    16

    my database is attached

    Quote Originally Posted by Sinndho
    The first thing you should do would be to normalize the data model. For instance, you could create a table for Persons, a table for Times and a table for Appointments, like this:



    In the table Appointments, AppointmentPersonID contains a pointer to the table Persons and AppointmentTimeID contains a pointer to the table Times.

    That way, every piece of data is stored in one and only one place.

    You then create a query grouping data from the three tables, using the relationships described above :

    Code:
    SELECT Appointments.AppointmentID, 
    	Appointments.AppointmentPersonID, 
    	Appointments.AppointmentTimeID, 
    	Appointments.DateOfAppointment, 
    	Appointments.AppointmentComment, 
    	Persons.Name, 
    	Persons.Surname, 
    	Persons.PersonComment, 
    	Times.Time, 
    	Times.TimeComment
    FROM 	Persons 
    	INNER JOIN Appointments ON Persons.PersonID = Appointments.AppointmentPersonID) 
    	INNER JOIN Times ON Appointments.AppointmentTimeID = Times.TimeID;
    Now you can build a form based on this query.

    You'll find in attachment a quick and very simple example of what can be done this way. Simply open the F_Appointments form.

    Have a nice day!

    Hi

    Thanks for all the effort you have made

    I have attached my file

    This way you maybe able to help me more
    As I said I need the comments to be fixed. i am pretty sure this is designed badly. The form is how i want it to be though
    Thanks

    johnathon
    Attached Files Attached Files

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Well ! In that case, the easiest way to get what you want would be to create a function that returns a comment from the Comments table and use it as the RecordSource of the Comments TextBoxes in your form.

    See how it's done in the attached file.

    Have a nice day!
    Attached Files Attached Files

  7. #7
    Join Date
    Mar 2009
    Posts
    16

    hi does notwork

    Hi

    That function does not work correctly everytime I press next day it wont allow me to enter anything in those three boxes. Can you please correct this. All comments boxes should be recorded to a new day

    If i enter late one day,m then the next day it should be clear and when i go back to that day it should have late. I should be Able to enter whatever I like and change it whenevrr

    thanks again

  8. #8
    Join Date
    Mar 2009
    Posts
    16

    doesnt work

    Quote Originally Posted by Sinndho
    Well ! In that case, the easiest way to get what you want would be to create a function that returns a comment from the Comments table and use it as the RecordSource of the Comments TextBoxes in your form.

    See how it's done in the attached file.

    Have a nice day!

    Hi

    That function does not work correctly everytime I press next day it wont allow me to enter anything in those three boxes. Can you please correct this. All comments boxes should be recorded to a new day

    If i enter late one day,m then the next day it should be clear and when i go back to that day it should have late. I should be Able to enter whatever I like and change it whenevrr

    thanks again

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The function works properly: it returns the data you're looking for.

    If you want to input data, you have to dynamically change the control source of the control:
    -if you want to add data then ControlSource = column in the table or in the query.
    -if you want to see data then ControlSource = function.

    However the basic problem is that you have more than 255 columns of data, so you cannot access them all in one time. We come back to what I wrote about normalization. What you are trying to do is totally right in a spreadsheet such as Excel, but it cannot easily be done in Access because Access is not made for that.

    You could probably solve the problem by using a subform but the interface would be totally different then. Maybe you should consider dividing the day in two halves (perhaps using a ControlTab control) as to have less controls (and less data) to deal with in the form.

    Have a nice day!

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Just thinking of something. It's a bit "twisted" but it should work. You can replace the textboxes with the comments by comboboxes with only one line.

    As each combobox can have it's own data source independently of the data source of the form, you should be able to get what you want. However it's a lot of work and the resulting form would probably rather slow.

    See how it's done in the attached file.

    Have a nice day!
    Attached Files Attached Files

Posting Permissions

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