New user question

[eTom]

Hello! I'm very new to Access and have only just recently set up a small database for our customer complaints. It's pretty bare-bones; there is an "incident" table that has fields for customer name, phone, product, etc. The ProductID field is linked to a ProductID key field in another table.

I've created a form that lets me enter each of these fields by text, memo, or combobox depending on the info. Products are limited to our products, so it's combobox. Customer name could be anyone, so it's text... etc.

Now I have come up to complaints involving multple products. Sometimes dozens. Rather than enter each of these fields in the form every time, I want to speed up the process a little. Ideally, I'd like the form to have the same text or combo boxes up top for things like customer name, phone, location, etc... but then a table below where I could enter product, date, etc. That way I could enter multiple products and date without having to enter the customer info every time.

The trick is that i want each of those products to be it's own record. Is it even possible to have a form create multiple records using some fields up top that are common for each record it creates, while a table at the bottom has info that's different for each of these records?

Failing that, if I have my form as it stands, can I program a button at the bottom that will re-open the form and carry over the information that I would need to re-enter for another product? ie: re-open the form but somehow send it CustomerName, Phone, Country, etc... ?

Hopefully that makes sense. I've only just started with Access this week and haven't been able to get out to pick up a good book on the subject. I've got some programming experience with C++, C# and JavaScript, but I'm new to the world of databases!

Thanks in advance!

 

[Scooterbug]

Sounds like you need to revist your table design. This is a classic Sales Database scenerio. Off the top of my head, you will need the following tables:

tblCustomer
CustomerID (Primary Key)
Customer Info

tblIncident
IncidentID (PrimaryKey)
CustomerID (ForeignKey)
Other Incident Info

tblIncidentProductDetail
DetailID (Primary Key)
IncidentID (Foreign Key)
Other Product Info

You can then use Forms and Subforms to display all the data. Ideally I would imagine the Main form being based on tblIncident. A subform for the Customer Data (with the CustomerID as the Parent/child link) and a subform for the ProductDetail (With the IncidentID as the Parent/Child link)

As for starting out with database dev, read up on Normalization. Good table structure(s) go a long way towards making a database work effectively and makes programming it a heck of a lot easier.

 

[Mr. B]

dTom,

Welcome to the Access world.

As is the case with most new users, you are already experiencing database deisgn and normalization issues. I would strongly suggest that your first learning adventure be in this area. Without being able to know all of the details about what you are needing to do it is a little difficult to determine exactly the data structure that you need. However, Access is a "relational" database, therefore you can have tables of information related to other tables of information.

In your case, you will need more that one or two tables that will have a one to many relationship and you will most certainly need to have at least one table that links all data together form, to form what is called a Many-to-many relationship.

I can quickly see that you would need a table for Customers, a table for products and a table for complaints. Each of these tables, in addition to the normal info, would have its own AutoNumber field that would be used as the Primary Key. You would then need a table where the Customes, the Products and the complains can all be linked by simply adding a record that would have a CustomerID, a ProductID and a complaintID. This would provide the method for joining all of these elements together.

Sorry to just throw all of this on you but there is not other way to learn than just to jump right in and get you feet wet. Start working on your database design. Read up on relational databases and on normalization. It is extremely important that you get the structure of your database designed correctly or you can and will have serious problems later on. As you have questions post back here and many on here will be happy to try to help.

We've all be there done that. Good luck!

 

[eTom]

Sorry, I should have listed it a little more thoroughly.

My main table is the tblIncident. It contains a UniqueID that's just a simple numerical increment.

I have other tables for: Products (the unique key being our in-house item number from our accounting dept), Distributor, Store, Country, and Employee.

Each table is related to the Incident table via their unique ID keys. When entering a report, I choose the Product, Employee reporting, Store, Distributor, etc all from drop down Combo Boxes that populate data from their respective tables. (With the exception of Products that uses a query in order to filter out products that have been flagged with a Yes/No field called "Obsolete")

Stores are linked to the Distributor they fall under and Distributors are linked to the Employee that is our rep for them as well as the Country they are located in.

My form doesn't have any subforms. This is the area I should be looking into?

 

[eTom]

