The Ever Dreaded Many To Many
Sep 21, 2007
I can't quite get my head wrapped around this, so any help will be appreciated. Here's my set up.
Every table has an AutoNumber Unique ID to make things simple.
This is about insurance information for a company that owns a lot of property.
There are other tables in my database that I am not mentioning because they do not affect my many to many drama. So if it looks incomplete, it (hopefully) isn't and I just didn't mention it.
Tables are:
tblBuilding
BuildingID
BuildingName
tblPolicy
PolicyID
EffectiveDate
ExpirationDate
tblPolicyCoverage
PolicyCoverageID
CoverageType
PolicyID
tblCoverageInfo
CoverageInfoID
PolicyCoverageID
True Statements about this information:
A "Policy" has many "Policy Coverages"
A "Policy Coverage" has many "Coverage Info"
A "Building" has many "Policy"
A "Policy" has many "Building"
So my Building/Policy relationship is the many to many relationship. So I want to create a middle table which will have a New ID, and the Building ID and the Policy ID. I think.
Here is my confusion. Do I create a middle table to connect the building to the policy? Or do I connect the building to the policy coverages? or do I connect the building to the Coverage Info? Because it's true that a building has many policies, many coverages and many coverage info. I'm thinking I put it at the policy level but I'm not 100% on that.
I fear I won't get this answer without hours of trial and error and I don't have hours being this is just a side thing for one client because they own like 200 properties. If you need more info, let me know. Any suggestions, much appreciated. Thank you.
View Replies
Dec 14, 2006
I have started making a stock control database, i have spent about a week on it, And i am struggling! There are obviously lots of different ways to actually build them efficiently. My problem is that i want to add stock and take stock out, changing the stock quantity and obviously keeping track of stock. I only want to ammend the stock quantity, in the stock table if the item is not a new item. The stock is added via a log sheet which could also have a new item. My head is beginning to hurt, but with a bit of help from you guys i might be able to crack it.
The Tables I have are:
tblStock
tblCategories
tblSuppliers
tblInventory transaction
tblStock_in
tblStock_Out
tblEmployees
Is this sufficient? Can i add stock and take stock out with one transaction table? Or would i need to add another table? I believe the code would be through a query but again am a little unsure as never done anything like this!
Please help!!
View 8 Replies
View Related
Sep 28, 2006
I recently inherited an MS Access application. To bring it up to company standards, the data needed to be rehosted in DB2. The screens remain in MS Access. To accomplish this with no/minimal code change, I followed these steps:
1) For each access table, I created a DB2 table with the same structure. (table name and field names changed based on company standard)
2) Created a link table in access for each DB2 table
3) removed each Access table and replaced with a query (based on link table) with same name. The query maps field names us AS clause.
The reports and screens paint fine but any update operation results in a 3073 error. 'Operation must use an updateable query'. There are no joins or aggregates in the query.
Any suggetions?
thanks
Howard
View 1 Replies
View Related
May 13, 2005
Hi Gang,
I'm new to this forum so please bare with me.
I have spent the last two days trying to resolve, or work out why on earth I am getting this error message.
I am at the point of pulling my hair out - its SO frustrating so *any* suggestion would be great.
I have created a small database for plant (as in flowers) details. To cut a long story short, its a simple db fronted by a
form that allows me to display and add records together with an associated image (stored outside the db).
I was trying to write some code that would look at the control that displays the image and, if there is no image associated
with a particular record, it would display an "add new image" button. If there is an associated image, the buttons visable property is set to false.
I had ammended a section of code for the forms "on current" event and saved the changes. I then changed to form view, tried to enter a new record and access throws up the error message. It does not matter which field I try to add data in, the same error still happens. I have checked, and re-checked all my code and property settings for every control and field on the form and I can not track down what is causing this error.
I noticed from searching this forum that someone else had this error but the resolution does not appear to have been posted so can anyone help in any way at all.
My grateful thanks,
Kenny
View 10 Replies
View Related
Jun 5, 2006
Please bear with me.
I'm fairly new to access, and have learned much in the past three weeks I've been "learning" how to use it.
I was simply sick of the process that was going on at my place of business where I'm a Manager (I've been there for about 3 months). We keep about 10 Excel forms for the different pieces of information (production numbers and such) we keep for each individual in the department. There are 115 people, 3 shifts working 24 hours a day. Each morning I would go in and open each excel file to analyze what happened the day before.
We're also tracking an individual's days off, tardiness, quality... you name it.
I finally decided it was time for a database... I had worked with them in the past, but I had never actually "built" one. I've actually knocked out a significant portion of what needs to be done, and I'm proud of my product thus far... except for one thing:
Vacation accrual is "kicking my posterior"!
I've searched these forums (finding some answers, most of which I don't understand), but I almost need to be handed the answers. My other struggles I've overcome with the help of these very forums and Google.
I'll lay out our policy and what I need and hopefully someone can help me. As I learn access (and I will!), maybe one day I could come back here and help someone else that may be in need.
Our policy is as follows:
90 Days to be eligible for accrual.
1-3 years of service - 6.67 Hours a Month (10 Day a year or 80 Work Hours... all the same, and whichever makes things easier)
4-7 years of service 10 hours a month (same deal as above as 15 days or 120 work hours)
+8 years -13.33 hours a month (blah blah)
The hours do NOT carry over, they are all use it or lose it. I can make manual entries when necessary, so it doesn't have to be all "automatic".
Like I said, the searching I've done has come up with some things, but not in the language I yet understand. I do know "modules" and some expressions, but the more that could be "spelled out" the better.
I've got your (what I consider) normal Employee fields, and would like to use something like "DateStarted" as a beginning point, but I'm open to anything.
Thank you VERY much in advance for any help that can be given. This has stalled my project almost a week trying to figure out a solution!
View 10 Replies
View Related
