Fairly New and Need Help (3 Viewers)

[Jack F]

I am working on a revamp project at work. I have built an Access database containing 5 tables. I am unable to get the information of a single record to display customer name and any requirements information the customer may have when typed into a new field. I am also trying to display a pop-up warning message if there are restrictions tied to customer name and address. As of this point in the build, I do not know how to code and my database knowledge is limited to a few college courses.

Currently, the CustName is the primary key. Again, I am trying to pull single record information and display messages from other tables and display in Table 1 (Primary Table). Thanks for any help, thoughts and/or better design, since this is the preliminary/concept build.

Table Design 1:

Table Design 2:

Table Design 3:

Table Design 4:

Table Design 5:

 

[Gasman]

Do yourself a favour. Use auto number as PK. Guaranteed to be unique.

 

[RonPaii]

You need to revisit your table structure.
Customer Name is a bad choice for primary key. What are you going to do when a customer name changes?
Customer Name is defined as Short Text or Long Text in your tables. You cannot link those 2 types.
Add a autonumber ID field as the primary key to your main customer table.
Add a long CustID indexed field as the foreign key to customer to all your other tables holding customer information.
With these 2 things done, you can link the tables in the query editor.

 

[Jack F]

Thank you. I have made the Primary Key (Auto) on all tables. I have added the CustID field to all tables as long and indexed. I have changed all custname long to custname short.

 

[moke123]

While you're at it you should eliminate any spaces and special characters in your field names, as well as all your other objects.

 

[Jack F]

While you're at it you should eliminate any spaces and special characters in your field names, as well as all your other objects.

Thank you, done.

 

[Jack F]

You need to revisit your table structure.
Customer Name is a bad choice for primary key. What are you going to do when a customer name changes?
Customer Name is defined as Short Text or Long Text in your tables. You cannot link those 2 types.
Add a autonumber ID field as the primary key to your main customer table.
Add a long CustID indexed field as the foreign key to customer to all your other tables holding customer information.
With these 2 things done, you can link the tables in the query editor.

Thank you, Done.

 

[XPS35]

Are the relations from table 1 to tables 2, 3, 4 and 5 one-to-many? If not, why do you have separate tables?
I hope the tables have meaningful names. It would have helped us to know the names.
Why do you store the address in two tables?
What is the meaning of the Display Message field in each table?

 

[Jack F]

Are the relations from table 1 to tables 2, 3, 4 and 5 one-to-many? If not, why do you have separate tables?
I hope the tables have meaningful names. It would have helped us to know the names.
Why do you store the address in two tables?
What is the meaning of the Display Message field in each table?

The concept names are given in my original post. They are semi-meaningful at this point. There is a one to many relationship between the tables. The addresses are in two tables to deliniate commercial vs. general public accounts. The Display Message was an attempt on retrieving a single record with notes, requirements, restrictions and showing a pop-up or modul message. I am just not sure how to make that happen.

 

[plog]

Here's what we need to help you:

1. A simple explanation of what your organization does. Pretend its career day at an elementary school and tell us what you do. Use simple terms and no database jargon .

2. Very high level explanation of what this database does for you. Use very little database jargon.

3. A copy of the existing systems data. Or a screenshot of it. Make sure it includes sample data.

 

[isladogs]

Each field should only contain one item of data.
So you should split Customer Name into LastName and FirstName fields and only store that in one table.

Similarly split the CustomerAddress into several fields e.g. AddressLine1, AddressLine2, Town, Postcode and only store in 1 table.
Add an extra field AddressType and populate as Commercial or General Public.
Better still, add an AddressType lookup table with those as values 1 and 2 and enter those values in the AddressType field in the Address table

Go through each of your fields in the same way, splitting where ever two or more data items are included.

 

[Jack F]

Here's what we need to help you:

1. A simple explanation of what your organization does. Pretend its career day at an elementary school and tell us what you do. Use simple terms and no database jargon .

2. Very high level explanation of what this database does for you. Use very little database jargon.

3. A copy of the existing systems data. Or a screenshot of it. Make sure it includes sample data.

I am a service dispatcher for a small business. The database is an overhaul move away from MS Excel because, trying to do this in spreadsheets will be complicated for me. The idea of the database is to put all relevant customer information in one spot in front of me. So, the database will act as a dispatch resource and allow me to get the information I need at the time a service request is made. The customer can be commercial or residential. Relevant information: Customer Name, Customer Address, Does the customer have special requirements or restrictions to their order (Currently labeled as Notes)? Can I dispatch to the location or customer because of those restrictions or requirements? I assume this may need to be a nested subform but, I am not sure. Once I have the customer information in one spot in front of me, I can schedule and give the responding technician the information I see. So, there is less confusion for all sides.
Sample Data will look something close to: CustName: Scooby Doo / CustAddress: 101 Field Street / CustRequirements: Need PO# and pay immediately or CustRequirements: Cannot dispatch because of past due account / Notes: Cust needs XYZ service for or containing given information from CustRequest. I can't provide systems data because I have Personally Identifiable Information already inserted.

 

[Jack F]

Each field should only contain one item of data.
So you should split Customer Name into LastName and FirstName fields and only store that in one table.

Similarly split the CustomerAddress into several fields e.g. AddressLine1, AddressLine2, Town, Postcode and only store in 1 table.
Add an extra field AddressType and populate as Commercial or General Public.
Better still, add an AddressType lookup table with those as values 1 and 2 and enter those values in the AddressType field in the Address table

Go through each of your fields in the same way, splitting where ever two or more data items are included.

Good Idea

 

[mike60smart]

Can you upload a copy of your database?

 

[Jack F]

Can you upload a copy of your database?

I can if I delete the current information or rebuild a scratch version of it. The existing database has Personally Identifiable Information already filled in.

 

[mike60smart]

I can if I delete the current information or rebuild a scratch version of it. The existing database has Personally Identifiable Information already filled in.

Yes just remove any confidential data

 

[Jack F]

Can you upload a copy of your database?

I will send upload it tomorrow. We are about to close for the day.

 

[mike60smart]

I will send upload it tomorrow. We are about to close for the day.

OK

 

[arnelgp]

there is a sample customer service database template from microsoft.
the attached is the accdb from that template.

 

[Jack F]

Yes just remove any confidential data