Lookup vendor amounts per customer

[btr94]

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.

[Stingaway]

First thing I would suggest is that you normalize your data at least to third normal form ( 1 - Eliminate repeating groups 2 - Eliminate redundant data and 3 - Eliminate Columns not dependent upon the key ) into the tables that you want to see in Access. From there you can either establish relationships through actual database relationships or through queried relationships - in which case you can utilize forms to display in the manner in which you would like to see them.

It's kind of hard to give you a "step 1, do this, step 2 do that" without actually seeing the data.

Hope this helps.

[btr94]

I could theoretically combine the two charts, but then I would have a chart with well over 400 columns. I could split it up - but here's my biggest issue. How can I create a relationship (or a query) that will show column names with a corresponding value in a list, eliminating blanks? Say Customer one might have a value for Vendors 1-5 and 7, but customer two only has values for 2-4 and 6. How would that work?

[Stingaway]

In your query, put criteria such as "Is not Null" or <>"" in each column - either as an "And" or as an "Or", depending on what you want to see. If you do it as an "and" you will still see a record (row) when columns 1 and 5 are filled but 2 and 4 are not. The only records (rows) that will not show up are records where the entire record is blank..

As for the relationship, put the two tables in the query itself and join the ID's together by dragging the key from one table over to the key in the other table. Double click on the line if you want the relationship to be an inner or outer join (there is a little wizard box that will come up and explain the 3 options you have.)

[btr94]

Thanks, so theoretically what you're saying is to transpose my data (so the CUSTID would be the column header and the vendor would be the first column)? I couldn't figure out how to set a relationship to tie the CUSTID column in the first table to a CUSTID row in the second...

Would it help if I sent you a sample data set on here so you could see my issue?

[Stingaway]

Yes to the sample data set...

[btr94]

Sent you a link via private message. Thanks.

[Stingaway]

Ok - yeah what you are looking to do will be best done if you normalize the data into some more tables. For example, in the Receipts table you sent - the column data is Vendor 1, Vendor 2, Vendor 3, etc. Vendor should be a separate table, with each vendor being a record - that way you would have a transaction table to match customer to vendor and be able to pull just the data you wanted...

That said, if you don't want to go about designing the database properly ;-) - but just want to get moving on what you have, I would recommend changing the customer id field in the customer table to you primary key - that way you can at least create some joins on the receipt table that will get you started...

Once you have done that, open a query, switch to sql view and put this in:

SELECT [Customer Data].CUSTID, Receipts.[Vendor 1], Receipts.[Vendor 2], Receipts.[Vendor 4], Receipts.[Vendor 5], Receipts.[Vendor 6]
FROM [Customer Data] INNER JOIN Receipts ON [Customer Data].CUSTID = Receipts.CUSTID;

From there you can switch back to design view and see what it is supposed to look like.

Again, I highly recommend the normalization over this shoestring and bubblegum approach... it will be much better for you in the long run and give you a lot more flexibility.

Hope this helps.

[btr94]

What you're saying makes sense.

Would I have three tables then?

1) Customer Data with two columns: CUSTID, Customer Name
2) Vendor Data with two columns: VENDID, Vendor Name
3) Transaction table with what? I feel as though to do this, I would have to have upwards of 10-20 records per customer - three columns being CUSTID, VENDID, and Amount. Is this accurate and if so, is there an easy way to go about doing this with my Excel data? I tried to do something similar in a PivotTable a while back but couldn't figure it out.

[Stingaway]

Yes, you are getting it - the transaction table would be CustID, Vendor ID, Amount, possibly transaction date, etc - anything related to the transaction. The CustID and VendorID would be the ID's that were found in the first two tables for the corresponding customer and vendor.

As for your question - that depends on your excel data. If you want to send me a sample of that I can take a look at it and see. The Pivot Table might actually be a good option for you if you are just trying to get a quick look at the data. Let me know what you are wanting to see with the excel data (give me an idea of the format) and I will see what I can put together.