Sorry, and I should go on to say that I know it seems like there is a little redundancy there, but due to the information that I'm actually given on these Incidents (which I hope to change in the near future) I often have to leave some fields blank.

Example: CustomerName in the Form/Incident field I use to refer to the person filing the complaint: Store Owner, Distributor, Consumer, or otherwise.

The ComboBox (and linked field) for Store and Distributor I use to later down the road look for trends and track complaints by each store or distributor. That's why I have to use separate fields for CustomerName, phone number, etc.

The linking of Distributor allows me to get the Distributors information like address or phone number (or Stores based on Store) from the corresponding tables.

Some small data issues have come up. One early example was that in theory when I select a store from the Combo Box, one would think that "Distributor" should fill out automatically because each store should be linked to it's corresponding distributor. Unfortunately, I'm not given all the information that I need for that, and some stores are left unlinked to distributors. I hope to fix that up as I add more stores to the Stores table, as I've been adding them as I come across any new one in a complaint I enter.

 

[Scooterbug]

Sorry, I should have listed it a little more thoroughly.

My main table is the tblIncident. It contains a UniqueID that's just a simple numerical increment.

I have other tables for: Products (the unique key being our in-house item number from our accounting dept), Distributor, Store, Country, and Employee.

Each table is related to the Incident table via their unique ID keys. When entering a report, I choose the Product, Employee reporting, Store, Distributor, etc all from drop down Combo Boxes that populate data from their respective tables. (With the exception of Products that uses a query in order to filter out products that have been flagged with a Yes/No field called "Obsolete")

Stores are linked to the Distributor they fall under and Distributors are linked to the Employee that is our rep for them as well as the Country they are located in.

My form doesn't have any subforms. This is the area I should be looking into?

I think you might be putting the cart before the horse. Let's analyize what data you want to store.

Your main record is going to be the Incident. So we will start with that. Now lets examine what data you want to associate with each Incident.
1. Customer. Can the customer making the complaint make more than one complaint in their lifetime? If yes, then the Customer Info should be stored in it's own table, and their Primary Key should be stored in the Incident Table.
2. Distributor. How do you determine the Distributor? Is it related to the Product? (My guess is Yes). If so, you dont need to record that in the incident table.
3. Store. Are you looking to record the store that the the Incident started from? Or are you looking to record the store that sold the order that the Incident began from? Either way, if a store can have more than one incident, you need to a separate table of stores and only record the StoreID in the record.
4. Product. Can there be more than one product associated with this Incident? You've already said Yes, so that needs to be recorded in it's own table. You should store the IncidentID in that table.
5. Employee Reporting. Is their only one Employee who will be reporting the Incident? If yes, you can store the EmployeeID in the incident table.

So, the Incident table will include the following:
IncidentID (Primary Key)
CustomerID (Foreign Key)
EmployeeID (Foreign Key)
StoreID (Foreign Key)
IncidentDate
IncidentNotes

I'm a bit confused on the Distributor. If the Distributor is based on the Store, then you dont need to record that. You've recorded the StoreID, which can be used to determine the Distributor. The Sales Rep can also be found through the Distributor, which is found through the store, which is recorded via the StoreID.

Once you understand how the table structure should look like, setting up a form should be easy. Your main form will be the Incident. All the fields with IDs should be combo boxes where the row source will be the respective tables. A Subforms can also be used to display all the data for the fields with IDs (For example, you could have a subform that when you choose a Store, it will show the Store, Store details, Distributor details and Employee Rep details since they are all tied together via IDs).

As for adding products to the Incident, that data will be stored in a separate table. You can use a combo box to pick the Product, then a command button to insert that data into the separate table.

 

[Scooterbug]

Sorry, and I should go on to say that I know it seems like there is a little redundancy there, but due to the information that I'm actually given on these Incidents (which I hope to change in the near future) I often have to leave some fields blank.

Example: CustomerName in the Form/Incident field I use to refer to the person filing the complaint: Store Owner, Distributor, Consumer, or otherwise.

Then it shouldn't be Customer Name...it should be CustomerType. You can create a table with an ID and CustomerType.

The ComboBox (and linked field) for Store and Distributor I use to later down the road look for trends and track complaints by each store or distributor. That's why I have to use separate fields for CustomerName, phone number, etc.

