dynamic form needed

[Daveisalwayshere]

I have a job table that has a job number as the primary key and the rest of the feilds will be part numbers that i need to apply quantities to.

i have roughly 240 parts that can go into a job however i will only have roughly 10-to 20 at a time that needs to be entered into the table.

i need to find a way to create a form that allows me to copy and paste part numbers and quantities which i get from an Excel spreadsheet without having to have 240 blank boxes in my form.

the part number list would look like this

786979 , 4
543253 , 12
654364, 3
654377, 15
867466, 33
with part quantities in the second column my issue is that the parts wont be the same everytime and i would like to have a dynamic control so that it wouldn't matter what order the parts cam in when I pasted from excel it would fill out my table correctly

 

[pbaldy]

You're having trouble because the database design is incorrect. The parts for a job should be in a related table, with 1 record for each part. The concept is called normalization:

http://www.r937.com/Relational.html

 

[plog]

Agree with pbaldy. It seems your Excel file is properly structured (1 column for part number, 1 column for quantity). You should be able to use the Access import wizard to efficiently import your data from Excel to your Access table.

 

[Daveisalwayshere]

they are normalized what i am having issues with is inputting the article numbers into the table I have 50 to 60 guys creating drawings and from those drawings in autocad I get a "BCOUNT" which gets copied and pasted into excel from there i have to find a way of entering the counts i get from the spreadsheets into my tables easily considering the spreadsheet numbers will not be in the same order for every transaction

 

[jdraw]

Perhaps you could tell us more about the database.

You've mentioned jobs, parts , quantities, articles, drawings, creators(guys), counts and transactions.

A jpg of the tables and relationships could help. You'll have to zip it because of post count.

 

[Daveisalwayshere]

What it is is actually a parts log that allows me to track what parts are under what job. I have a table for my parts list that stores information for each part which will allow me to eventually add a cost to each job

 

[plog]

First post: job number, part number, quantity
Second post: article number, drawings, counts


I genuinely think you are trying to confuse us. When you don't explicitly tell us which terms are synonyms, we think they are distinct. Pick one term for a concept and stick with it.

You also mention concern over order when inputting data into a table. Data entry order shouldn't matter in a table. If you need to track data entry time, you can create a Date/Time field in your table and set its default value to when it is created. But even still, order shouldn't matter--that's what foreign and primary keys are for.

Can you post your database?

 

[Daveisalwayshere]

here is the uploaded database i am working on please not it is just a start

 

[Daveisalwayshere]

Sorry to confuse you guys. i am Very new to this i first opened access on Monday and i am looking to create this monster of a database. I dont know all the terms for everything in access but i will try to use the correct terms as best i can. i will put together screenshots of what i mean so it can be a little easier to understand

 

[jdraw]

I don't think your database is normalized, but I don't know your business or what the database is supposed to support.
There are a number of fields in the QuoteLogT that are suspect. For example: Mob, Erection, Mod, Dismantle,DeMob,Mob2, Erection2... Can a Job be associated with more than 1 Location? Can a Job have more than 1 Contact? CustName does not belong in OpenJobsT (can be determined via CustomerNum).
RentalRevenueT seems to a mixture of things...

It will save you much time and effort to get your tables and relationships designed to support your Business rules. Also, adopting a naming convention without embedded spaces (Cost category) and use of special characters (cell #) will save many syntax errors.

Just a few comments for consideration.

 

[Daveisalwayshere]

Thanks, when a salesman quotes on a job he either quotes it as a hard dollar bid or as a per piece bid and this is where the sales team would input the hard dollar amount that goes with the quote. i didnt think it would be necessary to create a second table for those values.
anyways is there an easy form i can make that will allow me to enter information into ScaffoldPartsT other than a fixed form with 240 spots to put information into? preferably i would like to copy and paste from a list, however the list wont have the article numbers in the same order all the time