Database Planning
Mar 13, 2007
Hi All,
I'm Looking for a bit of advise. I'm about to try and build a new database that will have about 40 tables. One of these will be the main table. This table will have a primary key TAGNUM and several other fields containing data associated with each tagnum. Most of the other tables will then contain more detailed info about each tagnum.
So the obvious aim is to have all these secondary tables connected somehow to the main table so that when a new tagnum is added/deleted, the corresponding row in any effected secondary tables is added/deleted also.
My attempts so far have involved setting up a relationship beween tagnum in the primary table and tagnum in each secondary table. This appears to work ok but I'm trying to be sure that my setup is good. I have a one to one relationship in each case, I have selected referential integrity, and the cascade changes etc. My join type is "All records from main table and only those... secondary table". Is this correct?
The further complication is that I need to have Lookup?? tables (not sure if it's the correct word) tied to each of the secondary tables and 3 tied to the main table. The user needs to be able to select items from the fields bound to these lookup tables via a dropdown box on the field in a query. When the dropdown box is selected a descriptor for each option available need be seen The non bound fields in the lookup tables only needs to be available to a report bar the showing of the Description field during selection. Any ideas?
Sorry about the size of this and annoying you with what is i'm sure not difficult stuff! Pretty new to this.:)
View Replies
ADVERTISEMENT
Aug 11, 2005
Hi guys
Quick question. im running a planning database which has got 3 tables Objectors, Representations and Agents. Ive put these together using a one-to-many join matching up the index fields from Objectors and Representations. Ive just constructed a form which has a main form being the Representations and the sub forms with Objectors and Agents. When I want to update or add records a message saying " you cannot add or change a record because a related records is required in table Objectors.
Can any one tell me how I can get around this or possible solutions to this is? Any comments or feedback greatly appreciated!
Cheers
View 5 Replies
View Related
Oct 14, 2005
HI THERE. IM HAVING A FEW PROBLEMS TRYING SORT THIS PROBLEM OUT. I HAVE A DATABASE IM TRYING TO APPLY A FILTER ON THROUGH AN UNBOUND COMBO BOX. WHEN SELECTING THE DROP DOWN FILTER TO SORT THE RECORDS NOTHING HAPPENS. THE CODE IM USING IS
Private Sub Combo??_AfterUpdate ()
'Find the record that matches the the control
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[WARD_AREA] = '" & Me! [Combo??] & " ' "
If Not rs.EOF Then.Bookmark = rs.Bookmark
End Sub
Does anyone know why this filter is not sorting through the records. Any comments much appreciated!!
Attached is the database
View 1 Replies
View Related
Sep 6, 2007
Hello
I wonder if anyone can help me?
I am currently creating my first Access database and in the planning stage but have come across one problem area. I need to create a database that holds and reports on information recorded on a clinical form. The section of the form I am stuck on contains a table as below.
123456
Catheter Type
Wire Type
Balloon Type
Type of Stent
Size of Stent
Target Vessel
Successful?Y / NY / NY / NY / NY / NY / N
The user works their way down this form starting in column one but free typing what make of Catheter they will use and then type of Wire and so on. The problem arises when a particular item doesn't work, e.g. a type of Balloon. They then try a different type of balloon and would enter this information in Column 2 and then if this works go back to column 1 to fill in the next field 'Type of Stent'. As below..
1 2 3456
Catheter TypeCathsRUS
Wire TypeWire 1000
Balloon TypeBAL001 BAL002
Type of StentST555
Size of Stent10
Target VesselLeft side
Successful?Y / N Y / N Y / NY / NY / NY / N
Not only does the user need to record all this information without duplicating anything else from column 1 into column 2 but there are also instances that within the same procedure number, that a second bleed occurs and they need to fill in the next available column, which in the example above wiuld be column 3. They would again work their way down, but this time they may not need to use a wire as they can reuse the last one.
1 2 3 4 56
Catheter TypeCathsRUS OpCa34
Wire TypeWire 1000
Balloon TypeBAL001 BAL002 BA3
Type of StentST555 ST333
Size of Stent10 5
Target VesselLeft side Right side
Successful?Y / NY / N Y / N Y / N Y / N Y / N
The access database needs to be able to hold all this information and show that 2 separate episodes in affect have occurred. The user wants to be able to report on various things such as, what type of Stent/Wire/Balloon/Stent size is the most/least used / successful/unsuccessful? Etc
If anyone can help shed some light on the best way to structure this information in Access I would be very grateful!
View 4 Replies
View Related
Dec 18, 2007
Hi all,
I’m adding a new function to our Costumer Relations Management system – Time management. Or maybe “time registration” is a better definition for it. The idea is that our consultants can add their hours into our CRM, either from the “Project”, “Tasks” or “Costumer” part of the CRM. Note: This is a tool to keep track of hours spent on a project, not a planning tool.
I’ve made the input-form for the records (Date, ClockStart, ClockStop, TimeSpent, InvoiceHours, etc.). But now I’m basically without a clue on where to start – I want to make a function almost like the calendar in Outlook, where the consultants can input what they’ve been working on, say between 13:00 and 14:00.
It doesn’t have to be fancy graphically, but functional and give a good overview.
Any ideas?
View 6 Replies
View Related
Feb 28, 2007
Hi all! Need a bit of planning help with this one.
I have a access db for a small webhosting company. In this db there is a table containing the different sites, and another table containing siteproducts (Domains, Hosting, etc).
I want to make a function that creates a invoice (tblInvoice) for every site with billingdate in march. This function has to transfer the siteproducts (tblInvoiceLine) as well and gives the invoiceline the right invoice number.
I guess I have to do this with querries, but I have noe idea how to transfer "two" levels - or from and to two tables at once.
Any suggestions?
View 3 Replies
View Related
Sep 7, 2011
I'm trying to set up a simple query that links four tables. However, the tables are extremely large, all in excess of 1.5GB each so I had to split the tables up into four separate DBs. I've tried the following with no success:
1) Link the 4 tables in the DB which contains my primary key. This quickly inflates increases the file size above 2GB and won't let me go any further.
2) Build a remote query to connect the four tables. This looked promising until I tried to run the query and it became evident that it only knows to point to the last database source that you specified.
I'm running everything locally on my C drive. The data source are simple text files (1.6 million rows) from the FDA website.
View 3 Replies
View Related
Oct 7, 2005
Hello All...
Well, I am facing one problem..in my application; I need to show all forms / reports name of other database( .mdb ) file without opening the other database physically. I tried a lot but didnt succeded. I tried with below code..
Set AcApl = New Access.Application
Call AcApl.OpenCurrentDatabase(strfolder, True)
Set AcProj = AcApl.CurrentProject
Set frm1 = AcProj.AllForms
intCount = frm1.Count
But here wen the second line AcApl.opencurrentdatabase get executed at that time the database get open physically, and i dont want that..So is there any other way around..If so..please please help me..
Thanks in advance..!!
View 4 Replies
View Related
Aug 22, 2014
i have a database that runs updates from within itself.what i need is, this database to then open a another database run a update query, then close it.
View 4 Replies
View Related
Sep 12, 2012
Database: Access 2007
I have designed a touchscreen input system using Visual Basic.net and this writes to an Access Database. Each Touchscreen has its database locally so it can still work even if there are Network problems.
Now what I would like to do is have all these local databases write to a central database say every minute but only write new records to the central database. The Central Database can either be Access or SQL.
What is the best way to do this?
View 1 Replies
View Related
Jun 24, 2015
I have been working on a database for over a month now, and my boss just threw a monkey wrench in my work. I believed that the Metal Market Prices would be entered once a week in the current DB. My boss informs me today that he wants an employee to go in every morning and enter that days Price for Each Metal with respect to many different markets.
There is a total of 12 metals, and 5 markets. I need the data to be stored first by date, then by either market or metal, and lastly by which ever isn't used second (Either: date-market-metal, or date-metal-market). I think the Latter of the two methods makes the most sense. Is it possible for my current DB to lookup values from the Metals Database based on date-metal-market?
View 14 Replies
View Related
Jul 7, 2005
I have a problem that seems to be happening on several users' databases and is causing a big problem. None of the databases is a shared database...they are all single-user databases on stand-alone computers. I have tried looking for help within previous posts, but all seem to be related to shared databases.
I am getting an error message: "The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time." The database cannot be opened, imported, repaired...nothing seems to work.
Again...these are NOT shared databases. I appreciate any help I can get. I created the database for all of the secretaries in our school district to keep up with absence data. It involves many tables, queries, forms and reports, and has generally worked well. However I am now seeing several that are getting similar errors as mentioned.
Thanks!
View 3 Replies
View Related
Dec 25, 2005
I'm new to Access and VBA, for the record. What I need to do is copy a SQL database (table structures and records) and save it as a local Access database, so that the user can query and make any changes on the local file without editting the SQL database.
Looking around, it seems like the best option would be to use the TransferDatabase method with the acImport option. Does this sound reasonable, or can someone with more experience suggest a better way to go about it? Thanks.
View 3 Replies
View Related
Aug 28, 2013
I am testing the security of my DB X on Acccess 2007. I could create a new database Y and linked to the database X. Unfortunately i could change the records on the tables. I don't want other DB that make connections to my DB to change my tables!
View 1 Replies
View Related
Aug 29, 2005
Is it possible to access a table in one Database (Database A) from a separate Database (Database B) and if so how. I should clarify that this is an Access Database.
View 2 Replies
View Related
Oct 24, 2013
I created a database with forms, querys, etc. Now I was thinking of creating a exe so everyone can use this database without having access to all the design functions.
the only problem I see about doing this is if we need to use the same database for different project, we need to always create a new exe for each project.
Is there a way to make a empty database exe with just the layout, querys etc but with no data on the tables and have a save, open and save as option. So we can have several projects using the same database?
I was thinking to create a VB code for the database to delete all data on the tables when the database is open.
and before closing the database exporting all the data to a txt file or something.
Then the next time someone opens the database goes to the main form which has a open bottom which imports the txt file into the tables.
This way we can have several txt file for several project and use always the same exe database.
View 4 Replies
View Related
Nov 20, 2006
I am working on a database that will be an addition to an existing one on the company server. However, to make the overall layout not so complex and allow room for other additions in the future, I'd like to keep the databases separate. This will also ensure more efficiency, integrity and troubleshooting overall.
Does anyone know how this is done?
Thank you in advance for your help,
~Kilch :eek:
View 4 Replies
View Related
Oct 2, 2007
The menu for make a MDE file is blanked out or grey’d out. The database is partly made using access 2000 then xp
Jabez
View 6 Replies
View Related
Dec 21, 2013
I have a series of unrelated access databases and I would like to have one 'super menu' where i can click a button to go to different databases.
View 3 Replies
View Related
May 24, 2005
I have a car dealership database and have a servicing section.
I want to create a form to service these vehicles.
The only vehicles which will be displayed on the from are the sold ones (because you cannot service a vehcile which hasnt been sold) which i have a query for.
I also want the form to display the customer who owns the car the service details (eg what they need), the parts availiable and the parts required for the service.
The calculations i need to make in this form are as follows
quantity needed (parts required table) - quantity in stock (purchasing customer parts table)
quantity needed (parts required table) * price (Parts Table) + labour (servicing table) = part/labour costs (servicing table)
http://pic18.picturetrail.com/VOL885/3178839/6480291/97832270.jpg
any suggestions on how to do this would be fantastic
View 2 Replies
View Related
Jan 4, 2006
i havent been able to figure out how to get my subform to work properly....
i've posted in the forms section (http://www.access-programmers.co.uk/forums/showthread.php?t=99624 )
it is really important that i figure this out so that i can keep my job..
how else can i obtain help? even if i have to pay for it?
what do you guys suggest?
View 4 Replies
View Related
Feb 13, 2006
Hi all,
I was playing with passwords for one of my access db.
I tried opening the db with the password, but it won't work. So, I thought I forgot the password. I checked some posts on this forum that helped in retrieving the password and it's the same that I have been trying. I am not sure if the Name is correct, is there a way to retrieve the log on name?
I also noticed that when I try opening any other db on my system, it asks me to log on, I though I was only applying a password to one file.
Please help
Thanks!
View 3 Replies
View Related
Apr 3, 2006
Hello Here is my question I have a datbase with 4 tables
I have one of the table related to the other 3 by agent name.
I Have a page with the agents and all thier info. NO problem
I want to create a link from each agent to thier listings..
Do I need to have a seprate page for each agent or can one page
create the info I need with that agent..??
View 1 Replies
View Related
Apr 15, 2006
I have been tasked with setting up a database for all employees the info contained is along the following lines:
Employee Number:
Name:
Qualification Status:
Qualification Run Out Date:
To name but a few of the parameters
I have currently set up the start up screen, main switch board and the complete employee records table with form (for simple viewing/input).
My problem is this we have four departments that require access to the database
Admin - Require access to all employee records (View)
1 Dept - Require access to their employees (View and update records)
2 Dept - Require access to their employees (View and update records)
3 Dept - Require access to their employees (View and update records)
Currently i have created one complete employee record table (Admin) how do i break this down for each department so that when the info within each one is updated it in turn updates the main Admin table?
I started by copying and renaming the Admin table for each department but this never updated.
Any help and info would be appreciated.
On a slightly less important note for the time being at least can i password protect each command button on the main switchboard?
View 1 Replies
View Related
May 16, 2006
Hello,
I basically started teaching myself how to use Access 2000 about a week ago. I am working on a project for work, that requires to have a report of all the training records. But my boss wants it to be color coded. He wants one color to show the people's training classes that are 45 days out from being over due, he wants another color to show what classes are due. And another color that shows classes that are over due. In my table, I have a column for the classes that show the date that they took the class, and I have another column that shows the due date of the class. But I have no idea how to go about getting the report color coded with what he wants. Any help at all would be great. Many thanks in advance.
View 4 Replies
View Related
Jun 28, 2006
I am being asked to modify a old program for a new similar function.
In the blue heading bar above the tool bars there is a name (ECB License) for the database. I need to modify this name to the new name (wheat license).
Thanks for your help. GG
View 2 Replies
View Related
