Relational tables cust # to cust details (1 Viewer)

[damageinc86]

I am trying to setup a database for my desk at work which will eventually have thousands and thousands of customer numbers, with details about each customer. Each customer number will have a bunch of details like dates and document numbers and such, each needing their own row for one date's worth of info. So I was wondering which way would be more efficient to store this:

A. One table with all customer numbers (primary key), and a table for each customer with all the details within (relationship I assume?).
Or B. One huge table with duplicate customer number entries for each row of data needed.

I started out using the second method with only a few customer numbers and only a few rows of dates entered. I have successfully setup a query form that will run the filter query in a subform based on what customer number you type in. It shows each row with that customer number, along with all that info. But what I'm worried about is having an incredibly huge table with so many duplicate customer numbers each with their own row, and just having it be too much all in one place.

If I go the table route, then each customer will have their own table named after their customer number, which obviously would mean thousands of tables. I don't know if that's bad practice? But I was thinking that the main table with just customer ID#'s could call upon whichever table matched that customer ID and show the records from that table in a subform. I couldn't figure that out on my own at all by using relationships, so I gave up.

any ideas on how to set this up?

 

[jdraw]

Please tell us how you determined this:

If I go the table route, then each customer will have their own table named after their customer number, which obviously would mean thousands of tables.

 

[Galaxiom]

Definitely not a table for each customer and probably not your other structure either.

Your question shows that you don't really understand relational data structure. You need to study this before continuing. Then post your table structure for us to review if you have it right.

 

[damageinc86]

Please tell us how you determined this:

I determined it by the fact that if I gave a table to each customer number, and within that table it had all the different dates of transactions, then there would be way too many tables.

Well I obviously do not understand the relationship stuff, which is why I sought guidance here in the Theory and practice of database design. This would be in the theory part I assume, since I'd like to get a grasp on how to design this before I put loads of data in.

For my end result I just want a simple Customer ID search box, that looks at a table full of just Customer ID's, and when it matches the txtbox contents, it will then go seek out another match from somewhere else that will, as I thought, be a place that has just that Customer ID's transactions (dates, name, location of document, etc.) within it.

 

[damageinc86]

So i watched some youtube videos about relationships, and it made a little more sense. But the examples usually used don't seem to have a lot of similarities to the data that I will have at the end stage I guess you could call it. The real data that will be aggregated by having a relationship. Or I'm not "seeing" how to break down the data I'm really trying to get to, into a table that can have a neat relationship.

So we have microfilm reels with about a thousand documents on each reel. Every 6 years they purge the records that contain all the coordinates, from an old DOS looking "mainframe" program. What I mean by coordinates, are the date a transaction took place, it's batch number, it's actual effective date on the letter (document), the content of the letter in a short word or two, the number of the reel, the batch number on the reel, and the document number on the reel.

So once those records have been purged from the mainframe, they are printed to microfiche cards. So if we ever have to look something up that took place 6 or more years ago, then we have to find the customer number on one of the microfiche cards, put it into a reader, and find the transactions we are looking for, then which microfilm reel it's on, bla bla. So we can THEN, go look it up on the actual reel. So on the microfiche card, the one customer number has anywhere from 10-50 or so transactions under the customer number and name header, until there is a new customer number and there's more under that. Visually it is in column format, just no column names. But it's all lined up as if it was in a spreadsheet or something.

So since there are so many columns associated with one customer number, and so many records under that, I guess I can't conceptualize how to contain that data without having it always be associated with the customer number. If that makes any sense.

 

[The_Doc_Man]

