I have a database split into a frontend and backend.
The front end has several make-table queries that create tables of data on which pivot tables and other analyses queries are based. The make-table queries get re-run every now and then as new data get entered.
The resulting tables are huge - 500K records in some cases. It seems like a bad idea to store them in either the front and back end.
Should I create a second "backend" that holds just these temporary tables? If so, what's the best way to design the make-table queries so they make the table in this "backend"?
What I would like to do is to call up all the data in table1, and then call up (asset, debt, equity,...) from table2 where the company_code and year-month are equal. What do you think is the best way to do this? Keep in mind a few things:
1. I'm dealing with hundreds of thousands of data lines 2. I may need to get more data similar to table2 in the future and call it up in the same way
I thought I should create a primary key "company_code-year_month"for both tables, but that method doesn't seem like a good one, and if I were to have another table, will have to create the same primary key for that table as well?
I'm thinking about creating temporary (while db open) relationships between tables, since transfering tables is possible only if no linked relationships are present. However it's important to preserve in some cases referential integrity.Looking online, I found this code:
Code: Public Function CreateRelation(primaryTableName As String, _ primaryFieldName As String, foreignTableName As String, _ foreignFieldName As String) As Boolean
[code]...
Even with the comments I don't really understand what exactly this code does or doesn't do.I tried the code. It seems (?) to create a relationship. Since the relationship doesn't show up in the relationships table, I'm not really sure if it's there.
- why it's not visible among other relashionships (or is it me?? maybe, it wasn't the code that was working but the query...) - how to implement referential integrity - what's the behaviour of this supposed relationship. Is it permanent or not? If not when is it deleted?
I'm making a library database program thing... There's an option for the user to view all books on loan.
I have two tables:
Books, which has columns ID*, ISBN, Author, Title, Year, Location BorrowerStorage, which has columns Book ID, Name, Email Address, Desk Number
Book ID in BorrowerStorage is related to the Books primary key.
Now, for the viewing all books on loan, I want it to produce a read only table which contains all the entries from the BorrowerStorage table and the corresponding Title/Author columns (i.e. the records for which the ID in Books column = BookID in Borrower Storage column)...
My DVD library database I have a memo field which has the description of the movies.
In some cases, like my tv show records, I have a lot of data because of the episodes and episode descriptions.
I'm currently displaying this data using a split form with the datasheet on the left
I must use a small font in the memo field in order to display all data.
I could use a larger font and simply scroll but I'm looking for an alternative.
I've even thought of creating a hyperlink filed and point to the movie on IMDB's web site. But when I click the link nothing happens even though I copied and pasted the link into the field.
I am taking a filing index of City Council Resolutions which is currently based in Excel and transferring the index to an Access database I am building. An issue I am having is that the Resolutions being indexed have a subject description that ranges from about 100 characters to +700. The new Access database must hold the complete contents of the Excel spreadsheet.
The direction I have taken is to divide up the subject field into three text fields as needed ([Subject1], [Subject2], [Subject3]) each with up to 255 characters. In reports I will simply combine these fields into one large text box so the user will see them as one large subject description. Memo fields are not an option because in the near future the database backend will be moved to SQL and I understand that SQL does not always play well with memo fields.
My tables are sybase linked tables to my Access front end. I get this error whenever i click away from my mainform while my not-null fields (textboxes) have not been entered. So, i would like to put an error message "somewhere" that tells the user to fill in the necessary fields and highlight the necessary textboxes in red, instead of this scary ODBC message http://img82.imageshack.us/img82/6829/error0el.png :eek: :eek:
When i put my error handling in the afterUpdate or afterInsert Event. I still get the above error msg instead of my msgbox. Where do i put this message? or should be question be, "what the proper way to handle this intimidating error? :)
Here's my error handling:
On Error GoTo Err_Form_AfterInsert
'do something
Exit_Form_AfterInsert: Exit Sub
Err_Form_AfterInsert: MsgBox Err.Description, vbExclamation, "Please enter the Project Name, Project Leader Initials, Main User and Status Code" & Err.Number Me!Combo67.BackColor = "red" Me!Combo65.BackColor = "red" Me![Project Name].BackColor = "red" Me![Project Leader Initials Combo].BackColor = "red" Resume Exit_Form_AfterInsert
How can i extract all the information in those tables and put all the data into one large table? I want to extract everything apart from one table?
and can I format the large table once the data have been put in i.e. insert new Columns at the start, and populate fields based on the value of other fields values?
Hi there, I've developed over the past 3-4 years a database holding data relating to workload figures for my place of work. It has grown to a large size (eg. one table holds 1-2 million records). I've been trying to run a particular pivot table based on the sql query below:
SELECT Tests.Test, Year([DAUTH])+(Month([DAUTH])<4) AS FYear FROM Sets INNER JOIN Tests ON Sets.SET = Tests.[Set Code] WHERE (((Sets.DAUTH) Between #4/1/2003# And #3/31/2007#));
When I try producing a pivot table using the above query it takes forever - has it running for over 8 hours last night - then access closed down with a runtime error. Does MS access have a problem with large databases? Is there any way I can improve the efficiency / speed of access with a download add-on? Or, will I have to resort to using something like MySQL on a LINUX OS which has fewer demands on system resources when using large databases.
Any help would be much appreciated. Thanks for reading.
I am a newbie when it comes to Access and ASP but I am trying. I am in the need for some help. I have a large table in Access 2003 and need to break it into smaller tables (not using a query) where the fields are the same except each of the smaller, new tables will hold info for a specific person. These smaller tables would need tro be linked to the larger. Is there an easy way to do this? I need to keep it in tables due to the ASP software I use to generate the asp pages. Is this possible? My goal is for each user to be able to only view/edit their data and for the large table to reflect any changes made to the smaller table. Thanks, Dan
I have "Master" table with fields "Job No" and "Revision No". Both together is a primary key, so that combination of both cannot be duplicated. I have 100 other tables to be related with referential integrity(+update&delete) to Master for both fields. Apart from Job No and Revision No, all 100 tables have different set of fields which is why I had to come with so many tables.
Due to 32 limit rule, I had to come up with workaround method to have all 100 tables in the relationship. So, I created 5 other SubMaster1, SubMaster2, ...., SubMaster5 which are related to Master with relationship with referential integrity (+update&delete). Then I assigned 20 tables to each SubMaster so that 20 tables are related to each SubMaster table.
Whenever I create new record in Unit, the new record is generated in each SubMaster using update query for each SubMaster table. I have all the forms and necessary query laid out. The only missing part is being able to duplicate a record. I have limited knowledge in VBA, but I should be able to modify it to address to my requirement.
I want to copy a given record in Master, SubMasters and 100 tables as a new record. I need this feature so that I can select certain Job No and Revision No and copy that as a new Job No(assigned manually in a form) and 0 as the revision number. Possibly a button which will ask for new job number and copy everything from the active Job No and Revision No to a New Job No and "0" Revision No. The existing record may not be there on all 100 tables for the given Job No and Revision No. If it is there, then copy otherwise ignore for each of the tables.
I have a table "ItemList" which lists all the unique name of the 100 tables.
I want to make a database of diseases (need to learn them for school and would like a serchable database on my smart phone for future reference). Unfortunately spent a lot of time making hundreds of pages of word tables before i realized a database would be better. See attached image or pdf. Is there a way to import the tables to Access?
I need to preserve the hierarchic info in the nested bullet point lists. E.g. under treatment i might have a point called Acute treatment, with sub-levels, Step 1, Step 2, etc. with their own sub-levels. I need to maintain this relational hierarchic info.
Currently trying to build a database for customer management and order placement/tracking. Want to set a couple of rules so that if I for instance click yes of billing and shipping address the same that the database will automatically fill the shipping address with the data I inputted for the billing address in the same table.
The other issue I can see I'll run into is, I want to be able to select one of the company ID's (made up of a three letter abbreviation of the full company name) in the product ordering table and it will automatically fill in the rest of the customer data (phone, email, address etc) data into that form.
What I have is a database setup with multiple tables in which different areas of my DC can input information simultaneously into their respective tables. I then have another database linked to it for myself to have a live view of each updated record. I would like to see all the records of each table in 1 single table (possibly just sorted ascending by time). Each table has the same Field headings but may have different qtys of records. As I will then have it linked to an Excel table to VLOOKUP from it.
I have tried Union coding but always get Syntax Error etc.
I'm using the MS Access 2010 ExportwithFormating action to export three tables to a single MS Excel 2010 workbook. The action overwrites the first excel worksheet each time instead of saving all three worksheets in a single excel workbook.
How can I export three tables into a single excel workbook.
I have created a table that acts as a header for my data and a second table that acts as line item data. What I need to do now is add a second child table that uses the line item data as its parent table and stores associated line items for each record. Is this possible?
I am using calculated field as a data type in access 2010.
They are working fine.
However, I added a new field and now the final calc won't work.
I have Subtotal adding loads of fields together. Works fine.
Then I have a VATunit field which is a double integer, so enter 20 and my next field is VATTotal calculates the SubTotal + the VATunit by doing (Subtotal/100)*VATunit. This calculation is fine and gives me the correct amount.
The next field is a Total field. Which adds Subtotal and the VATTotal together. Howver, the Total is the same as Subtotal. It is not adding the VATTotal to it?
I have some experience doing 'Update Query' using two different tables but I'm having a hard time doing an 'Update Query' using 3 tables.
I have my source table TP05XY with the fields 'Mark' 'Date' 'UTM_Edig' and 'UTM_Ndig'. Mark and Date are my primary keys (they together uniquely ID each record). I have my Observations table with the fields 'Mark' 'Date' and 'Obs_ID'. The last table is Locations with 'Obs_ID' 'UTM_E' and 'UTM_N'.
I want to update my fields UTM_E and UTM_N from UTM_Edig and UTM_Ndig. However, to do so, I have to go from my TP05XY table, through Observations table to update Locations table. Table TP05XY is joined to Observations through 'Mark' and 'Date' and Observations table is linked to Locations through 'Obs_ID' field.
I have tried a few options without success ... anyone knows how to do it?
I have being playing with ms access but I really don't know much about it or databases in general.I have created a very simple database to gather twitter following/followers data for research purposes.One table (table01) has a field for the "boss" user (=the user who I gather data for), another field for "client" (=bosses followers or friends).Both fields are numeric and contain the users id's.In order to distinguish if the link is follower or friend there is a third field, called type which can be either 1 (=follower) or 2 (=friend).So the data would look like this:
boss - client - type 12345, 67890, 1 12345, 54321, 2
If user with user id 12345 had a follower (type 1) with user id 67890 and a friend with user id 54321...In order to avoid getting duplicate rows I also added a unique identifier which is of the form boss_id-user_id-type.So the above row looks like this:
That works just fine.For several reasons I also needed data of the form source - target.So I also made another table (table02) of this form.
67890, 12345 12345, 54321 ...
In table 2 you don't need the "type" field since the position of the user id shows the type of relationship.Still, you need a unique identifier in order to avoid duplicates, so I added on with the form: source_id-client_id..So table02 lookes like this
Both tables also have a date/time stamp for each line.As you can see, table01, having also a type field is bigger than table02.The problem is when I try to append data, exactly the same data in both tables.Appending data to table01 is ok, while appending data to table02 (which is smaller, having one less field) takes a really long time, maybe 10 times as long as appending data to table01.To make sure that no query's are causing the problem I have tried first creating temporary tables with the data to be appended, no duplicates, nothing that would cause the database to make extra calculations and used a simple update query with no filters to append data.Still I get the same result, table02 takes a very long time to finish while table01 finishes in no time.
I am currently using Microsoft Access 2010 32bit, and have one database acting as parent, with a second linked database as a client for people to work with, and the parent database has tables imported from Sage V21 via ODBC. I have used the following code as specified in other examples as follows:
one of the tables has 10k records in it, it only transfers 77 records. After manually attempting an import via the ODBC wizard it finally lets me access all 10k records.I wish to have a single button click delete and import fresh tables without worrying if all the data is coming across.Also, when manually adding a table, I am usually asked by the wizard to specify an index, but with the code above, I am not offered that option and the tables come across with no index. I am led to believe that having tables that link to each other without an index is bad, so how do I ensure an index is created?
I have two tables of data, each relating to three business branches (branches A, B and C).
Table 1 shows the expenditure of each branch (by fuel, premises and wages).
Table 2 shows a number of units for each branch (mileage, floorspace and sales).
What I would like to do is calculate unit costs, based on the expenditure in Table 1, divided by a relevant unit in Table 2. The catch is that I want to have a third table which allows the user to specify which expenditure (from Table 1) is combined with which unit (from Table 2) to generate the calculated unit costs. I've been able to do this in Excel, and have attached an example. I've also attached an incomplete Access version with the first two tables. Given the complexity of my actual data, I feel this could be better handled in Access than Excel.
