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 RepliesHi Guys and Girls.
I have about 100 or so tables that I need to append back into one table :eek:
However all of these 100 tables all begin with the number 100 at the beggining. I know that you can append tables - but as far as I know you can only do one at a time. Is there any easy way to do this - for example writing a small SQL statement - saying select all tables that begin with 100* and then append into a master table :confused:
Alternatively are there any programs on the net that can do this (I just wrote a massive macro to import them from a text file in to access- not realising this problem would happen!) :(
Unfortunately each of the tables has the first row as the column heading too?
Any help would be much appreciated!!
Cuurently using Access 97 though.
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.
I am making a database and I require a box in a form where when I press a key, it enters the drink name, price, and stockID. It should also keep a running total of the price and make a subtotal where I can then input the cash given.
Can anyone help? How do I make the box?
Thanks,
Fred
I'm wondering if there is a way in Access 2013 to - for example - enter a value in a date field and then highlight it and drag it down to populate all the records in the table with that value, as can be done in Excel?
View 2 Replies View RelatedI have three account types, 110 fiscal months, seven types of fees each associated with a cost. That's 2310 pieces of data if I enter the information with the collumns: AccKey, DateKey, FeeDesc, FeeCost (e.g. For account type one on Fiscal month Jan '11, an overdraft fee was $10). I have all the data on an excel sheet, but it is formatted so that each fiscal year is separate. Is there a way for me to enter this info quickly?
View 1 Replies View RelatedHello Ladies & Gentlemen,
I was trying to access this particular MS-ACCESS file which serves as a DB for a software. But the file opens and closes in a split of a second and was impossible to view its structure. :mad:
I beleive it's made to respond so by the programmers in order to hide the content. Can anyone please help me open this file? PLEASE...
Teddo
I have a file with 45 columns and I need to be able to make 40 columns quickly dis-appear and re-appear. When I search for responses to similar questions I get references to "Horizontal scroll bar and List Box control" which I do not know how to find and/or use.
View 4 Replies View RelatedI have Access as front end and SQL Server as back end. The Access application is placed on a common network drive for theusers to access it. The compact on close option is enabled when the access application is closed.
I have a local copy of the access app. When I close the app, it takes ony a few seconds. But when on a network drive, it is taking 2 mins.I just wanted to know if the time taken to close the application on the network drive can be brought down without disabling the compact on close option.
Situation
My database stores course information for faculty in my department and prints out various reports based on that information.
The 2 mandatory fields in the form are [AcademicYear] and as they create the potential for classes to be added to the curriculum.
Question
Is there a way to add multiple records to the db based on the which year and instructor combinations were use prior?
[I]ex.
2007/2008, prof-a
2007/2008, prof-b
2007/2008, prof-c
2007/2008, prof-d
This represents the 2007/2008 school year where professors a through d taught courses. Is there a way to add records for another academic year so that:
2008/2009, prof-a
2008/2009, prof-b
2008/2009, prof-c
2008/2009, prof-d
appear with just the press of a button?
I don't know if I'm being clear enough. :p
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.
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.
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.
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 RelatedI 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
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)...
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
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 Relatedwhat 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 RelatedI 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
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
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
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.
I'm using Access 2003 and excel 2003.
We currently manually run 5 different queries then copy and paste this data into 5 separate tabs on 1 workbook, I'm trying to automate some of this process if possible.
I am trying to use the 'transferspreadsheet' action within a macro to run a query and post it into a template excel file, using this code:
Trasfer Type Export
Spreadsheet Type Excel 8-10
Table Name (query Name)
FIle Name (FIle location)
Has field names No
Range Blank
----
This does seem to work and puts the data on a new tab on the specified workbook.
However I have a few questions:
1. Can you specify which query gets put onto which tab in excel? The tabs have different fixed names.
2. Can you specify which Cell the data gets pasted into to? As each tab has a set of headers and titles which need to remain.i.e would need to get query 1 to start in cell A4.
3. How would you expand the above out so that it runs all 5 queries, would you just add in multiple transfer spreadsheet actions in the same macro?
I am trying to add calculations to queries based on columns in the query... it seems to randomly expect 'Expression' or 'Group by' as column types, and Im having to create 3 sets of queries following on from each other to de-dupe data and allow filters on calculated values.
Also I've got a function which turns a date into a quarterly cohort, e.g. Oct 2013 -> 20134. I use ot on a lot of dates. I created a VBA function, CohortQ used as follows in queries:
Cohort: IIf Year([InputDate]) < 1990 or Year([InputDate]) > 2020, 0 CohortQ([InputDate]))
In the VBA, InputDate is defined as a date
Code:
Function CohortQ(InputDate As Date) As Integer
If InputDate = 0 Then
CohortQ = 0
Exit Function
End If
[Code] .....
But when I run it on a date field, it gives me a data mismatch error. I can't step through as it's working on 600K rows.
If I put the function into the query,
Cohort: IIf Year([InputDate]) < 1990 or Year([InputDate]) > 2020, 0 Year([InputDate])*10+DatePart("q",[InputDate]))
it works.
i I have two queries.. What i'm hoping is to combine the result into one query but not in one column only but instead the result of the second query should be beside the first query.. The result of the second query should be added as a new column.
First Query
SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailReceived
FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD
GROUP BY tbl_uSers.UserName;
Second Query
SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailProcessed
FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD
GROUP BY tbl_uSers.UserName, tbl_rEceived_eMail.ProcessedYN
HAVING (((tbl_rEceived_eMail.ProcessedYN)="Y"));