I have taken over exisiting databases and the names for the tables,forms,queries,reports are not technically named correctly.. What would be the easiest way to rename everything accordingly:
tbl for table
qry for query
rpt for report
mcr for macro
bas for module
frm for form.
Of Course they have names but it is hard to tell when viewing a query for instance if you are looking at a table or a query.... in the SQL View.
Im putting up my Access table/query names for critique, because at some point Ill have to hand over this database to someone else and I want to see how they might react. So if you opened a query list and saw the list below, would you be able to find your way around, or are there too many similar looking queries?
Some of the queries are made up of 6 or 7 of the other queries. I find its easier to see what going on if they are separated out into individual queries, rather than throwing them all into a single select statement, and it means I can re-use things that I know work.
I get stuck on using plurals vs singular (e.g. StoresDeductions or StoreDeductions); how much detail to put in (e.g. BrokersChainsStoresCasesByMonth); how to indicate totals or being grouped by e.g. month; how to indicate date parameters, i.e. whenever a query says MonthRange it asks for this range, and any queries built on top of this one also have MonthRange in their name.
Code:
AccountsTotalNumStores
BrokersChainDeductionsMonthRange
BrokersChainDeductionsMonthRange_Crosstab
BrokersChainNumStores
BrokersChainsTotalsByMonthRange_Crosstab2
BrokersChainTotalsByMonth
BrokersChainTotalsByMonthRange
BrokersChainTotalsByMonthRange_Crosstab
BrokersChainTotalsMonthRange
[code]....
I'm not talking about naming conventions as in tblThis or qryThat, but I have 100+ queries, many of which do similar things from slightly different angles - e.g. a query that gives the total sales per store per month; the same query but per store per product per month; the same query but per sales manager per store per month etc. And these are then used in queries further up the tree that also have similar but slightly different results.
So is there an example database anywhere that I can download that has a decent amount of queries, and a sensible naming convention? I think I'm tying myself in knots because I have only a few tables but lots of queries (for data analysis), whereas most databases I see have lots of tables and a handful of queries.
Is there any systematic way of naming Tables, Queries, Forms, and Reports in order to identify easily which of them are connected to each other?
View 1 Replies View RelatedHi
I would like to add to my VB6 app the ability to build an array of what tables are in my Access database, and the fields that are in each of those tables.
I am working with this database using ADODB, but it doesn't give properties like Tables and Fields, for example.
I am happy to open this database some other way, grab this info, close that connection, and proceed using ADODB.
What do you think
Robert
I am very inexperienced in databases and I am currently attempting to design a database and there is one part of the schema where I am unsure how to proceed.
I need to model schools, pupils and teams. Schools have a number of pupils as do teams.
I rushed in with the following design but it just seems wrong to me. I don't know much about database design but I don't like the idea of having a kind of loop/triangle in the schema like this.
School(SchoolID, SchoolName, Postcode)
Pupil(PupilID, School, Team, Forename, Surname)
Team(TeamID, School, TeamName)
I was conscious of having a fan trap of a school has many teams and a school has many pupils.
Is this bad practice or is it fine?
Any guidance or related articles on this would be appreciated.
Hello,
Is there a way to print the schema of a table right out of Access?? Basically I just want to print out all the field names, data types and descriptions. When looking at the table in design view MS Access 2000 does not give you the option to 'Print'.
Any ideas?
Thanks
Brian
We have added new columns to a table in our developer db. Now we need to get these columns into the client's system.
Their computer is running on Access runtime, so if we change our existing Access db into a Mde would it be possible to upgrade their database with the changes without corrupting any of the client's data? We're quite happy to overwrite the forms, queries etc. but what happens to the client's data? especially when we have made some changes to the database schema e.g added new columns to the table?
We'd be grateful for any help.
Hello,
I have some questions about a schema design I am working on. After much trial, it appears to me that a particular table is not and probably does not need to be related to any other table in the schema. So my questions are:
1. Is it right or quite typical for schemas to have some tables which are orphan (i.e. unattached to any other tables)?
2. If tables can exist in isolation, then what are the primary purpose they provide? I have a suspicion that if fields from an orphan table are used in query etc, then the possibility of Cartesian joins will arise. If so, how does one take care of avoiding such a problem?
Thanks very much for the enlightenment.
I have a xml schema which defines all things necessary (tables, field names, relationships, etc) to build a database in access. The question is whether Access could decode xml schema and build the database (create all tables, fields and relationships) automatically by macro or something else. I have tried to load xml schema by using external data in Access. However Access can only recognize some tables, not everything.
View 2 Replies View RelatedHello,
I have a couple of elementar level questions on Form creation process and standards invloving a schema with several M-M relationships. I have a highly normalized design (thereby producing several junction tables). I have attached a picture of my schema to give an ideal of the schema I am talking about.
1. What is typcially the process of creating Forms on such a schema for the purpose of data-entry. Meaning, should there be several Forms (on the order of # of tables) or should there be a few that enables simulataneous data entry to many tables through one Form. What is typically the practise?
2. If there are 3 tables (A, B, C) so that A and C have a M-M relationship and B is a junction table that makes A &B and A & C have a 1 - M relationship, then how does one enter data for A, B, and C so that all the three get populated?
3. Can someone provide a copy or link to a sample database that has a similar normalized schema and Forms built into it that takes care of the data entry to the various tables?
Thank you very much for some light on this path.
How do I change all the currency fields (many many of them....)from $ to currency without going in and changing every form and fields manually.
View 3 Replies View RelatedWe are currently using Access 2007 for all our database needs but so many different people have had access to changes things that now they are so convoluted that my boss is wanting me to build a brand new database... Is there a way to take all the tables in one database and migrate them over to the new database that I am building?
View 3 Replies View Relatedhow am I suppose to change the form name in the forms in VBA access, now see I would not want to change the names by using the coding.
Please note that I have got so many forms, however I have worked with the default names to start some of them would be, form1, form2 Now, when I am trying to follow the best practice approch, by changing the form name by right clicking and hitting rename, I am unable do so as it is popping up and giving me a statement as below.
"The form name 'Form2' is missspelled or refers to a form that does not exist" and continued in the next line as below
"if the invalid form name is in macro, an Action failed dialog box will display the macro name and the macro's arguments after you click OK. Open the Macro window, and either the correct name."
I'm having a problem changing the hyperlinks in my access table.the hyperlinks point to locations of various documents on the server i.e. Server Quality DocumentsDoc1.docx
we have 2 domains at work so on one domain the hyper link works but on the other it doesn't.I did a find and replace so i have formatted all hyperlinks as such..Server.mydomain.localQualityDocumentsDoc1.doc x
However, this approach hasn't worked as it has only altered the 'text to display' and not the actual address (if i right click the hyperlink -> edit hyperlink the address is still ServerQualityDocumentsDoc1.docx).Is there away to change the Address fields of all the hyperlinks in one go?
I have a small Access DB (multi-user) on a network that keeps turning into a read-only DB, with a message bar saying please save as . . . and it will not let you complete that. It seems to lock the application, and when I can get everyone out and copy the file out I can finally get it back to its normal state.
View 3 Replies View RelatedI want to change text in txt boxes. when a new customer is being created we are too lazy to put uppercase in. so i want to change the text so the first letter is uppercase. i have used this on every event and it doesn't change a thing.
PHP Code:
Private Sub txtTown_AfterUpdate()LResult = StrConv("TECH ON THE NET", 3)End Sub
Private Sub txtPostcode_AfterUpdate()LResult = StrConv([txtPostcode], 1)End Sub
Private Sub txtStreet_AfterUpdate()LResult = StrConv([txtStreet], 3)End Sub
Private Sub txtFirstName_BeforeUpdate()LResult = StrConv([txtFirstName], 3)End Sub
I have this at the top
PHP Code : Option Compare DatabaseOption ExplicitDim LResult As StringDim LResponse As Integer
In the past I have only done data input into Access databases that have already been created. They have been simple text fields only. I was hired on contract to finish data input for a database that serves to catalogue material resources. The database itself was supposed to have been completed by a former contractor but I'm discovering bits and pieces that weren't finished. I don't have any background in IT or coding.
So, this database eventually has to be shared in a "locked" format so that other entities can view our resources (like a catalogue) without changing them. My next step is to add images to each of the items being catalogued (there are about 100 images). The former contractor began a table labeled "Pictures" in which there are 2 columns: PictureID and PicturePath, the latter being a path starting with C:. The issue is, his picture paths include the hard drive name so that it looks like this:C:HardDrivePicturesPictureID.jpg
I need the database and its images to be share-able on other computers. And I need to use relative paths to connect the images to the database so I'm not tremendously increasing the size of the database.does the location/path of the database need to be changed so it can be shared, and if so how do I do that?
Second question: I have all the images I will be using in a folder on my desktop; how do I save them in a "fixed subdirectory" of the database so that they get moved with it whenever it is sent out to another user?
If I change the database name as I'm working on it (I've been saving a new database each day, so that if I screw up something I can go back to the most recent working one) will that mess up my picture paths?
I'm altering a database to have certain fields be recorded monthly.For example: instead of a client's file having "Total X Purchased" it would now be "Total X Purchased - Jan", "Total X Purchaed - Feb".
I want to spin the monthlies out of the master clients table & in to ex. tbl_clientsJan, tbl_clientsFeb, etc. but still have them linked; & have a "Totals" table that aggregates data from all of the tables (adds them up).
I have an existing form, & I was thinking of just creating tabs for the months & subforms in each with their sources as the month tables, & removing the fields that don't change month-to-month (e.g. client name), with relationships between them. I would start by copying all of the master table data in to the month tables & allowing edits from there.
the company doesn't track when the transactions occurred; I'm unable to group them along those lines.I'm new to Access & don't want to make a mistake.
In Access 2002, I have a Phone Number field (Text) that is meant to store (obviously enough) phone numbers. However, when I enter 2009 as the last four digits (ex. (555)-555-2009) the number is changed to (555)-555-2010. This also occurs if I enter the number directly into the table (which doesn't have an input mask). I have tested it in a new form (even without input mask), and a new table, which both result in the exact same thing.
View 2 Replies View RelatedIm building an inventory database for my company and have the tables, relationships, forms and query's built but im struggling to figure out a way to make all the quantities move around when I have a purchase order or sales order that either adds to removes from inventory.
My database is fairly simple as all I need to do is track what is coming and going and what is the current levels of inventory. I have for instance a product table that has all the information on what products I have and what their part numbers, item number (primary key), description and so forth, separate customer tables and supplier tables, as well as an inventory table setup similar to the northwinds database. I used queries to combine my purchase order and purchase order detail tables and to combine the sales order and sales order detail tables to make sales orders and purchase orders through their respected forms and all is good in that sense but it doesn't move the inventory numbers just lists what is in each purchase Order/Sales order.
When I change a font for a control, save it and then reopen it the font changes back to another font.
It's always the same font.
I'm trying to save it to Calibri but it reverts back to Constantia for some reason.
I've deleted the bound control and added it back but it did no good.
The only way I can change the font is to set it in the On Load of the form.
All,
I try to use common naming conventions wherever possible.
Every now and then I come across what should be common but I can't find a standard method to use.
It not really a huge problem, but as this is the General forum I thought I'd ask.
Does anyone have a good link with a long list of conventions?
http://www.mvps.org/access/general/gen0012.htm
http://www.databasedev.co.uk/naming_conv.html
http://www.acc-technology.com/namconv.htm
Are all good places to look.
Does anyone have a standard way to name a table which permenantly resides in the client but the data is very much temporary.
I use these tables a lot and was just interested.
No matter if there isn't one, just seems a good idea to open up a discussion for anyone else who wastes time like I do on the name of objects etc. rather than doing the work:-)
Any and all ideas on the subject most welcome.
Cheers, I hope this becomes a useful thread.
Following on from entries in a previous post (see http://www.access-programmers.co.uk/forums/showthread.php?p=612071#post612071 ) I wondered what people considered to sensible naming conventions.
For example should a table be called "Order Details", "Order_Details", "OrderDetails" or "tblOrderDetails".
Should variables in VBA be given a prefix which indicates their type eg strString or intInteger etc.
Lets have your views.
When I started using Access excel had been my closest app. to a database.
My first attempts to build a database failed because I tried to build the database like sets of excel sheets.
Any ways I then discovered Normalization, that helped a lot.
Now I am wondering about naming rules.
I have seen a few examples like:
ThisTableName,
strThisFieldName,
this_field_name,
Is there one standard or method or is this just user preference?
I have a access 2007 file. with a table with a yes/no field. When I link the table to another db using lnked table manager, the yes/no display is not a checkbox (as set) but is turns into textbox.
View 1 Replies View Related