 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / May 2008Still New to Access DB
I have started a new post after posting in my old one. I'm not sure this the right way to do this as the old post has worked its way down the list and have had no responce. This started as a table design problem so I hope I am posting to the correct newsgroup I am still having trouble with my subforms. I have set up the main form based on the employee. Subform form 1 is my TblFundingCentre with a Link Master Field EmpID with a Link Child Field PayID. Problem here is Tab Order, I have checked the Tab Order in design view and it looks OK but tab goes to the last field on the subform. I have the same problem with subform# 2 TblAttendEvent Link Master Field EmpID with a Link Child Field EmpID. When I created subform # 2 I had to add fields from TblEvent as I need that info in the record. I had a suggesting last week that I could put a combo field using a query on my sub form but I could not get that to work as the wizard said I could not use the query and try another table or Query. I may want to add that once all is good, I need to get reports with what employees name, the conf they attended and what funding center paid for it. Also how much of each funding centre has been spent per employee and total spent. Any help would be appreciated  SignatureThanks Brent Hi Brent, Could you check if I've listed your table structure correctly. I read your previous post where you began to do it but it got a bit confusing as to which field was in which table or if the space between the field names meant that there were 2 fields or 1. You could just edit the structure below eg TblEmployee EmpID (PK Autonumber) EmpFirstName EmpLastName TblFunctionalCentre PayID (PK Account Functional Centre Org Other Funding Source (is this meant to be a field in your table? what is this about? TblEvent EventID (PK) ConfName Venue Presenting RefundDate (date by which refunds must be claimed) (but no start and enddate for the event???) TblAttendEvent AtEventID (PK) EmpID (Linked from TblEmployee FundID (linked from TblFundingCenter EventID (Linked from TblEvent) RegCost (how much it cost that employee to register for the event) TravelCost (How much it cost the employee to travel to/from the event) AccomCost (How much the employee had to pay for accomodation ReturnDate (the date the employee returned from the event DateBooked date Employee was booked to attend the event DateAttended (date Employee attended the event - or else just use a yes/no field) FundAmount (because you said that the centre funds an individual employee rather than giving a lump sum to be divided among several Employees However, when you say that you need to know 'How much of each funding centre has been spent per employee and total spent.' That seems to imply that the centre gave a lump sum for the employees and it was divided up among them/ If this is true then you may need another table to express this. You will need to be clear (since we dont' know your business) if the funding centre gave a lump sum for say 5 employees to divide among themselves to attend a particular event, or if the FC pay £x for Employee 1 and £x for Employee 2 to attend a specified event. The table structures will differ. A list like this, with explanations, will ensure the structure is absolutely correct before you go on to creating a coded, linked form. When you say you checked the Tab order, did you go to View , Tab order? For your combo, you will almost certainly have to use different query from the one on which your subform is based and it needs to have a Unique Primary Key field. So if your subform held a list of groceries with the main form being the Shopping Trip, You wouldn't base your Groceries combo on the this subform table because each grocery could appear more than once on different shopping trips. You would need to base it on a table containing a list of different items available for buying with each item being only listed once. And you should include that Primary Key field which the wizard should hide and next, the field which you want to see in your combo To avoid confusion, you can start off basing your combo on your 'one' table (each item in the combo is only listed once) and when that is working, then go into the combo's Row Source Properties and click next to that so that you turn the table there into a sorted query. Can I suggest, that until you've had a bit more experience with forms and subforms, that you put up with using seperate forms+subforms to add your data. So use simple forms without data to add your data to your 'one' tables (TblEmployee, TblEvent TblFunding Centre in my example) Then use one of these 'one' table as a main form and add the links via a combo with your single forms ready to add extra details Once that feels comfortable, and you can manage your combos etc then you can think about your double form Otherwise trouble-shooting will be a nightmare. Evi > I have started a new post after posting in my old one. I'm not sure this the > right way to do this as the old post has worked its way down the list and [quoted text clipped - 23 lines] > -- > Thanks Brent Evi I hope you can see my responses as I tried to make it as clear as I can. I started a new db and removed all the spaces as you suggested and used your naming conventions. I tried to edit your post to help. All is as you have it other than my notes in brackets. eg TblEmployee EmpID (PK Autonumber) EmpFirstName EmpLastName EmpNumber (a company number assigned each employee) TblFunctionalCentre PayID (PK Account Functional Centre Org Other Funding Source (is this meant to be a field in your table? what is this about? (A text field for notes if another funding source will pay a portion of the costs. Another program looks after this part of the funding if it applies) TblEvent EventID (PK) ConfName Venue Presenting RefundDate (date by which refunds must be claimed) (but no start and enddate for the event???) This table is correct. The start and end dates are in the TblAttendEvent table.( This is true) (I just found out I need a confStartDate and a ConfEndDate added, this was a request from another user. Where will these fields go?) TblAttendEvent AtEventID (PK) EmpID (Linked from TblEmployee (Yes) FundID (linked from TblFundingCenter (I called this field PayedID)(Yes) EventID (Linked from TblEvent) (Yes) RegCost (how much it cost that employee to register for the event) TravelCost (How much it cost the employee to travel to/from the event) AccomCost (How much the employee had to pay for accomodation TravelDate (I have this field) (date employee traveled to event) ReturnDate (the date the employee returned from the event DateBooked date Employee was booked to attend the event (I need this field) DateAttended (date Employee attended the event - or else just use a yes/no field) (I need the Date) FundAmount (because you said that the centre funds an individual employee rather than giving a lump sum to be divided among several Employees (I don't have this field anywhere in the db and yes the centre funds an individual) However, when you say that you need to know 'How much of each funding centre has been spent per employee and total spent.' (This is a report the Manager wants to see as there are some 30 funding centres so wants to see the breakdown and totals on reports) A list like this, with explanations, will ensure the structure is absolutely correct before you go on to creating a coded, linked form. When you say you checked the Tab order, did you go to View , Tab order? (Yes in the form design view, it worked ok for the first record then would jump to the last field in each of the two subforms ???) For your combo, (Lets forget this for now if it will make things easier) Can I suggest, that until you've had a bit more experience with forms and subforms, that you put up with using seperate forms+subforms to add your data. So use simple forms without data to add your data to your 'one' tables (TblEmployee, TblEvent TblFunding Centre in my example) Then use one of these 'one' table as a main form and add the links via a combo with your single forms ready to add extra details (Not sure how this would work ?? but ok. I was just trying so hard not to give up on the subform x 2 idea ) Once again thanks for taking the time to help. I started this new post as the first one was way down the list. SignatureThanks Brent > Hi Brent, > Could you check if I've listed your table structure correctly. I read your [quoted text clipped - 122 lines] > > -- > > Thanks Brent Hi Brent. If you need to have a start date and end date for the event (is that what is meant by ConfStartDate, ConfEndDate?) then it would go in the table of events. The reason it might be necessary is if the employee doesn't attend the whole event. It's looking fine as far as I can see. If you are saying that Events are things that happen during a Conference, then you would need to add another level to your database, though an easy one. You need a TblConference whose Primary Key field will be a Foreign Key field in TblEvent. You say: > 'How much of each funding centre has been spent per employee and total > spent.' (This is a report the Manager wants to see as there are some 30 > funding centres so wants to see the breakdown and totals on reports) With the structure below, that should not be a problem. You could have a query based mainly on TblAttendEvent. You would add (without their primary keys) any details from the other tables which you required for the report (so you wouldn't need the date field for instance or the AtEventID field or even the EventID because the statistics required don't concern that but you would want some of the Employee fields from TblEmployee and some of the the Funding Centre fields from TblFunding centre. You could turn that into a totals query and base a report on that grouped first by funding centre, then by employee. Evi > Evi > I hope you can see my responses as I tried to make it as clear as I can. I [quoted text clipped - 202 lines] > > > -- > > > Thanks Brent Hi Evi. Once again thank you for all your help. I have not had time to do any more work on th db over the last few days but will get on it today. I think everything should work now. Do you think my main form being the Employee form and the two subforms will be ok or should I forget it and move forward? It would be great if I could get that to work. That should be all for now and again thanks to all SignatureThanks Brent > Hi Brent. If you need to have a start date and end date for the event (is > that what is meant by ConfStartDate, ConfEndDate?) then it would go in the [quoted text clipped - 274 lines] > > > > -- > > > > Thanks Brent Don't forget it altogether, but there must be some reason why you couldn't get it to work, so just for now, see if it works OK to do a normal main form and subform. If you can get that working OK, and everything works as it ought, then you can go onto doing the double sub. Evi > Hi Evi. Once again thank you for all your help. I have not had time to do any > more work on th db over the last few days but will get on it today. I think [quoted text clipped - 284 lines] > > > > > -- > > > > > Thanks Brent Your right Evi, I tried it again by building a new db and it still didn't work. What main form and subform would you suggest I start with? I would like to start with the employee form as the employee name and employee number seems like the natural place to start. Would the AttEvent subforms be OK? I would need to figure out how to get the other fields on the employee form ie. funding and event info. Thanks for all your help SignatureBrent > Don't forget it altogether, but there must be some reason why you couldn't > get it to work, so just for now, see if it works OK to do a normal main form [quoted text clipped - 275 lines] > > > there > > > > > into a Lets see if we can pinpoint what is going wrong. If you don't have any data in your db, add a dummy employee to TblEmployee. Add a dummy event to TblEvent. An AttEvent subform would list how many events that employee attended so you could certainly start with that. 1. Click on TblEmployee then drag the (closed) TblAttEvent from the db window onto the newly created form in Design view. 2. The wizard ought to kick in. (if it doesn't, ensure that the Wizard button is pressed down in the Form's design view (looks like a wand) Is this bit happening? Evi > Your right Evi, I tried it again by building a new db and it still didn't > work. What main form and subform would you suggest I start with? [quoted text clipped - 287 lines] > > > > there > > > > > > into a Yes, this is happening. After the first record the tab order on the subform goes right to the last field but the Master and Child links on the subform are linked to the EmpID which is right. We will need to get the event and funding tables in on this. Should we stay on this post or start a new one? It takes me about 1/2 hour to get here. Is there a faster way to get onto Microsoft? This is the only web site it takes so long to load. SignatureThanks Brent > Lets see if we can pinpoint what is going wrong. If you don't have any data > in your db, add a dummy employee to TblEmployee. [quoted text clipped - 272 lines] > > > > > > > EventID (Linked from TblEvent) > > > > > > > RegCost (how much it cost that employee to register for the >Is there a faster way to get onto Microsoft? This is the only >web site it takes so long to load. Yes. The webpage is slow, bloated, buggy... and completely unnecessary. You can use Outlook Express or Windows Mail as a Newsreader; set the news server (under Tools... Accounts) to msnews.microsoft.com, and post to the newsgroup microsoft.public.access.tablesdbdesign. SignatureJohn W. Vinson [MVP] Hi Brent, you need to open the Subform by itself (not the main form) in Design view to set the Tab order. Open the subform in Design view, add a combo based on TblEvent to it, so that you can choose which event the employee is attending. (it needs EventID and whatever fields you need to identify an event) Is everything else working OK on the form - enter dummy data to your tables if you need to. Does it change records to show only records for that Employee, can you add more than 1 record to it. Can you add a new employee to it? I access this ng using Outlook Express and Newsgroups. It works pretty well. If I want to search for a topic, then I go online. By all means start a new thread - the more input you get, the better. Include your db design, listing your tables, and if you have a problem, state exactly what is happening Evi > Yes, this is happening. After the first record the tab order on the subform > goes right to the last field but the Master and Child links on the subform [quoted text clipped - 283 lines] > > > > > > > > EventID (Linked from TblEvent) > > > > > > > > RegCost (how much it cost that employee to register for the Evi Yes everything else is working OK on the form. I have entered dummy entries and it seems to work fine. The tab order in the design view on the subform only is correct, however the tabbing still does not work properly. It may have something to do with tabbing from field to field on the form and it goes to the next row on the first records subform then to the second record and then to the last field on the subform. We can work on this issue later if you think it doesn't have anything to do with anything else we are trying to do.. Now, when it comes to adding the combo box to the subform in design view, the wizard will not allow me to look up the value I want in a table or query. When I select the fields I want in the combo box it said the data can not be retrieved from the source I have selected. I must select a different table or query. When I try - I will type in the value and I add the combo to the form and type in the value I get it in 3 rows. Any thoughts??? Thanks to you and John using Outlook Express works like a charm. Brent > Hi Brent, you need to open the Subform by itself (not the main form) in > Design view to set the Tab order. [quoted text clipped - 376 lines] >> > > > > > > > RegCost (how much it cost that employee to register for >> > > > > > > > the Typing in the data into your combo is not the answer. You need to know why the Wizard won't allow you to use that query or table combo. Does this happen when you use a table for your combo's source? Possible reasons: You have chosen a query which doesn't work properly You have selected the fields without selecting the unique primary key field You are using the wrong table as the combo's source eg you want to retrieve a list of Employees but instead of using the Employees table where each employee is only mentioned once per record (a 'one' table) you have chosen a table where each employee has several records such as TblAttendEvent, You have chosen a query rather than a table and have chosen one into which you have put 2 Primary Keys. I can't imagine why your tab order isn't working in your subform. Has anyone else got any ideas on this one? It sounds as if you are saying that it actually misses out some of the fields before going on to the next record. Is that right? As a long shot, open your form in Design View. Click below the form and click Properties. On the Other tab, check that it says All Records next to Cycle. On the same page, check the Tab Index for each field (the first field should be 0, the next field 1 etc. Something doesn't add up. Is it possible that in an effort to make your explanation of your db more userfriendly, you have missed out information about it which may be relevant? Evi > Evi > Yes everything else is working OK on the form. I have entered dummy entries [quoted text clipped - 399 lines] > >> > > > > > > > RegCost (how much it cost that employee to register for > >> > > > > > > > the Hi Evi I have made some notes below in your reply from yesterday This is the table structure, does it look OK? The main form is Employee with subform AttendEvent. Now I and trying to add a combo box from TblEvent into my subform. Right? Do I need to have data in the table event to add it as a combo box? i.e. pre load ConfNames? If so I don't know all the ConfNames now. I have used combo boxes in the past on forms (per loaded with data) and they worked fine. If you areTblEmployee > EmpID (PK Autonumber) > EmpFirstName [quoted text clipped - 9 lines] > this about? (A text field for notes if another funding source will pay a > portion of the costs. Another program looks after this part of the funding if > it applies) > [quoted text clipped - 5 lines] > RefundDate (date by which refunds must be claimed) > (but no start and enddate for the event???) This table is correct. The start > and end dates are in the TblAttendEvent table.( This is true) (I just found > out I need a confStartDate and a ConfEndDate added, this was a request from > another user. Where will these fields go?) > [quoted text clipped - 9 lines] > ReturnDate (the date the employee returned from the event > DateBooked date Employee was booked to attend the event (I need this field) > DateAttended (date Employee attended the event - or else just use a yes/no > field) (I need the Date) > FundAmount (because you said that the centre funds an individual employee > rather than giving a lump sum to be divided among several Employees (I don't > have this field anywhere in the db and yes the centre funds an individual) > > However, when you say that you need to know > > 'How much of each funding getting tired of this just let me know as I > appreciate all you have done for me so far and it is taking up your time. TblEmployee EmpID (PK Autonumber) EmpFirstName EmpLastName EmpNumber (a company number assigned each employee) TblFunctionalCentre FundingID (PK Account Functional Centre Org Other Funding Source (A text field for notes if another funding source will pay a portion of the costs. Another program looks after this part of the funding if it applies) TblEvent EventID (PK) ConfName Venue Presenting RefundDate (date by which refunds must be claimed) DateBooked DateAttended DateReturned TblAttendEvent AtEventID (PK) EmpID (Linked from TblEmployee FK) FundID (linked from TblFundingCenter FK) EventID (Linked from TblEvent FK) RegCost (how much it cost that employee to register for the event) TravelCost (How much it cost the employee to travel to/from the event) AccomCost (How much the employee had to pay for accomodation TravelDate (I have this field) (date employee traveled to event) ReturnDate (the date the employee returned from the event > Typing in the data into your combo is not the answer. Should I have the > data pre entered? If so I don't know what the data is? [quoted text clipped - 492 lines] >> >> > > > > > > > RegCost (how much it cost that employee to register for >> >> > > > > > > > the > TblEvent > EventID (PK) [quoted text clipped - 5 lines] >DateAttended >DateReturned Seems like this should be two tables: Events, EventsBookings. Events (EventID, ConfName, Venue, RegFeeAmt,RefundDate, StartDate, EndDate) EventsBookings (EmpID, EventID, FundID, BookedDate) EventsAttendance (EmpID, EventID, FundID,RegAmt, AccomAmt,TravelAmt,TravelDate,ReturnDate) It is not clear how FunctionalCenter works. A functional center has one account which is charged for all travel costs. All costs are lumped into one account. Thanks Michael for taking the time to help. Are the pk and fk correct below? It is not clear how FunctionalCenter works. A functional center has one account which is charged for all travel costs. All costs are lumped into one account. (Yes this is correct, then the employee is reimbursed for the expense. However, there are some 30 functional centers from different departments that are used. The idea is to be able to trace the employee, what event they attended, when, where etc. then the manager wants to run reports showing employees attended what event etc. and what functional center will pay and how much has been charged against the center). Hope this is not confusing. Problem that I have been working on with Evi on is that I use the employee form with a subform AttEvent. We want to add a combo box to the AttEvent subform and the wizard will not allow me to add it and of course the tab order does not work right.(don't know why). My question is, how do I tie this data together on a form? What are the relationships? If more info is required please let me know. Any help is much appreciated. Events EventID-PK ConfName Venue RegFeeAmt RefundDate StartDate EndDate EventsBookings EmpID-FK EventID-FK FundID-FK BookedDate EventsAttendance EmpID-FK EventID-FK FundID-FK RegAmt AccomAmt TravelAmt TravelDate ReturnDate >> TblEvent >> EventID (PK) [quoted text clipped - 21 lines] > which is charged for all travel costs. All costs are lumped into one > account. >Thanks Michael for taking the time to help. Are the pk and fk correct below? > [quoted text clipped - 68 lines] >> which is charged for all travel costs. All costs are lumped into one >> account. see http://www.psci.net/gramelsp/temp/Subform_Example.zip look at Form1 as that is the normal way to link subforms Michael, thank you very much for taking the time to create this wonderful subform example, it's exactly what I've been trying to accomplish. I will use this as an example for all my future access db projects. I'm not sure why I couldn't get to this point but now I can use your example and work things out. I would like once again to thank Evi, John, Jeff and you for your time and patience helping me through this process. I've purchased a new book "Database Design for Mere Mortals" as suggested in a pervious post and hope that will help with some of the design issues I have had in the past. I will continue to visit this newsgroup and take advantage of all the great support there is on this site. Thanks to all Brent >>Thanks Michael for taking the time to help. Are the pk and fk correct >>below? [quoted text clipped - 76 lines] > > look at Form1 as that is the normal way to link subforms Brent Does that mean you aren't getting the answer you wanted, or you are getting an answer that you don't understand? If your question is about forms and subforms, the forms-related newsgroups might be a more appropriate. Your description covers a lot of "how" you are trying to do this. Can you step back for a moment and describe the "why"? If you were to explain what you'll be able to do (and why) to an 80-year old grandmother, in terms she'd likely understand, how would you describe it? SignatureRegards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ > I have started a new post after posting in my old one. I'm not sure this the > right way to do this as the old post has worked its way down the list and [quoted text clipped - 20 lines] > > Any help would be appreciated Jeff Thanks for taking the time to respond. I think you make a good point regarding the "how" and describe the "why" and will try to take that aproach next time. SignatureThanks Brent > Brent > [quoted text clipped - 41 lines] > > > > Any help would be appreciated
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.