I have a fairly complex (to me) situation that I am trying to iron out. I have, in Excel, a manually created table. The first column contains a Customer ID (CUSTID) unique to each customer, which corresponds to the same field in a large Access database. The second column is the Customer Name (which isn't as important). The third through upwards of 300 columns are titled by vendor, and in each row, a dollar amount exists. Some rows do not have corresponding dollar amounts for every vendor. I have two charts like this, each with different vendors across the top. No customer appears on both charts.
Inside the Access database, I have a table called "Customer Data" which basically has a CUSTID field, customer name, and much more about each customer. What I want to do is create a relationship where I can click on each customer, and expand a list of their vendor amounts below. I don't want the blank ones shown, only the ones with values. Currently, I have a relationship set up between the CUSTID in the Customer data chart with the CUSTID in another informational chart, and then that CUSTID in a relationship with another chart. So, I can basically expand two sub-sheets below each record in my original chart.
My end result should be this: I want to be able to expand each record in my initial chart, and have the first column be Vendor Name, and the second column be Vendor Amount. Blanks need not appear.
Since I have over 255 columns in my Excel chart, I know I need to split it into two Access tables. This shouldn't be an issue so long as I can still have two lookups.
Thoughts? Any help, even getting pointed in the right direction, would be greatly appreciated. If this can be accomplished by a query, that should work OK as well.
You are correct that you need another access table, but not because you have 255 columns! Is that logic assuming you will use a field in the access table for each vendor? If you want your data accessible, don't do that.
You already have your Customer Table with a unique ID, now you need to create one for the Vendors. The table will have VendorID, VendorName and other information that is specific to the Vendor as a whole. It doesn't not contain the dollar amounts or anything about the relationship between that vendor and the customer.
After you have the Customer and Vendor tables you create a join table that will hold the dollar amounts that are specific to that Customer/Vendor combination. So the fields are: CustomerID, VendorID, Amount.
How to deal with the data really depends on your long term plans. Is this spreadsheet going away? Is it the source of future data or a destination of data from the database?
Depending on how many customers you have it might be easiest to do each one individually. Copy the row of Vendors (Row A) and n a new Excel sheet do a paste special -> Transpose to move the vendors to Column B (so the list reads top to bottom rather than Left to Right). Now do the same for the customer specific dollar values and copy them into Column C. Copy the Customer ID into Column A. Save it and link it to the Access database where you can import the data to your tables. Of course, if you have more than a handful of customers this will become very cumbersome! As I said earlier, the best method depends on the long term plan for these spreadsheets and how many customers you have.