The linking of Distributor allows me to get the Distributors information like address or phone number (or Stores based on Store) from the corresponding tables.

Then you should have a table with just the Distributor or Store. You can then store the ID of the Distributor or Store and use that to pull up specific data.

Some small data issues have come up. One early example was that in theory when I select a store from the Combo Box, one would think that "Distributor" should fill out automatically because each store should be linked to it's corresponding distributor. Unfortunately, I'm not given all the information that I need for that, and some stores are left unlinked to distributors. I hope to fix that up as I add more stores to the Stores table, as I've been adding them as I come across any new one in a complaint I enter.

This goes back to table structure. Can a store have muliple Distributors? If so, then it's a one to many relationship. That relationship should be stored in a separate table:

tblStoreDistributors
StoreDistributorID (PK)
StoreID (Foreign Key - Record the Store ID here)
DistributorID (Foreign Key - Record the DistributorID here)

This way, you can query the table and say: "Give me all the distributors for the store with the ID of X) You now have a list of Distributors for that store.

You can also use the distributor(s) in your incident table. If more than one distributor is involved, you can setup a table like above, but substitue StoreID for IncidentID.

 

[eTom]

Yeah, some of it seemed a little backwards when I was making it.

1. Yes, customers can make more than one complaint, but in the 400 Incidents I've entered, I've only come across one duplicate. 99% of them are from consumers. The Distributor and Store that I enter is basically for tracking back to see if there are any trends based on Store.

2. Distributor should be based on store. Each distributor sells to stores in a given area. (This state, this section of this country, etc.) In theory I should be able to determine it based on the Store they provide, but if a complaint comes from a Distributor itself (a problem at the warehouse, for example) then there is no store to speak of, so I have to have a place to enter Distributor instead.

3. Yeah, I plan to record the store that started the incident if one occurs, but most of the time it's the store that sold it to the person filing the complaint. That's *if* I can get that information from them. Out of the 400 or so records about 75% of them are blank for "Store." The stores are listed in a separate table and the combo box pulls the data from that table to allow me to choose, and records the StoreID (which is linked via the relationship window.)

4. I had planned on having only one product per Incident. This is where I am mainly confused, I think. What you're saying is that I can make the "Product" field in my main Incident table a table of it's own? Is this a sub-table? That'd allow me to pick more than one product? I'd need to associate a specific lot/batch number per product, as well. The problem with our lot/batch system is that they are not unique. They are based on the date a product was made, so each ProductID (pulled from the Products table) can have multiple lot numbers, and each lot number can be assigned to more than one product if more than one product was produced that day.)

5. Yes, there is only one employee reporting the Incident. Sometimes this corresponds with the Employee assigned to the Distributor, sometimes it's someone who's answering the phones that day. Sometimes it might be myself. I've left it as a combo box to select an employee populated by the Employee table. For tables like this, is it common or acceptable to leave the EmployeeID as a simple sequential auto-number?

Here are the fields in my Incident table:
IncidentID (Primary Key)
Date Submitted
Complaint Level (Distributor, Store, Consumer, In-House)
Contact Name
Country
DistributorID (foreign key)
StoreID (foreign key)
ItemNumberID (foreign key)
LotNumber
Quantity
Complaint Details (memo box)
Corrective Action (memo box)
EmployeeID (foreign key)
Resolved (checkbox)

I think my form layout follows some of what you're saying. All the fields that are associated and linked to other table IDs (itemnumber, distributor, store, employee) are all combo boxes that pull the data from the table (and in the instance of ItemNumberID it pulls from a query that filters out obsolete products, as the data goes back about five years.)

I think the part that's confusing me the most is the idea of inserting Products into an Incident. The way the form works now, I pick the Product from the combo box populated from my Products table, and then I fill in the memo field for "Complaint Details."

Thanks again. Any input is great and gets me learning about this stuff.

I think you might be putting the cart before the horse. Let's analyize what data you want to store.