Using normalized relationships, you are talking about maybe 3 or 4 tables from the data you have presented so far. What you need to do is take a look at your business model FIRST, as that is always "right" (even if it isn't...).

Your elements as identified so far are customers with ID numbers that will probably become your prime key (PK), some sort of transaction records with ID numbers that become a PK but which will include the customer ID as a foreign key (FK). You have microfiche cards which presumably have ID numbers that will presumably become a PK.

I might break this up as follows:

table Customers: CustID (PK), customer-specific data

table FicheCards: CardID (PK), location data, other things relevant only to fiches

table Transactions: TransID (PK), CustID (FK), CardID (FK), things specific to transaction.

For each of these, if you want to save space, there is the possibility that you would add codes to replace longer strings in those tables. In each case, the code ID becomes a PK in the code translation table but an FK in the table that uses it as a description or attribute.

The fact that you have thousands of these transactions is immaterial for the first several iterations of this beasty (because ANYTHING based on a real-world business model has to evolve when the business evolves). Eventually, if you get enough data, you have to upconvert your tables to something like (but not limited to) SQL Server, but access can remain as your front-end interface even if you do that. So you can start this in Access. At my last job before I retired, I had database tables that had 600,000 entries with several FKs in them to reduce the size of what I was seeing.

Now let's talk about implementation. From your remarks, I infer that you are a novice at database design. Here is the first fact you need to know: Access is dumber than a box of rocks. You have to tell it how you want things done because it knows NOTHING about your business.

That means you have to have a good idea of what you want it to do. I use the "Old Programmer's Rule #1" as the short reminder: If you can't do it on paper, you can't do it in Access.

So what you do first as noted earlier is analyze your business on paper. Draw up your model and dataflow as a diagram to see what you need to keep. Look at what you need to track and try to separate out items from each other based on their different properties. It is a strong indicator that two business entities are different if they have different properties - and that means you have identified two items that deserve their own tables. But my words can be ambiguous so I'll clarify.

Things can have the same properties but different values. For instance, two customers can have two different names but they both have the properties FirstName, LastName, MiddleName, ContactPhone, ContactEMail, etc etc. But two of your transactions will have different properties like the CustomerID, TransactionID, and FicheCardID (once purged). Obviously different from customers when you look at it that way.

Once you have determined your distinct entities, you can start laying out data. Analysis of those entities in your business data flow will tell you how they are related, and then you can think about establishing those relationships. Analysis of how your data flow occurs will also tell you what operation you need to consider in the form of action (INSERT, UPDATE, DELETE) queries, search (SELECT) queries, and perhaps some forms to help manage those operations.

Remember this rule forever: The real-world data drives the design. The design doesn't drive the real-world data. That is why you analyze the real-world business first.

This should give you enough to chew on to get you going in the right direction. Come back when you have more specific question, but remember, it ALWAYS starts with data.

 

[damageinc86]

Wow thank you for the thoughtful post. That is a lot to chew on, and some of it makes sense. But yes the novice in me is very lost still with the theory. I will only be using the microfiche card to print out a single customer's records. Then I want to go one by one, and put those into the database, so I don't have to use the fiche cards anymore, it takes too long to look up records for my job that way and I was hoping to move that data to access.

Fiche:

Reel:

The real data in question is the data that points to where a document is on a microfilm reel. So at the top of the section is a customer number "1234567", followed by last, first, middle, suffix. To clarify further, since we call them customers and transactions, I was just used to that terminology. These are driving record related documents. So the transactions are really what was sent to the customer, that we now have to printout for a records request.

then under that are all the transactions. These don't need to be updated or changed, because it is just looking at what happened in the past. So I have to look up what notice letter was sent to the customer for a certain violation or infraction. All I have to go on is the name of the infraction, and the effective date of the letter. Then I can go to the microfiche card and find that effective date in a table underneath the license number and name, and there will be all the details, with coordinates on the right hand column for the location of the doc on the microfilm reel. On the microfiche card it literally looks like a table, which is how I began to see each customer as a table, which seems now as a flawed way of actually doing it in access.

A typical entry on the fiche looks like this:

The problem I'm having with conceptualizing this data in access, is that sometimes there are two items that have the same segment date, like a return envelope for the suspension letter mailing, but different counter dates, yet I need to print out both because they relate to each other, and to the customer number. I don't understand how to divide up all the transactions in access, and why they would have their own unique PK, when i need to see all of the entries for that cust number so I can search for the right one. If each transaction line, had it's own autonumbered PK, then wouldn't I have to go to the customer table relationship, and manually type in which transaction numbers apply to that cust number? I know I'm a big newb, but that seems like double the work because first I have to do all the data entry, then go back and tell the cust id table which transactions are tied to a certain cust number. If I even understand that right, which I probably don't. But anyways, that's my predicament.

 

[jdraw]

Just out of curiosity, have you approached any commercial firms who do conversion of
microfiche to digital media as a business.
Might be worth getting a few quotes to see what your options might be.

Good luck.

 

[damageinc86]

No i'm a low level employee, I am just running a desk that processing this stuff. I was hoping that if I got a decent working database going on my own first, that my boss might find it a worthy endeavor and back me up on it. Plus it would make my job a lot easier when I no longer have to go through the entire process of the microfiche lookup.

 

[jdraw]

You might try google with some of these terms
convert microfiche to ms access database

Some people offer free quotes.

Might be good for you and your management to know what alternatives exist.

 

[damageinc86]

Yeah I looked at those search results, but really I don't find it to be a problem other than the fact that the records are on microfiche. Visually, on the enlarged image it looks exactly like what I re-created in the image. It's a spreadsheet format really, so just imagine it was a directory full of excel spreadsheets saved with a filename of the customer #. I think it would function better if all the customer transactions linked to their number could be in a database instead of having to find and open each excel file.

So far, it's a flat database with each transaction date record having the correct customer # field as a part of it. So my simple query by customer # shows every transaction date record based on the fact that when I'm doing the data entry, I am adding the cust# to the first field of each transaction date. So those show up as being associated when searching by cust#. That works just fine since they are static records, and since when I'm doing the data entry I'm going in order and including all of the transaction records, so I wouldn't need to go back and add anything. But I know there is something with relationships that might be better,...just still don't understand how to make the relationships between only one set of specific transaction dates (10-50 usually) point to only one customer # if the transactions from thousands of cust#'s are in their own table. I guess if I went in order like I am doing now, then when I was done with a certain set of transactions, i could select them all and have their relationship be only to a certain cust# before moving onto the next set. Is that what some of you are thinking?

 

[The_Doc_Man]

Ah, you are looking at unrolling the fiche cards to put them back online. You are telling us that you actually want to reverse the storage to fiche. It is not clear but also perhaps not immediately material whether you want to point to the fiche anyway, but maybe you do. Let's look at your spreadsheet data.

The first line of the fiche shows a person's name and code number. Then you have a detail section in which you show some dates; some batch, reel, and doc(ument?) numbers; a reason code and a type code. So, if these represent transactions, you have identified two tables and possibly others. You have to decide whether you will retain the fiche location information as a separate issue.

The first table is still the person table: CustID (PK), FirstName, Middle Name, Last Name, Name Suffix (which might be blank).

The next table is your transaction table: Since you got the person info from the upper part of the card, you will have fields like this: Optionally, you can have a Transaction ID which is an autonumber and PK for that table. You will have a person number in each transaction record and it will be the FK to the person table. That way, you don't have to repeat four name fields each time. Then you would have the two dates, your batch numbers, reel number, and document number. All numbers so far, right? Then you could have two more fields based on lookups for the various type codes and reason codes, and the odds are that these two could be based on shorter numbers. You might wish to change the names for BTCH# and BTCHNO because I could see them becoming a point of confusion down the road. If they are two different types of batches, qualify their names, perhaps. I previously mentioned that you might have a table of FicheCards with a CardID. If so, your reel number would an FK to the Card table.

Now, as to how you divide them all up, here is the overview: You can use the query builder to automatically design a JOIN query between your customers and the transactions. Technically you don't need to have a declared relationship but if you DO, then the query builder wizard sees it and can take it into account. You would have a one-to-many relationship where Customer was the "one" side and Transaction would be the "many" side. When you do this, the query automatically gathers the data so that you see what APPEARS to be (but isn't) a spreadsheet with the person's info and each transaction for that person. Then you use the QUERY to do other things. You don't need to use tables directly because Access just wants a recordset and a SELECT query returns a recordset.

