Unanswered: Multiple entries - single field - single record
Beginner in need of assistance with Acess 2000 database.
Is there a way to have a form in which you can enter data into one field which will poplulate several other fields, and also allow you to enter multiple data into the same field.
For instance: I have a form that contains such fields as CustomerID, SiteID, ProjectMgr, Technician, SSO, MTC, RouterMake, RouterModel, RouterSerial, CircuitType, CircuitSpeed, DLCI, and etc. My problem is that a site may have anywhere from 2-20 routers and circuits, and each site may also have 2 or 3 DLCI numbers. There are also several other fields in the current database this applies to as well. Right now, I have to enter each site as a seperate record, and then if there is more than one router, a new record has to be created for each additional router and you have to type in all the information again (address, site name, siteID, etc) for each record associated with that site.
What I would like to be able to do is to have one form that I can enter all the site information into for each site at one time, and have it populate the MasterData table. Then when the information for that site needs to be viewed or changed, the same form would be used to look up the project name or customerID & siteID -- which would populate the fields to be viewed or updated.
Since there are so many fields associated with a project, I don't want to use a data table in the form and have to scroll from side to side to fill in the information.
As reference, I have attached a snapshots of the forms currently being used for data entry.
What you are doing requires a sub form approach. Depending on how much Access experience you have had, it may be that your table design is wrong, so the subform approach may not work. So before we go into the sub form we will talk about the structure.
I assume you have a table set up something like:
I presume a customer has a site. So therefore you have a relationship from Customers to Sites and this has referential integrity enforced.
Likewise I assume, that equipmenttype is related to equipment and referential integrity is enforced.
I wish I could give you a good idea of this database, but I wasn't the designer for this database. I'm just trying to fix it up a bit so that it is more user and data entry friendly.
I have attached a screen shot of the relationships for the current database for your review.
What we are trying to do is fix up this database so that it is not so difficult to enter data into so that it can be used more effectively and efficiently until we can move into something else. What we are attempting to do is to create a tool with a SQL backend and a Web frontend that will also interface with another applications we use called Actuate and/or Premier. This change will be a long time coming, and so I have been given the task of doing whatever it takes to figure out how to fix the current Access database to better server our purpose. Since I have very little experience with Access, it is proving to be a challenge.
All we really need is a homepage/switchboard that consists of a button to enter information for new projects that will allow us to enter multiple router, hub, circuit, and order information for the same siteID for a customer. Then, we need some pregenerated report queries and the option to create custom report. We can handle the reports, it is just the entering of multiples for the same customer/siteID that we are having trouble with.
I would attach the database file without the existing data, but with all the changes we have going on within it, I am afraid that it would be too confusing and too detailed to understand which forms/tables/Quieres are the ones we are building and which are the original ones.
The data base is not normalised. This is the problem and solution. As it currently stands the Master Data table is a mismash of data, and is the table your form appears to be based on.
The Master Data table is in fact a join table for Client Detail, Routers, Project Management and so on. However, it is not atomic and this is why you are having problems with data entry.
I strongly recommend a complete overhaul of the data base starting at table design and relationships. If this is not possible then you may have to live with the clumsy data entry.
My suggestions are as follows:
1: Set up a table called Site. this table would contain all the site details like address and so on. It would be related to MasterData by SiteID.
2: Region would relate to site by RegionID, not by region as it is now.
3:I cannot see call manager well enough to see what is happening with this and site ID.
4: Project table needs an ID field ProjectID and this should be related not project name
5: PO1,PO2,PO3 is not how Access tables should be stored.
There is a lot in these 5 points, and that is without studying the actual data to sort out what belongs with which.
I can see that there are around five tables in master data that need to be extracted and probably about the same in stage data. I also see what appears to be another person making changes to the database previously.
This is not a task for the faint hearted, nor a small undertaking.
Thank you so much for taking the time you have. It was my thought and feeling from the very beginning that the current database needed to be scrapped (except for the data), and the we needed to just build another one with proper formats and relationships. Trying to sort out the current database and make it functional is proving to be a hugh task. No wonder nobody likes to keep the thing updated
I will take your suggestions and put them in place when reconstructing our new database.