Query Diagram
Nov 2, 2007
Is there an easy way to get an explanation of where each query within the Db gets it's data from?
I'd ideally like to get a hierachical model - be it as a diagram, text, or whatever - along the lines of
QueryABC gets data from QueryD and QueryE
QueryE gets data from Table1
QueryD gets data from QueryX
QueryX gets data from Table2
I've only just needed to look at the existing queries in this Db in any detail and I'm hoping there's some faster way to sort this out, rather than manually opening and listing each one.
Thanks for any ideas.
View Replies
ADVERTISEMENT
Jun 11, 2014
All I have taken over a database. It has dozens of queries. Is there a tool or code that I can use that would quickly diagram the querie like a flowchart telling me what queries are related and the steps and so on.
View 1 Replies
View Related
Aug 4, 2005
Can anyone point me in the right direction to produce an application diagram.
Sort of a map of an app, what data it hold in laymans terms rathers than table and field definitions. What forms do what, and how the whole application fits together.
Thanks for any assistance you can offer.
View 9 Replies
View Related
Sep 24, 2006
Hello I would like to make a table Relationships diagram to enforce referential integrity.
I wanted to make this diagram in the Back End container but I realized if I want to delete a corrupt table I have to delete the relationships as well.
(My clients might need to delete an individual corrupt table and replace it with a previous copy. This will cause the relationship to be removed and I don't expect my clients to re-establish this relationship.)
My question is: What if I put the relationship diagram in the Front End container? Will this enforce all the integrity rules? I assume if I put the diagram on the front end ... the user can replace tables in the Back End and the relationships diagram will be unaffected on the FE.
Any input on this topic would be appreciated thank you.
View 3 Replies
View Related
Jun 20, 2007
Hi i'm building a database with access for a computer business which sells computer products. The database is going to be from the point of view of the business not the customer. I'm having some trouble with the Relationship diagram, so far i have done this http://img509.imageshack.us/my.php?i...atabasefa1.jpg
The aim of the database so for the customer to purchase product, and the business records this transaction. Its from the point of view of the business not the customer. The business uses a supplier for its products and has no physical stock on hand.
View 8 Replies
View Related
Dec 15, 2005
Hello forum,I am new to designing databases and am excited to learn everything I can. I have read about normalization and relational structures and just need some assurance that I’m on the right track. I’m not looking for someone to design this database for me but am looking for someone to help critique what I have so far which is some user requirements and a RSD. My RSD is attached below.What I am building is a database to track carpets that have been cleaned in rooms which are in buildings. Employees clean carpets in rooms in a building. Many employees can be assigned to a carpet cleaning project. Employee’s record which building they are in, what room's carpet they are cleaning, what day and time they started and ended cleaning, what materials and method of cleaning they used, and the reason for the cleaning. Depending on the reason, the customer is charged a certain rate per hour of carpet cleaning. I am hoping to be able to query from this database to be able to answer, when the last room was cleaned, how long did it take to clean a room, and how much did it cost to clean a room. Some questions I have are: What attributes should be included in the PROJECTDETAIL table and not in the PROJECT table? When should I create a table showing DETAILS of an entity? How do I know when I should have an associative table or correlation table? Am I on the right track in creating this database? Thanks for your help!
View 2 Replies
View Related
May 25, 2005
I need some help. I have to design a relational database for a small bookstore that operates 3 stores.Books are sold by a sales person to a customer through an order. A order may consist of one or more books but assume that for a given order, all the books sold are all from one store and are sold by one salesperson.
Field name
Address
Author ID
Author Name
Book ID
Book Type
Book type description
City country
Cust ID
Cust name
Cust Type
Cust type descr
Email
On hand
Order date
Order no
phone number
phone typr
phone type descr
postcde
publisher ID
Publisher name
quantity
salesperson ID
Salesperson name
Salesperson Phone
Selling Price
Store Address
Store City
Store email
Title
How do I start the database
View 1 Replies
View Related
Feb 25, 2006
I am developing one software in access 2000. Now where i stuck up is ... Customer is asking for " process flow chart" (steps in block diagram). The requirement is as below..
For a pen there are below operations
1. moulding
2. cleaning
3. polishing
4.assembly
Now these flow of operation is to be presented in block diagram. Please suggest me how do I do this in accesss? (in form or report)...
View 3 Replies
View Related
Jun 4, 2006
hi everyone, this is first post, i have this task and i think i have the solution but im not sure, so plsplsplspsssssssss someone help!!!!
its called class scheduling. below is the physical model. i need to make the logical, i have been told that course and section share a many to many relation as well as section and faculty and also bwee/ faculty and course, thus the creation of the intersection tbl, the OFFERINGS TABLE. i have inserted the foreign keys (FK's) into that table (which are the primary keys for the 3 main tables). what i need to know is that:
1. do i need anymore FK's?
2. wen i make my logical diagram, do i show it like the way i have shown it in the word file? i made it as such that course and section have the PK of the 2 tables and are now FK which allow duplication ie 1 course can have many sections etcc pls helpppppppppppp
Courses table
CourseID (PK)
Title
Credits
Sections table
SectionsID (PK)
Days
Time
Faculty table
FacultyID (PK)
Firstname
Lastname
department
Title
FullTime or partime
hiredate
Offerings table (intersection table)
OfferingID (PK)
CourseID (FK)
SectionID (FK)
FacultyID (FK)
Building
Room
View 1 Replies
View Related
Aug 21, 2013
Using access 2010; anyway to create an er diagram from an existing database? I have inherited a fairly large database and need to make changes to some field properties and need to find out where these fields are located. I know I can go into tools and run the documenter but doesn't give me an easy format to look at.
View 5 Replies
View Related
Nov 16, 2013
what means a table to appear as an entity on the diagram? And how to identify the cardinality of a relationship and how to specify which is mandatory and which is optional.
View 5 Replies
View Related
Oct 17, 2014
I would like to do two things
1.) pink arrow // I've changed the titel in a diagram with vba ... is there also a methode to move the position of the titel with vba ( left-center or rigth ? )
Private Sub Form_Current()
' KostenHinweis im Diagram
Me!Dia_WS.Object.ChartTitle.Text = _
"Preis: " & Me.[Roh-Preis] & "€/kg " & _
" Mindestbestand =" & Round(Me.MindestBestand * Me.[Roh-Preis], 0) & "€ ; " & _
" Maximalbestand =" & Round(Me.MindestBestand_Max * Me.[Roh-Preis], 0) & "€"
'Me!Dia_WS.Object.ChartTitle.Left ???
End Sub
2.) red arrow // maybe its because of the titel placement.I would like to change the size of a diagram with VBA code to have the diagram fit nicely to a given space best thing would be if there would be a possibility to resize the diagram in the given space
View 3 Replies
View Related
Mar 21, 2008
Hey people, hows it going? Quick question
I am creating an EPOS system for a bookstore and I have a many to many relationship between the transactions table (tbl_transactions) and the products table (tbl_products) using a link table (tbl_linktblproductstransac). The below diagram shows what i mean below:
http://img512.imageshack.us/img512/3246/relationnshipswd9.jpg
I made it many to many because 1 transaction can have many products on it and 1 product can be on many transactions.
Now what I need to know is how will i make records with more than one product on one transaction (in the table itself or in a transaction form). I would really appreciate if someone could help. Thanks :) and have a good Easter
View 2 Replies
View Related
Feb 9, 2006
Hello : )
I am currently taking a database design class using Access 2003 and am looking for some help. I am trying to draw a dependency diagram before I create the tables based on the following attributes / fields in one table named Student:
StudentNumber, StudentName, StudentMajor, student's AdvisorNumber, AdvisorName, AdvisorOfficeNumber, AdvisorPhone, student's NumberCredits and student's Class (freshman, sophomore, etc.)
So far I have identified the Primary Keys as StudentNumber and AdvisorNumber and this is what I have:
Table #1 - Student = Student Number functionally determines Student Name, StudentMajor (?) NumberCredits and Class
Table #2 - Advisor = Advisor number functionally determines Advisor name, office number and phone
The problem I run into is with the the StudentMajor / Advisor / StudentNumber relationship. Since one student can have many majors and therefore many advisors since there is only one advisor per major, and each advisor has many students I am assuming it is a many-to-many relationship.
The problem I am having is determining the third table (and fourth if there will be one) and the relationship between StudentMajor / Advisor / StudentNumber.
Should StudentMajor be included as functionally dependent on StudentNumber? If it is there would be redundancy due to multiple entries in that field for each row so I guess I can't include it actually. :confused:
Wouldn't there have to be another attribute named AdvisorDept for this to work properly....that way there is a relationship between Advisor and their department they work in so I can link the student's major to the advisor :confused:
Any pointers and suggestions would be greatly appreciated
Thanks
View 2 Replies
View Related
Nov 9, 2011
I have upsized from Access 2003 to SQL Server 2008 R2 using upsizing wizard. Everything works fine. But I don't see relationship in SQL SErver 2008 R2 if I go to database. But I set relationship in Access 2003 before upsizing it.
Amso I don't see relationship (diagram) in Access 2003 , which I was able to see before.
So do I need to again recreate the relationship amongst the table in Access 2003 Or SQL SErver 2008 R2 ? I thought, if you link tables, everything should be taken care but i don't see relationship structure any more.
View 1 Replies
View Related
Oct 19, 2004
What is the best way to impliment a query in a form so that the user can view the query records, and have the option to print or save the selected record using command buttons?
I tried subforms but I could not get the command buttons to work in the subform after it went into the form, it wanted to print the entire form instead of the selected record from the subform.
So in a nutshell I have 3-4 queries that are built, and I want to have them show up on my form in a format that the user can scroll through the results and select a single record of the results and then print or save that individual record from the form, if such a thing is possible.
Thanks in advance
Todd
View 1 Replies
View Related
Mar 24, 2013
I am trying to do the good 'ol sales report (query) to include customers with no sales.
I have a customers table, account number table, sales table & sales (line) detail table. (all linked in that order)
If I run a query to show customers (in the customer table) with account numbers, that works
An unmatched query to show customer without an account number works (but of course the unmatched account number field isn't shown).
How can I get the two two be shown together with the "unmatched" having a null or 0 for their account number?
I am guessing in principle, the resulting solution can be modified to show customers without sales alongside those with sales?
View 3 Replies
View Related
Mar 4, 2015
In some cases I create pass-through queries and use these in an Append or Make-table query to bring data locally.
All is well and fine until source data changes and the pass-through query runs too long and times out.
If needed, I can extend the timeout value in the Parameters of the pass-through query no problem, but when I try to open the Append or Make-table query in Design view to do the same, the pass-through query is first triggered and then throws the timeout, and I cannot access the Design view of the Append or Make-table
Is there a way to open an Append or Make-table query in Design view without invoking the source query?
View 1 Replies
View Related
Oct 28, 2005
Hello All,
I'm trying to run a UNION query that joins five queries through a MS WorkSpace into a DAO.recordset in VB. I'm pulling the data from a SQL Server Database through VB in Access. I'm attempting to open a recordset with a query passed to it as a string. The query is below. For some reason, I'm receiving a message: "MS Jet database engine cannot find the input table or query. Runtime Error 3078".
Here's what's puzzling. When I run a single query without any UNION statement, the code finds the table and runs fine without error, but anytime I join two or more queries with a UNION statement in the VB, it gives me the error.
I've executed the same UNION query in both Access Query Builder and SQL Server's Query Analyzer and they work fine in both environments. It's only when I call the query from a DAO.Recodset with VB that it causes this problem. The following is a sample of the UNION query joining two of the five queries. Does anyone have any idea what could be the problem? The following query executes in about 5 seconds so I don't think there's a "time-out" issue. I'm thinking that the UNION statement may be the culprit. Maybe there's another way to approach joining these separate queries? Any help would be most appreciated. Thanks.
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((CategoryID) = 'HCPROD') AND ((BrandID)<>'CSS')) AND (((BrandID)<>'1356')) AND (((BrandID)<>'1400')) AND (((BrandID)<>'1551')) AND (((BrandID)<>'555')) AND (((BrandID)<>'66'))
AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
UNION
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((ProductID) = '0029800')) AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
Set wrkJet = CreateWorkspace("", "pw", "", dbUseJet)
Set db = wrkJet.OpenDatabase("DW", _
dbDriverNoPrompt, True, _
"ODBC;DATABASE=DW;DSN=DW2")
'Set rs1 = db.OpenRecordset(strSQL)
View 9 Replies
View Related
Nov 20, 2013
I am wondering if there is a quicker way to export a query to excel then have the data in that query removed from the original table. (effectively cutting the data from the table and exporting to excel)
I understand that this can be done by exporting the query to excel then running the same query as a delete query to remove the data but I just wondered if this is the most efficient way.
I have experience of VB in excel but currently only use the basic macro builder in Access though if Access VB is more efficient I can easily learn.
View 5 Replies
View Related
Apr 2, 2013
I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.
View 3 Replies
View Related
Aug 12, 2015
I have a query where these are the fields:
ProductRevType
RevLag
RevFlowThru
CloseMoYr
ProjRevDate
CurrentMRC
ProjRevMRC
The ProjRevMRC field is an expression that reads:ProjRevMRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentMRC]*[qry303a_ SFADetailMRC_ONLY]![Rev Flow Through],0)
When I run the query, it works perfectly, but when I created a crosstab query to show totals by month, I wanted the totals to be zero for the months less than the current month. Is there a way for the crosstab query to execute the expression and put zeroes for those months?
View 4 Replies
View Related
Jul 15, 2014
I have a field that is giving me the number of business days between a period of time and then I want to subtract that number - the person's PTO time to see the actual days they were available...when I simply type the number in (see below) it works great but I want to set up a prompt that will ask me how many PTO Days to calculate as it will be different for each person I am quering...is this possible?
View 9 Replies
View Related
Jul 23, 2015
I have a form which will be used as the basis to print a label.
It is bound to a query and when I open the form I pass over a 'where' condition to return 1 record. I then use the query to produce a report/label.
What I want to do is to update the form/query without updating the underlying tables to the query.
View 14 Replies
View Related
May 31, 2006
:confused:
I am trying to help someone with a complex problem (so it seems to me) but I will first ask about what should be a simple thing....
First goal: to COUNT the number of times a TYPE of visit is made.
There are several different TYPEs but only interested in tracking 2 of them.
When a crosstab query is created - if one of the 2 parameters are not "met", a blank is returned. I have been reading posts about using NZ and IIf IsNull, etc to get past that - but none of them make any sense to me and the Access help suggestions do not work. Hope someone can make it clear with this information: (can't give more specifics to keep privacy intact)
The SQL was written by Access not by me. :)
Here is an example of the Crosstab SQL (which is using a previous query):
TRANSFORM Count([qryTest2.TYPE]) AS CountOfTYPE
SELECT qryTest2.CID
FROM qryTest2
GROUP BY qryTest2.CID
PIVOT qryTest2.TYPE;
-----------
qryTest2 SQL: (Grouping by to remove dups)
SELECT DISTINCTROW tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
FROM tblM LEFT JOIN tblC ON tblM.[M#] = tblC.[M#]
GROUP BY tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
HAVING (((tblC.TYPE)="Out" Or (tblC.TYPE)="In"))
ORDER BY tblM.CID, tblM.LNAME, tblM.FNAME;
Thanks for you time! :)
View 1 Replies
View Related
Mar 8, 2005
I'm building a report for annual software license renewals. The report data source is a query that combines the customer information, their computer information, and the licenses purchased for that computer. I am having no trouble with the form displaying the customer info page, then a page with the computer info at the top and a list of licenses purchased for that computer underneath.
That would be great, if that's what my boss wanted. However, she wants the whole list of available licenses displayed on each page, in the event someone want to purchase additional licenses with this year's renewal.
I'm trying to figure out how I can set up a query/report grouping to do that. I've tried making a new query, relating the qryLicense!licenseID to the qryPurchase!purchLicense and setting the relationship option to show all records from qryLicense and only those related from qryPurchase. I added the qryPurchase!purchCPU field to my query, hidden it, and set the criteria to “=1” (the computer ID of one of my dummy computer records). I also have a Sum of the qryPurchase!purchQty field included in the new query that I want to display the total number of that particular license purchased (and 0 if there are no corresponding records). All fields except for the quantity field are set to Group By.
What I’m getting from this is simply a list of the licenses purchased for that computer, not the complete list of licenses available showing the quantity purchased where applicable.
Can someone see where I’m going wrong?
Slaughter
slaughter at mizzou dot edu
View 9 Replies
View Related
