Nested Tables/drill Down Limitations...?
Jun 16, 2005
Hi there,
I'm using access 2000 for starters.
I have created one to many relationships for each of 10 cascading tables dependent on related records in each preceeding table and was expecting to be able to drill down into them in the standard database window prior to creating a data form to work with.
However i have found that tables 1 through 7 are visible, but access doens't give a little "+" in the corner of table 8 to get to tables 9 and 10. i.e, i can't seem can't seem to drill down beyond table 8 to view related records in tables 9 then 10.
Does this mean that there is a limit to how far you can drill down through table relationships within access?
Thanks
Vince
View Replies
ADVERTISEMENT
Jun 16, 2005
Hi there,
I'm using access 2000 for starters.
I have created one to many relationships for each of 10 cascading tables dependent on related records in each preceeding table and was expecting to be able to drill down into them in the standard database window prior to creating a data access form to work with.
However i have found that tables 1 through 7 are visible, but access doens't give a little "+" in the corner of table 8 to get to tables 9 and 10. i.e, i can't seem can't seem to drill down beyond table 8 to view related records in tables 9 then 10.
Does this mean that there is a limit to how far you can drill down through table relationships within access?
Thanks
Vince
View 9 Replies
View Related
Dec 31, 2012
I'm in the process of constructing a student database for my school which would track (in addition to detailed student information) the daily attendance of over 270 students.
It is my understanding that there is a limit of 255 field names per table, so I can't use the student names as field names, nor can I really use dates as this would not quite cover even a year.
Student ID and AttendanceDate as field names is also not really a feasible strategy, as each week this would eat up 1300+ rows, so I would run out of space in that direction as well.
My question is ultimately am I better off building this in excel 2010, or is there a way of doing this in access that I have overlooked?
View 4 Replies
View Related
Jan 5, 2008
I'm at a place in my design where I don't want to go further without advice for fear of really making a mess.:o
I have Products, Customers, Customer Locations, and Customer Models that can have any number of combinations. I've joined what I think makes sense at the lowest level, customers with locations, and next model with customers/locations.
I think products is the outer most junction, because many customers can use the same product in any number of models and locations.
The attached diagram shows where I am now--multiple junction tables, and junctions of junction tables. Now each of these tables has other relationships to nomalize things, but those are not shown.
Is one more level, or nest if you will, the right way to do this?
View 8 Replies
View Related
Jul 31, 2015
I need to output my Access Database as one XML File. My issue is I need to have two tables mapped back to one table. When I try to nest them, one table is nested as it should be, but the other one is just added on the end of the file; not nested like I need it to be.
Board Stacks is the upper end Table. Calibration and Station_Captesters needs to be nested inside it. On top of that, I also want to add they lower two tables in series.
View 4 Replies
View Related
May 25, 2014
I have three tables, tblPeople, tblAddresses and tblVehicles. I have a form called frmPeople that shows a single person record, and has subforms with the list of addresses and vehicles for that person (each record has several). I want to create a single search form with each of the fields in my tblPeople, tblAddresses and tblVehicle tables, and allow the user to type anything into any of the fields on the search form, and have the search form fetch records that match.
So far, I have the query built, and the tables all linked the right way (I think), and I have all the search fields coded. And it works GREAT... except for this one little issue: If I do a search, and I put "Smith" in the last name field, I will get several Smiths in my search result, one for each address and vehicle combination. In other words, if I have ONE Smith in the table, and two vehicles and two addresses for that Smith, the query results give me FOUR Smiths! One with Vehicle1 and Address1, one with Vehicle2 and Address1, one with Vehicle1 and Address2, and one with Vehicle2 and Address2.
What I would LIKE is to have ONE record in the query results, and that record shows me that one Smith. And if I then search for "Plymouth" in the VehicleType field, and I have Smith in the name field, I'll get ONE record, and it will be the one of Smith with his Plymouth (Vehicle2).
Here is how my query looks right now (this one ONLY has the last name and vehicle search function to save space), and this is the one that gives me multiple results (which I do NOT want, unless multiple PEOPLE match the search criteria):
Code:
SELECT DISTINCTROW tblPeople.LastName, tblPeople.FirstName, tblPeople.PersonID, tblAddresses.Address, tblVehicles.Vehicle, tblVehicles.Plate, tblVehicles.VehicleYear
FROM tblPeople LEFT JOIN tblAddresses ON tblPeople.PersonID = tblAddresses.PersonID
WHERE (((tblPeople.LastName) Like "*" & [NameField] & "*")
AND ((tblVehicles.Vehicle) Like "*" & [VehicleField]));
Did I write the query wrong?
View 4 Replies
View Related
Jun 16, 2006
How can i do the following? In a combo box, how can i type in say PEPPER and see every formulaName that contains Pepper in their description. I would get back Red Pepper, Orange Pepper, Black Pepper, Pepper, to choose from. Notice that Pepper can be anywhere inside the name.
I would like the choices change as i type. In other words, if i type BL, i would start to see choices like Black Pepper, Blue Pepper, Roger Blueboy, Green Bloak or any formulaname containing BL.
I presently have the following combo box. if i start typing BL and hit the down arrow, i will see formulas starting with BL. unfortunately, furtherdown, i also see all the other thousands of formulas in the base; meaning I see things that start with C, D, E etc.
I was hoping that i would see every formula containing the letters that i type, As I Type. so if i went further and typed BLAC, the formulas that almost made the cut, disappear from view, leaving me with things having BLAC in name, anywhere in the formulaName. Is this possible? I kind of want to DRILL down. when i finish typing black, i would have all formulaNames that have the word Black somewhere.
here is the combo box after update code:
----------------------------------------------
Private Sub CboFormulaNameFilter_AfterUpdate()
If Me![CboFormulaNameFilter] = "<All>" Then
DoCmd.ShowAllRecords
Else
DoCmd.ApplyFilter , "[FormulaID] = Forms![frmFormulaMain]![cboFormulaNameFilter]"
End If
End Sub
------------------------------------------------
============================
Here is the row source sql code:
SELECT DISTINCT tblFormulaMain.FormulaName, tblFormulaMain.FormulaID, tblFormulaMain.Description, tblFormulaMain.FormulaStatus
FROM tblFormulaMain
GROUP BY tblFormulaMain.FormulaName, tblFormulaMain.FormulaID, tblFormulaMain.Description, tblFormulaMain.FormulaStatus
ORDER BY tblFormulaMain.FormulaName;
===============================
FormulaID is a text field.
thanks
Penwood
View 13 Replies
View Related
Oct 1, 2004
I have a Summary report and a detailed report in MS Access. Upon clicking one of the records in the Summary report should open the detailed version for that record only. Lets say the Summary report has a list of employees. Upon clicking an employee record/link in the summary report should open the detailed report containing the details for that employee alone.
View 1 Replies
View Related
Dec 6, 2006
Hello, I am wondering if there is an efficient way to create an explorer style drill down for a location/equipment table I have
i.e.
-Dept
+Finance
+HR
-Quality
+Supplier
+QA
-General
+Etc1
-Etc2
+Production
I have used the tree control to acomplish this but the major drawback I have found is it becomes WAY too slow when utilizing large amounts of data due to many embeded loops. Does anyone know an alternative or a way to program the tree control to only add nodes once a selection is expanded?
Basically I want this to operate as a drilldown of infinate levels with the speed of windows explorer. Unfortunatly all the code I have seen requires that all nodes are designated/Created when the form is loaded.
Any thoughts are greatly appreciated
View 3 Replies
View Related
Oct 6, 2005
Does anyone know what the maximum rows & columns are for a crosstab query as I cannot find them anywhere?
When I create one that is too big, it just gives the message of how many columns I have rather then how many I'm allowed.
Any help would be greatly recieved.
Regards
Carly
View 3 Replies
View Related
Mar 4, 2008
Hi,
I have a unbound textfield which is defined as a MEMO as i need the capacity the memotype gives. When i use DLookup to retrieve text from a tablefield it seems like the DLookup function can't retrieve all the text from it. The capacity is there when i use bound relations (not using DLookup), but not with it. Does anyone know how i can get around this?
Thanks in advance :)
View 2 Replies
View Related
Dec 27, 2005
Can a combo box, used to auto fill fields on a form, use a memo field? My selections seem to be cutting off at the 255 character limit, eventhough the underlying table is set to memo field. Is there a work-around for this issue?
View 14 Replies
View Related
Oct 28, 2006
Hi,
My website keeps going down. We run an Access database with ASP coding. I have been told that the problem is from Access having limitations. I read in another posting here that there is a limit of 255 concurrent users, something that we would reach.
I want to know what is the best way forward. Would I need to upgrade to MS sequel? Could I switch to MYSQL? What is the best option and how would I go about doing it? Any other tips or pointers would be much appreciated.
Thanks in advance.
View 1 Replies
View Related
Mar 26, 2005
I have a form with a memo field. This field is based on a table with a field set as a memo data type. The problem is that although the form field lets you type endlessly, it only saves 255 characters. A memo field is supposed to let you enter up to 64000 characters. Has anyone else ever had this problem, if so what was the solution?
View 1 Replies
View Related
Apr 21, 2006
I've got a database that is about to blowup with Access' 2GB limit. Just wondering if I should wait for Access 12.
View 2 Replies
View Related
Feb 13, 2005
Hello All,
I just had a couple quick questions. My first question is access limited
to a certain amount of records per table, or limited on how big (disk space wise)
a database can be. The reason why I asked was I think I read access 97 had a
limitation on the db could be no more then 2 gigs, or so many records per table, or both.
Thanks
View 2 Replies
View Related
Apr 24, 2008
Hello everybody
My office gave me assignment to find out the scope & limitations of the two Microsoft database applications - MS Access and MS SQL Server. After the findings, we have to chose the more appropriate database for our new project, which will have a humble start but may grow big in a year or two.
I will apreciate the info in facts & figures (not only in general).
View 7 Replies
View Related
Sep 25, 2006
We are planning a database application for a client that will need to be accessed remotely by their consultants. We will initially host this application on one of our servers for a few months, and then the client will take it over and host it. The problem we are facing is that this client wants to use ms access as the database, and doesnt want the interface to be web based. The plan was to use data access pages, and let their consultants use a remote desktop connection to access these pages. I don't use access, but I was under the impression that access couldn't really support multiple users hitting the database in this format. Is this correct?
Thanks,
Greg
View 5 Replies
View Related
Mar 24, 2006
Hi,
I would like to use an Access database over an Intranet. What are the limitations of doing this? I want multiple individuals to be able to open forms and enter data. I would also want others to be able to run queries and reports on the database. Would I need to upsize to an Oracle database to run with multiple users? Any words of wisdom would be appreciated.
View 4 Replies
View Related
Sep 9, 2005
Hi
I have just designed a central training database to aid in the running of our training department.
One section is for an IT course which contains 8 modules.
The tests have the following IDs
Mod 1: ID05, ID06, ID07, ID08
Mod 2: 2.1, 2.2, 2.3, 2.4
Mod 3: 3.1, 3.2, 3.3, 3.4
Mod 4: 4.1, 4.2, 4.3, 4.4
Mod 5: 5.1, 5.2, 5.3, 5.4
Mod 6: 6.1, 6.2, 6.3, 6.4
Mod 7: 7.1, 7.2, 7.3, 7.4
Mod 8: 55, 56, 57, 58
One person may take any of the tests.
If someone completes Mod1, 2 and 7 they achieve Level 1
Similarly if someone completes Mod 3, 4, 5, 6 and 8 they achieve Level 2.
I need to run queries to find who has completed L1 or L2 or both.
Obviously they only achieve if they pass so in that table there is a status field with
Pass
Fail
Wait
As the options
Queries have never been my strong point and I was hoping someone here could help me.
I’m using XP Pro, with Office 2003 and the office 2000 db format.
Many thanks
Dazzy
View 1 Replies
View Related
Oct 26, 2006
Hi
how would one go about nested IIF
New Sal : IIf([emplsalaryaa]>80000,([emplsalaryaa]+([emplsalaryaa]-80000)/(100)*9)+7713.98)
i have the above but i need to put one more
iif
i have tried , IIF but it does not work
i don't know why?
Asad
View 3 Replies
View Related
Jun 8, 2007
My first post ever... so here goes.
My Goal:
Columns of data contain date fields & "Status" that indicates where a case lies. I'm trying to calculate days spent in a specific area (determined by status code) and total amount of time spent there all together. Status code begining w/ "I***" means case was sent to an area, w/ "R***" it returned. I have it together up to a point. My problem lies with end of expression where I want to calculate last date at specific location to now. In line 1 it would be from 5/03/07 to now since no r162 follows & blank field follows. Problem is with second IIF - it acts independently instead of giving false value from first IIF... i think.
status1 date1 status2 date2 status3 date3 status4 date4
1) I162 5/3/07 I004 5/20/07 I006 6/1/07
2) I162 4/2/07 R162 4/30/07
My failure:
stat2n4: IIf([status2]="i162" Or [status2]='i062' Or [status2]='i009' Or [status2]='i159' And [status3]<>'r162' Or [status3]<>'r162' And [Status4]="r162",DateDiff("d",[date2],[date4]),IIf(IsNull([status4]),DateDiff("d",[date4],Now())))
View 9 Replies
View Related
Oct 3, 2005
I have placed a tab control within a tab control (which I have done before). The problem is that the sub tab control appears on every page of the main tab form (encountered this before). I fixed it in the past by placing code on the on open event to make the sub tab control not visible unless the appropriate tab is selected. That worked fine.
I have tried this in the new program and find that when I set the value of the main tab form to 4 (the page index of the tab) the sub tab form will not display at all. However if I set it to zero it will display regardless of which tab is selected. Confusion has now set in :confused: . Any thoughts out there on what I have done incorrectly.
[/CODE]Private Sub Form_Open(Cancel As Integer)
'Me.OrderBy = "RankNo desc"
'Me.OrderByOn = True
If TabCtl44.Value = 4 Then
TabCtl144.Visible = True
Else
TabCtl144.Visible = False
End If
End Sub[CODE]
View 5 Replies
View Related
Oct 20, 2006
Poor old Access. I think I've made it wave a white flag, but based on the following expression I am trying to get it to evaluate, I'm not too surprised...
The situation is that I have a large number of tick boxes on a form, which nicely filter through to a series of fields in a table whose values are either -1 or 0 (according to if the box is ticked or not). Unfortunately, I now need to generate a small piece of text for a report based on which boxes have been ticked. Deep breath...
institution:
IIf([people].[memctf]=-1,"CTF",(
IIf([people].[memwt]=-1,"Westcott",(
Iif([people].[memwy]=-1,"Wesley",(
Iif([people].[memry]=-1,"Ridley",(
Iif([people].[memwm]=-1,"Westminster",(
Iif([people].[memiocs]=-1,"IOCS",(
Iif([people].[memermc]=-1,"ERMC",(
Iif([people].[memmbi]=-1,"Margaret Beaufort Institute",(
Iif([people].[memcjcr]=-1,"CJCR",(
Iif([people].[memcym]=-1,"CYM",(
Iif([people].[memindepba]=-1,"Independent BA",(
Iif([people].[memhmc]=-1,"Henry Martyn Centre",(
Iif([people].[memregchelm]=-1,"Regional (Chelmsford)",(
Iif([people].[memregnor]=-1,"Regional (Norwich)",(
Iif([people].[memregpboro]=-1,"Regional (Chelmsford)",(
Iif([people].[memregalbans]=-1,"Regional (St Albans)",(
Iif([people].[memdiocedsips]=-1, "Diocesan (Eds and Ips)",(
Iif([people].[memdiocnorwich]=-1, "Diocesan (Norwich)",(
Iif([people].[memreg]=-1, "Regional", "Unknown"
)))))))))))))))))))))))))))))))))))))
The logic seemed to work nicely when there were just 3 options in the list, but when I try to enter the above, Access says "the expression you entered is too complex". Poor baby! :D
Does any one have any idea how I can get it to evaluate the data I need?
View 4 Replies
View Related
Mar 30, 2007
I have a nested IIF that I cannot get figured out. This is what I have so far:
Hours: IIf([Dept]="10",55,Format(IIf([EorDCode]=" 1",[Regular]/60,[OT]/60),"Fixed"))
The problem is that I need another IIF to differentiate between Regular and Overtime. It needs to do this.
If Dept = 10 Then
55 Regular Hours and 0 Ovettime hours
Else
Format(IIf([EorDCode]=" 1",[Regular]/60,[OT]/60),"Fixed"))
View 1 Replies
View Related
Aug 13, 2007
Hi,
I am facing a problem trying to calculate the commission for the order I have within my Access application.
I pull up the information from three tables to determine the commission. Commission is based on Currency then subtotal then percentage for each specific client.
Can you please look at the below query and tell me what seems to be the problem.
If the commission is anything other than USD, CAD, EUR or GBP I will calculate normal commission.
Commission: IIf(Currency="USD" Or "CAD",IIf Quantity>1000,USD_Commission2*Quantity,USD_Commission1),IIf (Currency="GBP" Or "EUR",IIf(Subtotal>100000,Subtotal*EUR_Commission2,EUR_Commission1),I If(Subtotal>10000,Price*Quantity*Commission1)))
View 3 Replies
View Related
