Table Structure

[riley73]

Hello, this is my first post so bear with me. I haven't used access in a long time and now I am tasked with building a new database. I'm hoping to get some guidance so I can set up my tables/relationships correctly before I get too deep into this. I've attached an xls file with my thoughts on how my tables should be for reference.



What I want to be able to do is this: Using macros/vba coding import an excel spreadsheet containing a list of competitor products a customer uses along with other info such as description, unit of measure, pack size etc. into an empty table (tblInput). I then want this to cross reference against our competitor product list and then export an excel file containing the input data/competive data/our recomended product with possible alternates. (tblApe to be updated monthly)

I've been able to get this to work on a very basic level but I know it could be much slicker than the way I do it. Currently the upload file has to be named the same and stored in the same location but I want to be able to choose what file to input each time. But that'll be another post.

If someone could please recommend how to best structure my tables I'd be very grateful. Please let me know if there are any questions.

Thanks in advance to all those who provide advice.
riley73

[jzwp11]

Welcome to the forum!

What process is your application trying to model? Can you explain your process?



I see some issues with the tables you posted that are concerning. First, you have several fields with the same name in two of the tables. Also, you have a series of sequentially numbered fields (alt1, alt2...) which would be considered a repeating group which would indicate that your tables are not normalized.

[riley73]

Hi jzwp11, thanks for the reply.

Hopefully I can explain this clearly.

Basically the way I want this to work is the user will import an excel file to the input table. The spreadsheet would be formatted as a template containing the same fields in the table. It would contain information provided to a sales rep by a customer regarding products they use. That is why I put a prefix of "Cust" on each field name since it is provided by the customer. The competitor table contains information that my company has compiled from past bids. So I added the prefix of "Comp" to some of these fields to differentiate it from the customer information. When the user imports the customer info it should cross reference against the competitor table and then again to the ape table which contains my company's product information. For simplicity this table is formatted exactly as it is exported out of our ordering system and will be updated monthly at a minimum.

The Alt fields are alternate products that could be options to quote to the customer. There may be 0 alts for a product or there could be multiple.

Key & Rank fields are meant to group similar products and rank them by relevance as alternates.

Once this is all done I want to have a file exported in excel format that contains the original information from the input table from the customer and also our equivalent product and related product info so that we can build a quote.

Clear as mud?
riley73

[jzwp11]

I understand what you want to do but an Excel spreadsheet is typically not normalized so it would not work well as a table in a relational database such as Access. The better approach would be as follows:

1. Create a table structure that follows the rules of normalization.
2. Import the Excel data into Access as a temporary table.
3. Use a series of append queries to move the Excel data into the normalized table structure.
4. Delete the temporary table.

[riley73]

Ok, that make sense I think. I'll have to try to wrap my head around doing it that way.

Thanks for the advice.

I may be back with questions.
riley73

[jzwp11]

I may be back with questions

We're here to help so please do not hesitate to ask.