Your main record is going to be the Incident. So we will start with that. Now lets examine what data you want to associate with each Incident.
1. Customer. Can the customer making the complaint make more than one complaint in their lifetime? If yes, then the Customer Info should be stored in it's own table, and their Primary Key should be stored in the Incident Table.
2. Distributor. How do you determine the Distributor? Is it related to the Product? (My guess is Yes). If so, you dont need to record that in the incident table.
3. Store. Are you looking to record the store that the the Incident started from? Or are you looking to record the store that sold the order that the Incident began from? Either way, if a store can have more than one incident, you need to a separate table of stores and only record the StoreID in the record.
4. Product. Can there be more than one product associated with this Incident? You've already said Yes, so that needs to be recorded in it's own table. You should store the IncidentID in that table.
5. Employee Reporting. Is their only one Employee who will be reporting the Incident? If yes, you can store the EmployeeID in the incident table.

So, the Incident table will include the following:
IncidentID (Primary Key)
CustomerID (Foreign Key)
EmployeeID (Foreign Key)
StoreID (Foreign Key)
IncidentDate
IncidentNotes

I'm a bit confused on the Distributor. If the Distributor is based on the Store, then you dont need to record that. You've recorded the StoreID, which can be used to determine the Distributor. The Sales Rep can also be found through the Distributor, which is found through the store, which is recorded via the StoreID.

Once you understand how the table structure should look like, setting up a form should be easy. Your main form will be the Incident. All the fields with IDs should be combo boxes where the row source will be the respective tables. A Subforms can also be used to display all the data for the fields with IDs (For example, you could have a subform that when you choose a Store, it will show the Store, Store details, Distributor details and Employee Rep details since they are all tied together via IDs).

As for adding products to the Incident, that data will be stored in a separate table. You can use a combo box to pick the Product, then a command button to insert that data into the separate table.

 

[Scooterbug]

Yeah, some of it seemed a little backwards when I was making it.

1. Yes, customers can make more than one complaint, but in the 400 Incidents I've entered, I've only come across one duplicate. 99% of them are from consumers. The Distributor and Store that I enter is basically for tracking back to see if there are any trends based on Store.

It's that 1% that will get you everytime. If there is ever a remote chance that a record can contain more than one of something, you are better off to err on the side of caution and create a separate table and use a relationship.

2. Distributor should be based on store. Each distributor sells to stores in a given area. (This state, this section of this country, etc.) In theory I should be able to determine it based on the Store they provide, but if a complaint comes from a Distributor itself (a problem at the warehouse, for example) then there is no store to speak of, so I have to have a place to enter Distributor instead.

Then the Distributors should have their own table. You can store the relationship between stores and distributors in a separate table.

4. I had planned on having only one product per Incident. This is where I am mainly confused, I think. What you're saying is that I can make the "Product" field in my main Incident table a table of it's own? Is this a sub-table? That'd allow me to pick more than one product? I'd need to associate a specific lot/batch number per product, as well. The problem with our lot/batch system is that they are not unique. They are based on the date a product was made, so each ProductID (pulled from the Products table) can have multiple lot numbers, and each lot number can be assigned to more than one product if more than one product was produced that day.)

And This:
Now I have come up to complaints involving multple products. Sometimes dozens. Rather than enter each of these fields in the form every time, I want to speed up the process a little. Ideally, I'd like the form to have the same text or combo boxes up top for things like customer name, phone, location, etc... but then a table below where I could enter product, date, etc. That way I could enter multiple products and date without having to enter the customer info every time.

The trick is that i want each of those products to be it's own record. Is it even possible to have a form create multiple records using some fields up top that are common for each record it creates, while a table at the bottom has info that's different for each of these records?

What you orginally described is what is called a One to Many Relationship. That is ONE incident can have MANY products associated with it. If you record only one product in the incident table, you are forced to create duplicate incidents with only the product changing. This violates the rules of Normalization and makes your life a lot harder. So instead, you setup a new table. This table will record all the data pertaining to the Product (Product Name, lot number, etc). In order to find all the products that relate to the specific incident, you also record the IncidentID. This way, you can now group together all the products associated with the particular incident. You can have as many products associated with the Incident that you want.

5. Yes, there is only one employee reporting the Incident. Sometimes this corresponds with the Employee assigned to the Distributor, sometimes it's someone who's answering the phones that day. Sometimes it might be myself. I've left it as a combo box to select an employee populated by the Employee table. For tables like this, is it common or acceptable to leave the EmployeeID as a simple sequential auto-number?