just still don't understand how to make the relationships between only one set of specific transaction dates (10-50 usually) point to only one customer # if the transactions from thousands of cust#'s are in their own table

This tells me you are still mired in flat-file thinking. The query groups things for you so that you see each transaction for each person in groups. Now if you use a SECOND query (or a form) to look for a specific person's records, that query might include something like "WHERE CustID = 1234567" to limit what you see to one person's records. You could also have the query sort by date. You could in fact have many queries, each one sorting by a different thing such as reason, type, date, etc.

I said that you could OPTIONALLY have a transaction ID number. If that transaction table is the "lowest" table in your hierarchy of data (i.e. it has no "child" table), it doesn't need a PK, though you might wish to declare indexes for other fields. You said that sometimes you can have two dates that are the same, so a compound key wouldn't be a good idea. PKs have to be unique.

 

[damageinc86]

Ah, you are looking at unrolling the fiche cards to put them back online. You are telling us that you actually want to reverse the storage to fiche. It is not clear but also perhaps not immediately material whether you want to point to the fiche anyway, but maybe you do. Let's look at your spreadsheet data.

Yes! because the only real pertinent data we need from the fiche is the person, their cust #, and all the microFILM reel details, so we can then go into the microfilm room and find that reel to print out the letter that was sent to them 10 years ago. I suppose I could include the actual number of the physical card (they have numbers in the upper left hand corner) as a backup, to show which card that customer is on in real life in case there is a problem. But ideally you just have to search in this database and then you can go straight to the microfilm reel, bypassing any need to go searching for a card and then finding the customer in the enlarger machine. It will all be in the database.

