How to structure an access database

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • kurai hikari
    New Member
    • Oct 2009
    • 15

    How to structure an access database

    i have a combobox from table( specialization)

    and i have a reports from table (managers)

    the combobox name is( combo1)

    the selections in the combo box are
    managersjob
    governorsjob
    presedentsjob
    employeesjob

    the reports i want to open are
    mj
    gj
    pj
    ej

    now i want to select managersjob from the combobox then the report mj open and when i select governorsjob the report gj opens and so on
    what is the code? and remeber that the combox and the reports i want to open r from adifferent tables in the same db, so any one has an answer?
  • NeoPa
    Recognized Expert Moderator MVP
    • Oct 2006
    • 32636

    #2
    Well, you need to decide first of all how the data is connected. Computer programs are not going to be able to ask the programmer when it's released. The logic needs to be there in the first place. You haven't told us what that logic is (or how it's held in your database).

    Clearly, you can have multiple columns in your ComboBox, and access those in your code. You need to get the correct, related, data into your ComboBox in the first place though.

    Comment

    • kurai hikari
      New Member
      • Oct 2009
      • 15

      #3
      iam quiet lost here?????

      Originally posted by NeoPa
      Well, you need to decide first of all how the data is connected. Computer programs are not going to be able to ask the programmer when it's released. The logic needs to be there in the first place. You haven't told us what that logic is (or how it's held in your database).

      Clearly, you can have multiple columns in your ComboBox, and access those in your code. You need to get the correct, related, data into your ComboBox in the first place though.
      okay i didn't quiet understand all what u said but i want to say that in this db i have 3 tables and the relationship between them is one to many in access. and i have only one column in my combobox itis name is (career)
      so what logic do u mean and what do u mean by the correct related data in my combobox? i need acode to put it in the combobox .so that when i select any selection from the selections i mentioned before it open aspecific report like i said before. and thank u for ur reply XD

      Comment

      • NeoPa
        Recognized Expert Moderator MVP
        • Oct 2006
        • 32636

        #4
        ManagersJob matches MJ. We know this because you've posted it.

        Where is this information (correlation) stored in your database? If there is nothing to connect them, then how can you expect to be able to program it in? It can't look on the web page for it.

        How is Combo1 set up at the moment? Where does it get the data from?

        Comment

        • kurai hikari
          New Member
          • Oct 2009
          • 15

          #5
          ok here is every thing in details i hope

          Originally posted by NeoPa
          ManagersJob matches MJ. We know this because you've posted it.

          Where is this information (correlation) stored in your database? If there is nothing to connect them, then how can you expect to be able to program it in? It can't look on the web page for it.

          How is Combo1 set up at the moment? Where does it get the data from?
          okay iam gonna explain to u in details okay forget what i said earlier let's focus on this

          i have 2 tables( personalinforma tion) table and (specialization) table
          personal information table has these fields [firstname_ job_iD_section]
          specialization table has these fileds[section_numbero fsection]
          now i have a form in the form there is acombobox that get the data from specialization table from section wich contains
          managers
          governoers
          ministries
          and i have areports that get it's data from personalinforma tion table the reports are
          Mj
          Gj
          Ij
          now i want to click on managers in combobox then the Mj report open
          is that possible in this data base? there is no relationship between the 2 tables becouse there is no common field between the 2 tables the section field in the 2 tables are different from each other so i understood from u that i can't programme the combobox if there isn't aconnection between the tables is that righ? so if itis so what shall i do? i hope i covered all the questions u needed to know XD

          Comment

          • NeoPa
            Recognized Expert Moderator MVP
            • Oct 2006
            • 32636

            #6
            Fair enough. I have to ask these questions otherwise I don't know how well your database is set up or how much you are likely to understand.

            It seems now that you have no way in your data to correlate these connections - Specialisation with Report. This can be done manually, but it is not recommended. After all, you are using a database, so why do a job manually when databases are a perfect place to store such correlation information?

            If you were to do it manually, you would need to change your RecordSource (of the ComboBox) to a query (if it's not already) and add a new field. The field would have to be something on the lines of :
            Code:
            ReportName: Switch([Section]='President','Pj',[Section]='Governor','Gj',[Section]='Manager','Mj',[Section]='Employee','Ej')
            Make sure the ComboBox settings are changed to reflect this. there must be at least two columns for instance.
            Another method, assuming it is always the first letter of the [Section] followed by "j", would be :
            Code:
            ReportName: Left([Section],1) & 'j'
            Seriously though, the better approach would be to add a field into your Managers table to indicate which report is associated with each record. That way the ComboBox is populated naturally, and the maintenance of the data is just like any other data, rather than someone needing to dig back into the code or design.

            Comment

            • kurai hikari
              New Member
              • Oct 2009
              • 15

              #7
              we r making aprogress but i have an idea

              what do u think if made it only one table wich means i will join the specialsation table and iti's fields to personalinforma tion table so that way i don't have to add afield or make any connections only one table will be the source of reports and my combobox and that is how my db will look like
              prsonalinformat ion table with these fileds[firstname_ job_iD_section_ section2_number ofsection]

              and i have aform wich contains acombobox the source of the combobox data is field section2(one column)
              the combobox contains these selections
              _ managers
              _governoers
              _ministries

              and i have already made 3 reports they r
              Mj
              Gj
              Ij

              now i want to click on selection managers and MJ report opens
              and click on governors and GJ opens
              then click on ministries then LJ report opens
              i think now things are much easier right and more simple so can u tell me how to do this with one table?
              thnks so much for ur patience with me :D

              Comment

              • NeoPa
                Recognized Expert Moderator MVP
                • Oct 2006
                • 32636

                #8
                Kurai,

                I understand that you're not English, so I expect your language to be imperfect. What I find very confusing is that you seem to take no care at all when you type out your post. You use abbreviations that are frankly not very clever (They are illegal on this site for the very obvious reason that they make it harder for you to be understood). You also mix up your letters and never bother to look at what you've written before posting. I would have thought posting in a language foreign to you would make it pretty obvious that it will make you almost impossible to understand. I'm not sure I want to read through this 5 or 6 times just to save you the bother of reading your own question before posting it.

                I will do what I can with this one, but if more posts come in such a mess as this one I will not bother. I have no interest in developing the skill of trying to understand badly written posts. Please be clear. I do not expect good English. I merely expect to see that you have at least checked your post before submitting it.

                Now. I have read through the post many times and I still cannot find any reference to where you store the name of the report that you want run. If it is in there then please let me know so that I can help you. If it is not in there then I'm very curious to know why. You seem to be talking as if you want to take that approach, yet without specifying where this single important information is held. Remember, I cannot read minds and I cannot see your database, so I only know what you tell me. You haven't told me where this data is held so I cannot help you to use it.

                Comment

                • kurai hikari
                  New Member
                  • Oct 2009
                  • 15

                  #9
                  oh oO

                  iam so sorry i bothered you i appologize so much so sorry my friend iam just trying to satisfy my boss and iam so used to use the abbreviations and i really preview my reply before i submit it okay
                  you needed to know where my reports are stored ?
                  first i use access iam sorry i didn't tell you that before i thought you already know
                  so my reports are stored in reports in access and so are my forms are stored in forms in access and my personalinforma tion table is in tables in access they all are one database so does that answer your question about reports?

                  i will tell you more about the reports okay every report opens aquery for example the MJ report get it's data from aquery that i made to filter only managers data
                  the query is called (m)
                  also the Gj report get it's data from( g) query wich filters only governers reports
                  so is that clear enough?

                  so i have one table and i told u the fields of it
                  and 3 queries and 3 reports each one opens aquery as i told you
                  and i have aform that contains the combobox i told you about with the selections i mentioned and you already know what iam trying to do
                  click aselection then the related report opens

                  iam so sorry again for bothering you i really didn't mean to but iam not carless and common iam not that bad in english ^_^
                  p.s your reply was alittel harsh hehe, but nevermind itis my fault so if there is still something that is not clear just ask and i will make it clear as much as i can

                  Comment

                  • NeoPa
                    Recognized Expert Moderator MVP
                    • Oct 2006
                    • 32636

                    #10
                    Originally posted by kurai hikari
                    iam so sorry i bothered you i appologize so much so sorry my friend iam just trying to satisfy my boss and iam so used to use the abbreviations and i really preview my reply before i submit it okay
                    you needed to know where my reports are stored ?
                    first i use access iam sorry i didn't tell you that before i thought you already know
                    so my reports are stored in reports in access and so are my forms are stored in forms in access and my personalinforma tion table is in tables in access they all are one database so does that answer your question about reports?
                    No. Not really. I'm not sure how I can make the question make better sense to you, but I will try. By the way, this is not about being stupid. If I were trying to do this in any language but my native one (English) I would be thoroughly confused. Unfortunately though, I don't think you have quite grasped the question properly.

                    Where is the information about which report to use held? Where in your database is there information that tells the computer which report to run when it already knows which item has been selected from the ComboBox? As far as I can tell, you don't have that information in your database anywhere.

                    I suggested earlier what needs to be done (See post #6) to ensure this data (the correlation data) is held within the database. It should be added to the Managers table. In that post there are also two suggestions for handling the situation more easily/clumsily. These should both work too. If you can't manage to answer the question for any reason, then the next best thing is simply to work from that post and I'm sure you will have a workable solution that should keep your boss happy.

                    Good luck.

                    Where is the information about which report to use held? Where in your database is there information that tells the computer which report to run when it already knows which item has been selected from the ComboBox?

                    I suggested earlier what needs to be done (See post #

                    Comment

                    • kurai hikari
                      New Member
                      • Oct 2009
                      • 15

                      #11
                      ok the last try

                      Originally posted by NeoPa
                      No. Not really. I'm not sure how I can make the question make better sense to you, but I will try. By the way, this is not about being stupid. If I were trying to do this in any language but my native one (English) I would be thoroughly confused. Unfortunately though, I don't think you have quite grasped the question properly.

                      Where is the information about which report to use held? Where in your database is there information that tells the computer which report to run when it already knows which item has been selected from the ComboBox? As far as I can tell, you don't have that information in your database anywhere.

                      I suggested earlier what needs to be done (See post #6) to ensure this data (the correlation data) is held within the database. It should be added to the Managers table. In that post there are also two suggestions for handling the situation more easily/clumsily. These should both work too. If you can't manage to answer the question for any reason, then the next best thing is simply to work from that post and I'm sure you will have a workable solution that should keep your boss happy.

                      Good luck.

                      Where is the information about which report to use held? Where in your database is there information that tells the computer which report to run when it already knows which item has been selected from the ComboBox?

                      I suggested earlier what needs to be done (See post #

                      i understood from your questions that you don't get my database at all. and no matter how much i explain and try to give you any details it won't work so here an example from my data base with access 2003 open it see it and answer all your questions.

                      you can answer your first question from the data base i sent you


                      but the second question is what iam asking you to help me with , that is what i want to know .can you give me the information that orders the computer which report to run when it already knows which item has been selected from the ComboBox?

                      as you can see in the db the combobox is in the form called (list)
                      itis very simple example from my database explore it all and see the queries and the reports . and i hope we make better progress this last time =)

                      db700.zip

                      and iam doing my best with woking from the post 6 XD

                      Comment

                      • NeoPa
                        Recognized Expert Moderator MVP
                        • Oct 2006
                        • 32636

                        #12
                        Originally posted by kurai hikari
                        i understood from your questions that you don't get my database at all. and no matter how much i explain and try to give you any details it won't work so here an example from my data base with access 2003 open it see it and answer all your questions.
                        I understand your database perfectly well (thank you). What I don't understand is why you cannot answer my question. Having your database won't answer my question. My question pertains to the design of the database in your head (I think it's pretty clear by now you haven't anything in your database that could give you the required link). I have tried to lead you to understand this but it seems no matter what I say you persist in thinking that I'm the one that's confused.
                        Originally posted by kurai hikari
                        but the second question is what iam asking you to help me with , that is what i want to know .can you give me the information that orders the computer which report to run when it already knows which item has been selected from the ComboBox?
                        No. Simply put that's impossible as it is data related not design related. I could design until the cows come home (for ever) and not be able to create something from nothing. I don't know how to make this point any clearer. You must start with some understanding of what connects the selection to the report. If there's something there, anything, the design can pick it out. Unfortunately, you haven't covered that first step yet. I have no knowledge of your requirements until you explain them to me. How do you think I can explain to you how to do it when you haven't explained properly what you want.

                        I'm sorry if my frustration is showing through, but this doesn't seem too complicated to me and I really cannot understand why you seem unable to grasp such a simple concept. The fact that you seem to be under the impression that your inability to understand simple concepts indicates I'm stupid, just makes it worse.
                        Originally posted by kurai hikari
                        as you can see in the db the combobox is in the form called (list)
                        itis very simple example from my database explore it all and see the queries and the reports . and i hope we make better progress this last time =)
                        I have looked at your database and I'm not surprised to see in there absolutely nothing that answers my question. This is because only you can answer that.
                        Originally posted by kurai hikari
                        and iam doing my best with woking from the post 6 XD
                        Actually, the question was supposed to save me some time and effort so I didn't have to explain all the different ways of doing it. I gave up on that a while back and posted everything you need in post #6 anyway. It may not be as much use as I thought now, as it seems your database is very different from what you described anyway. The tables you have talked about are not even there. Adding a field to the [Managers] table will not be possible if there is no [Managers] table.

                        Comment

                        • kurai hikari
                          New Member
                          • Oct 2009
                          • 15

                          #13
                          XD easy , iam just a biggener girl but not for long =)

                          i understand u and i did from the start . i know that i have to make aconnection between the selection and the report but with my data base i didn't know how

                          and howcome i didn't say what i want i told many times that i want to select aselection from the combobox and then aspecific report opens i said that in all my posts already but i don't know how to do that or how to make the requirments for that i tried to make aconnection like you told me that is why i made it only one table i thought that way if all the reports and the combobox are from the same table then that is aconnection

                          and you are talking to me about the managers table yes it was there at first but only in my first post but i modified the db and already told you how i modified it in my third post when i said let's forget about what i said earlier and focuse on this. but you only focused on my first post

                          i know itis very simple but i think we missunderstood eachother in every post lol
                          and i never ever would think that you are stubid or something like that never, you are the only one who is bothering him self to at least read my posts and i apreciate that so much. so plz don't come to concludes like this , i think you are the one who thinks iam stubid.

                          you are asking me about the design in my head , but that is why i sent you an example from my data base becouse i designed it that way. nothing else is in my head that is why iam asking for help. how to make that connection between the selections and the reports and how to make the selections open the reports i want to open? i showed you the db so that you can tell what do you think or give me adesign from your head to help me reach what i want.

                          i know itis very annoying to the professionals to help the beginners but if they don't help them there will never be professionals XD

                          anyways iam not gonna give up iam gonna be good at this things by away or another and i will find away for my db iam not gonna stay a beginner girl for long =) don't be frustrated. itis my work and my boss and iam not frustrated it doesn't matter if away faild another one won't ^_^

                          thank you so very much for taking the sufferness i coused you. in my next issue i will try to be more clear, iam gonna ask alot but in different issues but it seems u won't get rid of me hahaha XD i thank you so much really thnx

                          Comment

                          • NeoPa
                            Recognized Expert Moderator MVP
                            • Oct 2006
                            • 32636

                            #14
                            This is difficult. It is difficult to communicate clearly I know.

                            You are very determined and I respect that. I really do.

                            I am at a loss though (I'm stuck. I can't see what to say next to help). I don't know what I can say that will make sense to you.

                            Do I think you're stupid? If I'm fair, I think that the real problem is probably that you are trying to do what I cannot, and are trying to ask a technical question in a foreign language. That must be very difficult. It is simply frustrating for me, as I am usually very good at clear communication, and I am aware that something is not getting understood here between us.

                            Let's forget my question now. It need not be important.

                            Let me ask a new question. Have you renamed the reports so that they match the Section value? If so, then we can proceed quite easily.

                            Comment

                            • NeoPa
                              Recognized Expert Moderator MVP
                              • Oct 2006
                              • 32636

                              #15
                              Do you understand that the way the reports are named now (to match the [Section] field exactly) you only need to call the report to open passing the [Section] value to the Open call :
                              Code:
                              Call DoCmd.OpenReport(ReportName:=Me.Section, _
                                                    View:=acViewPreview, _
                                                    WhereCondition:="[SomeField]=" & Me.SomeControl, _
                                                    AcWindowMode:=acWindowNormal)
                              I tried to use the field and control names from your database to make it easier for you, but they are in some unicode font that my PC doesn't have so I was unable to get them cleanly.

                              Comment

                              Working...