Yes, it's common and acceptable. In fact, it's the Normalized way to do it. Since Employee information can be attached to several different things (Distributor and Sales Rep, Incident and Reporting Employee) you refrain from storing duplicate data by using the ID number to reference the data. This also allows you to change the data in one place (For example, an employee gets married and her last name changes) and it will reflect that change wherever the employee data is being used.

I think the part that's confusing me the most is the idea of inserting Products into an Incident. The way the form works now, I pick the Product from the combo box populated from my Products table, and then I fill in the memo field for "Complaint Details."
Thanks again. Any input is great and gets me learning about this stuff.

You aren't inserting Products into an Incident. You are creating a list of products and inserting the IncidentID into that list. If you look at it that way it may make more sense.

I know I work better with an example...I could do up a quick little db to illustrate if that will help you.

 

[eTom]

What you orginally described is what is called a One to Many Relationship. That is ONE incident can have MANY products associated with it. If you record only one product in the incident table, you are forced to create duplicate incidents with only the product changing. This violates the rules of Normalization and makes your life a lot harder. So instead, you setup a new table. This table will record all the data pertaining to the Product (Product Name, lot number, etc). In order to find all the products that relate to the specific incident, you also record the IncidentID. This way, you can now group together all the products associated with the particular incident. You can have as many products associated with the Incident that you want.

I think I am starting to see what you mean. My existing Incident table will still contain CustomerName, ComplaintDetails (memo), but will link to a seperate table that will list the Product (which links to the Product table), lot number and a field I use called ComplaintType which is just a few categories such as Damaged Package, Quantity of Product, Consistency of Product, etc.

I'll call this table ProductsInvolved or something, and then link a field in it called IncidentID to the primary key IncidentID from my main Incident table. Each record in there will list the IncidentID, a ProductID involved (linked to the primary key from Product table), lot number (entered by the user) and "Complaint Type" to record what was wrong with that particular case of product.

That'd be one to many (like all my other relationships) because one IncidentID could link to many ProductsInvolved, right? What would I use for the primary key in ProductsInvolved table... just a sequential numbering?

And once more, thank you so much for all your help. It's... frustrating to be so overwhelmed by something. ^^

 

[Scooterbug]

I think I am starting to see what you mean. My existing Incident table will still contain CustomerName, ComplaintDetails (memo), but will link to a seperate table that will list the Product (which links to the Product table), lot number and a field I use called ComplaintType which is just a few categories such as Damaged Package, Quantity of Product, Consistency of Product, etc.

Correct!

I'll call this table ProductsInvolved or something, and then link a field in it called IncidentID to the primary key IncidentID from my main Incident table. Each record in there will list the IncidentID, a ProductID involved (linked to the primary key from Product table), lot number (entered by the user) and "Complaint Type" to record what was wrong with that particular case of product.

Correct Again!

That'd be one to many (like all my other relationships) because one IncidentID could link to many ProductsInvolved, right? What would I use for the primary key in ProductsInvolved table... just a sequential numbering?

99% of the time, I and most others, will use the Autonumber datatype as the primary key generator. This guarentees a unique number.

And once more, thank you so much for all your help. It's... frustrating to be so overwhelmed by something. ^^

Most of us have been there. I know I was when starting out with db programming. Once you get use to it...it will be second nature

I had a few minutes...so I did up a quick little db to illustrate everything. You might find it useful.

 

[eTom]

Correct!


Correct Again!


99% of the time, I and most others, will use the Autonumber datatype as the primary key generator. This guarentees a unique number.



Most of us have been there. I know I was when starting out with db programming. Once you get use to it...it will be second nature

I had a few minutes...so I did up a quick little db to illustrate everything. You might find it useful.

Now, the only problem I foresee is the 400 entries I've already made. They have the items listed in the incident table already. Can I migrate them to this new ProductsInvolved table? Or should I create a new "Incidents2" table, copy the data I want into that one, and then create my ProductsInvolved table, copy the data needed into that one, and then get rid of the current "Incidents" table that I have?

I've spent the better part of two weeks entering some of this data. (I swear I need to work on everyone's idea of "record-keeping")

 

