Filtered TextBox Value

[vbaInet]

I'll need to see all tables, customers and suppliers included. Just delete the data and enter some sample data.

 

[connerlowen]

I can do that. I will upload the entire thing as soon as I get the sample data entered.

 

[connerlowen]

My database is too large to post on this site. what should I do?

 

[vbaInet]

* Delete all the reports
* Delete all the irrelevant queries, forms and code
* Only include very few test data, 10 or so
* Debug > Compile any left over code
* Compact & Repair the db
* Zip and upload

 

[connerlowen]

Here is all of my tables. I can post another with just queries, and just forms. All together it was too big.

View attachment Tables.accdb

 

[connerlowen]

The relationships that I had went away. do you want me to go in and recreate them, or post a screenshot of them, or just don't worry about it?

 

[vbaInet]

Don't worry about the relationships, I'll ask questions as we go along.

Now, a brief explanation what your database is about and what your objective is.

 

[connerlowen]

The database is about replacing the current method of making quotations which is 4 different excel sheets. The employees will enter all of the necessary data, the quotation will then run the necessary calculations. All of this will be used to create reports to either send by mail or email to customers. Also my boss wants to be able to run reports from a financial standpoint on margins and so on.

 

[vbaInet]

And you told your boss that you could do all this?

How long were you given from start to finish?

Explain what constitutes a quotation.

 

[connerlowen]

I started work on May 11th. they started me on a different database, but about 2 weeks in decided against it and started me on this one. My boss knows that I am new to access, and that there would be a learning curve. I told him that I thought I could do all of it, and that I would do my best.

The quotation will be generated in different ways depending on what management wants. The need that remains constant is the need for the calculations. This will be the main function of the database. Right now the Excel sheets allow for many opportunities for mistakes when entering data. The database will take as much of that away as possible.

 

[vbaInet]

For a beginner in Access - even if you might be technical - to replace an existing working system in just over a month is just unrealistic. Your boss should have given you at least 3 months.

You talk about "the quotation" and "necessary data" but don't go into detail. I need to know:
1. how a quotation is generated?
2. what it constitutes, i.e. what is in a typical quotation?
3. what sort of products are quoted on?
4. what are the main entities, i.e. Employees, Metals, etc - describe?

 

[connerlowen]

I think the easiest way for me to explain it would be to upload the existing excel files used. the files are .xlsx and the site will not let me load them. any suggestions?

 

[connerlowen]

I figured it out. I was in the wrong place in my computer. here is all four sheets used for calculations in quotations:

View attachment copy of Quotes.zip

 

[vbaInet]

I don't have much time to go through all your Excel files, so I still need a clear explanation of what each one does.

 

[connerlowen]

The first worksheet of each excel file is used to determine the price of the precious and base metals. These prices are used throughout the rest of the worksheets. My database will just create a table to store these values and call them when the time comes.

A single quote can contain one or all of the following elements: Individual External Part, Individual Internal Part, or Assembly.

An assembly can contain either an Individual Sub Part (which can be Internal or External), or a Sub Assembly which can again contain either an Individual Sub Part (which can be Internal or External).

The second worksheet is where most of the data is entered. The sequence for an Individual External Part goes as follows:
Part Number, Description, EAU (Estimated Annual Usage), MinBatch (Minimum Batch Required, used to determine whether the quote is per piece, or per 1,000. if value > 5,000 then quote is per 1,000.), FabPrice (*See note below*), [This is where the metals will be entered. In the excel sheet there are columns specific to the metal, the database will allow any metal to be entered at this stage.]. The only other things on this worksheet are calculated fields.
*The table at the top of the page is used if the part is Internal. This table is used to calculate the "FabPrice" field.*

The third worksheet is once again about metal prices.

The fourth worksheet is where all of the rest of the calculations are made.
the fields that contain data to be entered are as follows:
Number Of Handling Operations (used to calculate cost for Handling), Import Duty (used to calculate the shipping and import duty, if yes.), the other field is Margin Fab % (My boss wants the database to determine this field automatically).

The assemblies are similar in many ways.

I hope this helps. I focus on the Individual Part because it is the easiest, and the assemblies work off of the same process, but used the data is a slightly different way.

 

[vbaInet]

For clarity, can you tell me the names of "first, second...etc" spreadsheets as you've explained.

 

[connerlowen]

1. Exchange Rate & Metal Markets
2. Supplier Data
3. NAECO Metal Prices
4. NAECO Calculations

 

[vbaInet]

Ok, I'll look through later today today and advice.

 

[connerlowen]

Thank you so much. I am understanding and learning more and more every day between research and just experimenting. I look forward to your response. Thanks again.

 

[connerlowen]

Are you thinking that starting over completely is the only way to go about this? Or do you think I could salvage some of my current DB and make edits?