This tells me you are still mired in flat-file thinking. The query groups things for you so that you see each transaction for each person in groups. Now if you use a SECOND query (or a form) to look for a specific person's records, that query might include something like "WHERE CustID = 1234567" to limit what you see to one person's records. You could also have the query sort by date. You could in fact have many queries, each one sorting by a different thing such as reason, type, date, etc.

Right, that's what I'm doing now, the query is your WHERE statement, and then sorts by CTR DATE. That's all I need, in order to find the right reels from the Transaction info. And yes my confusion lies in the flat-filed thinking. The few tutorial videos on relationship haven't really helped me to see what exact steps you need to do in order to relate them. I see the dragging of the lines to create the one to many line, that's easy. Ok, so now that each and every transaction (thousands in one table) now have a primary key, AutoNumber?, now if transaction numbers 1,275-1,310 belong to custID 1234567, then how do I tell the transaction side of the relationship to make those particular transactions link up with only that custID number? That's where I'm having the breakdown. Because I'm fine with putting the Transactions all in their own table. It would be hard visually to see anything but thousands and thousands of un-related dates all in that table, but with the PK, it wouldn't have to make sense visually. But making that an actual relationship eludes me.

 

[The_Doc_Man]

But making that an actual relationship eludes me

Unfortunately, that concept requires you to SEE it. Descriptions might not help too much. Search this forum for the topic of "relationships" to see other posts and discussions. My way of explaining might not be what you need to hear.

It would be hard visually to see anything but thousands and thousands of un-related dates all in that table

And that is why you don't work with tables. You work with queries that can do the filtration for you and get rid of (well... actually just omit) the records that DON'T match what you asked for in the WHERE clause.

You are ABSOLUTELY right when you say there will be bunches of apparently unrelated data in the raw tables. But in Access, queries are your friend, your ally, your best companion. They can do so much to minimize the slop associated with a table holding lots of data. But you have to see it that way and as long as you stare at Excel spreadsheets, you will be mentally constrained to flat-land.

 

[damageinc86]

Right, I understand the power of queries, and I can see how what seems like unrelated info in a large table can easily get connected to meaningful results with the queries. So do you have any advice on my question about how to get a certain set of transactions in the transactions table to all be linked to one customer id through relationships? Because that would really be the only thing stopping me from getting the tables set up through relationships.

 

[The_Doc_Man]

The relationship that jumps out at me is that every transaction for customer ID 1234567 must have a foreign key to customer ID for which the value is 1234567. The customer ID is the parent table (a.k.a. independent table) and transactions, because they are intimately associated with customers, are the child time (a.k.a. dependent table). To build this in access you open the database and find the database tools ribbon. The Relationships option lets you declare the relationship by dragging the customer and transaction tables into that window ("add table" option). Then you expand the list of fields that are shown for each. Click/drag the customer ID field from the customer table to the customer ID field for the transaction table. (Which means you mush have already created the customer table layout, though both can be empty at the moment.)

You get a dialog box for which you would say "every record from Customer table and all matching records from Transaction table" (one customer/many transactions). Relational integrity, if you checked it, would require you to load the customer table first because RI requires that any record added on the "many" side of the relationship must match one of the entries in the "one" side.

That's the mechanics and the theory. Right now you are seeing the forest but can't yet see the trees. Queries with restrictive WHERE clauses allow you to pick the trees you want to see. Until you can see that aspect, you will continue to be baffled by the forest of data in the unfiltered transaction table. And it is a mental ability that some people don't naturally have. I can't help you make the mental leap. Perhaps if you search the web for some graphic tutorials, you might find something that can help you.

 

[damageinc86]

Right, that makes more sense, because it's basically what i'm already doing. I do have a table with all of the transactions in it that also have the customer# next to them. So I would just be linking that table to the parent customer# table if I understand correctly.

I think this is what it might look like?

Then I messed around with a little search form, and set my subforms to show each query I made for the transaction table and one record in the vehicle table just to see if I could successfully add a table and relationship and have it work. Onclick of search button set the macro to requery with control name left blank to load underlying table and viola! both tables showed up.