[Scooterbug]

Now, the only problem I foresee is the 400 entries I've already made. They have the items listed in the incident table already. Can I migrate them to this new ProductsInvolved table? Or should I create a new "Incidents2" table, copy the data I want into that one, and then create my ProductsInvolved table, copy the data needed into that one, and then get rid of the current "Incidents" table that I have?

I've spent the better part of two weeks entering some of this data.

Here is what I would do:
Create a query using your current incident table. Pick 3 or 4 fields, that if put all together would make a unique string (Employee, Distributor, Store, Date Submitted for example). Then switch to sql view. (Top left icon on the ribbon). Add the word DISTINCT after select. Run the query. This will give you a list of incidents with only 1 line for each incident where the fields you added are identical. You can then turn it into an Append query and add them to the new incident table (You could even add the tables and links for the Employee, Distributor, etc tables if you havent stored the IDs).

You should now have a table with each Incident in it's own record. If you use the form on the db I posted as a templet, you can then cycle through the Incidents and add the products and such to the ProductsInvolved table.

(I swear I need to work on everyone's idea of "record-keeping")

A giant wet noodle might get them into shape

 

[eTom]

Here is what I would do:
Create a query using your current incident table. Pick 3 or 4 fields, that if put all together would make a unique string (Employee, Distributor, Store, Date Submitted for example). Then switch to sql view. (Top left icon on the ribbon). Add the word DISTINCT after select. Run the query. This will give you a list of incidents with only 1 line for each incident where the fields you added are identical. You can then turn it into an Append query and add them to the new incident table (You could even add the tables and links for the Employee, Distributor, etc tables if you havent stored the IDs).

Wow, that was a lot about queries. I have a series of video tutorials that I'll have to run through again. I think there were four or five about queries.

Luckily, at the moment, I don't have any incidents that are identical, at the moment. I saved all of the ones with multiple product complaints at the same time for the end. Thinking back, had I just come and asked someone when I first came across this, I wouldn't have nearly so much data entered.

So what I'll do is try out a query that gives me just the fields I want, and Append it to the new Incident Table. A query listing the other fields, the ones that'll go into my IncidentDetails table and Append them into that. Then I just relink the relationships to the new tables as required.

I'm sure I'll mess up something along the way getting there, but at least I have lots of backups! ^^

Thanks again. I'll definitely let you know how it goes when all is said and done.

 

[eTom]

Well I'm plodding along and thought I'd post an update.

I got Incidents and IncidentDetails separated into two different tables, and created the relationship between the IncidentID that each contains. I haven't attempted to create a form yet that will allow me to enter them, I'm still working on re-connecting all the relationships.

If I go to my Products table now and choose a product at random, when I click the little + next to it, it drops down and lists all the different IncidentDetails records that are associated with it. (Which of course only lists five fields: IncidentID, ProductID, which are both foreign keys from other tables, and LotNumber, Quantity, and Category which are all fields entered from my old form before changing this.) There is another field, the primary key field for the IncidentDetails table that is just an AutoNumber so I hid it and won't worry about it for now.

I assume that if I want to look up more details on the specific Incidents listed under each ProductID I do that by building a Query or a Report, correct? Something I can worry about later when I've finished entering my data.

Now I'm at the point where I think I'm ready to create a form that will allow me to create a new record in the Incidents table, and will create a record in IncidentProductDetails table for each product that I enter. (Linked by the IncidentID assigned to the record in the Incidents table.

Using the Form Wizard, I have to ensure that I include inputs for fields from both tables, correct? How do I add a table where I can enter multiple Products?

 

[Scooterbug]

I assume that if I want to look up more details on the specific Incidents listed under each ProductID I do that by building a Query or a Report, correct? Something I can worry about later when I've finished entering my data.

A bit about Databases. Tables are used to store data. Queries are use to gather data up without having to go into the table itself. Queries 99% of the time can be used in the same place as tables. Also with queries, you can filter the data by adding criteria. Forms are used to Add/Edit/Display data. Reports are generally used to display data/print.
So, if you want to create a print out of incidents, you can design a query as the record source for a report. In your case, you would also have a subreport that would display the Product information.

Using the Form Wizard, I have to ensure that I include inputs for fields from both tables, correct? How do I add a table where I can enter multiple Products?

With the form, since you have a one to many relationship the best thing to do is to have a form and a subform. The main form will be based off of a query that contains the Incident table. You then create a form with the information from the table that is storing the Product information for the Incident. You then add a subform to the incident form. If you follow the subform wizard, you will choose the option to use an exsisting form, then link the subform to the main form by the IncidentID.
What this does is displays the data from the ProductInvolved table on the same form as the Incident. It filters the ProductInvolved data to only include the row(s) that have the same IncidentID in them as the IncidentID on the main form.

 

[eTom]

Wow, when I created the form with sub-form via the Form Wizard, it automatically created the IncidentProductDetails form for entering multiple Products involved at the bottom, and it also assigns the IncidentID to match the new Incident record that I'm creating! I didn't think it would be quite so easy or intuitive once I got the tables set up a little more correctly.

One issue I'm having now is my Country table with the little "expand" + that I get next to records that are related to records in other tables. (ie: in my Incidents table, if I hit the + next to a record it shows me the related IncidentProductDetails records. Same if I look at Stores or Distributors. It shows IncidentProductDetails related to that Store or Distributor)

When I click on the + in Country table, the record expands to show the same record below itself! The + next to that one will expand to show the same record, and so on and so on.

I've removed every Relationship to the Country table, yet it still is happening, and I'm unsure what I did differently in this table.

 

[eTom]

Wow, when I created the form with sub-form via the Form Wizard, it automatically created the IncidentProductDetails form for entering multiple Products involved at the bottom, and it also assigns the IncidentID to match the new Incident record that I'm creating! I didn't think it would be quite so easy or intuitive once I got the tables set up a little more correctly.

One issue I'm having now is my Country table with the little "expand" + that I get next to records that are related to records in other tables. (ie: in my Incidents table, if I hit the + next to a record it shows me the related IncidentProductDetails records. Same if I look at Stores or Distributors. It shows IncidentProductDetails related to that Store or Distributor)

When I click on the + in Country table, the record expands to show the same record below itself! The + next to that one will expand to show the same record, and so on and so on.

I've removed every Relationship to the Country table, yet it still is happening, and I'm unsure what I did differently in this table.

 

[eTom]

Wow, when I created the form with sub-form via the Form Wizard, it automatically created the IncidentProductDetails form for entering multiple Products involved at the bottom, and it also assigns the IncidentID to match the new Incident record that I'm creating! I didn't think it would be quite so easy or intuitive once I got the tables set up a little more correctly.

One issue I'm having now is my Country table with the little "expand" + that I get next to records that are related to records in other tables. (ie: in my Incidents table, if I hit the + next to a record it shows me the related IncidentProductDetails records. Same if I look at Stores or Distributors. It shows IncidentProductDetails related to that Store or Distributor)

When I click on the + in Country table, the record expands to show the same record below itself! The + next to that one will expand to show the same record, and so on and so on.

I've removed every Relationship to the Country table, yet it still is happening, and I'm unsure what I did differently in this table.

 

[Scooterbug]

Wow, when I created the form with sub-form via the Form Wizard, it automatically created the IncidentProductDetails form for entering multiple Products involved at the bottom, and it also assigns the IncidentID to match the new Incident record that I'm creating! I didn't think it would be quite so easy or intuitive once I got the tables set up a little more correctly.

Now you can see why having a database with the table setup correctly makes things a whole lot easier

One issue I'm having now is my Country table with the little "expand" + that I get next to records that are related to records in other tables. (ie: in my Incidents table, if I hit the + next to a record it shows me the related IncidentProductDetails records. Same if I look at Stores or Distributors. It shows IncidentProductDetails related to that Store or Distributor)
When I click on the + in Country table, the record expands to show the same record below itself! The + next to that one will expand to show the same record, and so on and so on.
I've removed every Relationship to the Country table, yet it still is happening, and I'm unsure what I did differently in this table.

I wouldn't worry about it too much...as tables shouldn't be use to input data. Those +s show up when you setup relationship in the Relationship window. Without seeing the actual db, i'd only be guessing why it's behaving that way.