Question database, table and form query from a self taught newb

[johncrofts]

hi guys,

im very new to access but i think its been easy enough so far to teach my self on the fly like i did with excel, that aside i have created a table called quote register which is where we store all quotes i.e: quote #, dates, client details, etc etc, this table is linked to my contacts table and is working very well, what I'm having trouble with is when i need to turn a quote into a job it needs to then be put in a separate table i will call job register. what i don't want to do is input the data again. i have designed a form which allows me to input data that way for "quote register" but it also has the fields for my job register in it(only some).
I'm stuck on how to make the 2nd lot of information (after job has been quoted i.:e price, who quoted etc) go into both tables. i would like to incorporate a command button on my form which will say "create job file" which would send the necessary info to the "job register" and also send the necessary info to the "quote register".

if im completely off base here in my needs could someone please let me know, the data entry here needs to be more efficient and this is the only thing i could think of after trying for months to code up excel, i think access is much more powerful and easier to use.

any help would be much appreciated.

kind regards,
John Crofts

 

[PNGBill]

Firstly, consider having just one table for all quotes and jobs. Have field to identify if the record is a quote or job.
Field can be text with "Quote" or "Job" or better method is to have a number field with 1, 2 or 3 etc which is linked to a small table tblRecordStatus which has two fields, RecordID and RecordDescription.Records would be 1, Quote & 2, Job.
With this system you avoid an additional table and only need to Update the field in your table from "Quote" to "Job" or 1 to 2.
You would do this with a ComboBox on a form or vbs code behind a Command Button on a form.

To use your current system, two tables, you need an Append Query to add the record to tblJobs and a Delete Query to remove the record from tblQuotes or an Update Query to make chnages to tblQuotes, if you do not delete the record.

To create the Update, Append or Delete query, first create a select query that wil return the data you are looking for and then change it to teh appropriate Action query.

Post some details if you need help re this.

 

[johncrofts]

thanks for the reply,
i thought about using 1 table but the thing is i work with people who are scared of computers and the change it can cause although for the better they just dont understand, so i was trying to keep things similar to what we have now in excel just better.

i also dont want to delete the quote side of things an update query sounds good but atm we have two excel files which is why i tried to do it that way the quote numbers and the job numbers are completely different as we do not win all of our quotes therefore if it were to be on the same table im afraid some data will be lost or screwed up.

i dont mind working a little harder to get the 2 tables to work, but with the append i dont understand it at all, right now i have been reading on how the change the start value of the auto number (as this is the main field thats gets stuffed up in excel) and i have read countless threads i just cant figure it out.

so is there any videos or something like that to visually help me as i do learn a lot better by doing it my self and watching someone do it.

regards'
john

 

[PNGBill]

Do you want to use the excel reference numbers as your record ID's in your Database ? (for existing records)

Your Primary Key doesn't need to be an autonumber.

You can use an autonumber primary key and then have another field, JobRef which is another number and operators only see JobRef, not the primary key.

This is used in some GL sofware where you are able to change the check/invice number. The record has a real primary key you do not see and the document number is another field. You can set this field to not allow duplicates.

With regard to one table. Your situation is quite common.
Add a field for JobRef and use code to get the next JobRef (number) from the last one used.
When you query Jobs, you will not see the Quotes.

This can also allow for One Quote and Multiply Jobs.

Think of the Job/quote table as a filing cabinet and when you open it for Jobs (query), that is all you see, with consecutive numbers and when you open it for Quotes, that is all you see.
But, you only have One cabinet.

A big issue with AutoNumber is any errors and you can have missing numbers from your sequence. Use autonumber as a hidden primary key and you will never notice any problem when this happens.

 

[johncrofts]

both u guys are awesome i'm just getting back into it now i am going to start all over again from scratch with the new quote regi but i need to continue the numberd seq as i do with the job regi but both your ideas are fantastic i just have to make them work which 1 i dunno yet whatever works first i suppose. thanks for your help guys i will no doubt be posting up again with the same sort of dramas

 

[gemma-the-husky]

i side with the two table idea

there should be no common fields between a job and a quote anyway - the point is, even if you only raise one quote, you should end up with a job line, and a quote line.

the only link is that the jobid is a value (and a foreign key) in the quote table


the "job" will include details about the job

the "quote" includes details about a particular customer who you have quoted for the job.

not the same thing, and it is important that you appreciate that they are not the same thing.

------
except that you get difficulties in this sort of set up (eg in construction) where a particular job can have many successful quotes - as different subcontractors may be responsible for different parts of the work. the data really needs careful analysis to determine the necessary structure



----
just one other point, with regard to your point 3

the data layout is nothing to do with users. they should not see your tables, and it should not matter to them how you achieve the solution. the database functionality should act like a "black-box" to insulate users from the implementation.

 

[PNGBill]

With a Quote and an Order situation, they can be virtually identical.
ie, you quote a customer for 4 items.
The customer gives you the go ahead for all 4, I see little or no difference except the fact that one is a quote and the other is an Order.

Of course, a quote can be partially accepted and even multi accepted ie 2 items ordered to day and 1 item ordered next week.

Many variations and of course company rules may well limit choice.

If your "boss" has only one way for the task to Appear to be done, then that is how you need to present your system.

Be aware that what the Boss and Users see doesn't mean other things are not happening behind the scene.

eg We have tblCommunications. One field is RecordType. Here we have Loan, Application, Employer.

When you deal with Employers, you only see communications regarding same yet they are all in one table.
I should have set it up so we have 1,2,3 or 4 as the field rather the a string 11 chrs long or more - less load on the database.

 

[johncrofts]

regardless of the client the way we have it setup in excel is we input data about a quote only the clients dont get there own file or database right now the person inputing the data copyies and paste client details from an earlier entry and which is slow, this is why i have created a table with clients only and linked it to a drop box. the other tpoint about it is that ther quotes we do are completely ours no one else is involced we are a frame and truss factory and only quote on our work so there is no third party quotes being added as a factor.if i may would it be ok in this thread to post a few pics of the things i wanted to do and what i have created or this against the forum rules.

@gemma the husky i think i have this sneaky suspicion i dont know exactly what you meant by your post this is the reasons for the uploading of pics sorry for being a pain guys, also i do understand your last point about the users but as it sits i only started using access the day i created this thread, my knowledge of computers and small knowledge of excel is the only reason my brain is still intact

 

[gemma-the-husky]

ah

i may be talking about somethnig different in distinguishing between "jobs" and quotes"

If you mean that you raise a lot of "quotes", and only some of them turn into "jobs", then that is one table

I was really thinking of a different situation that may not be aplicable here, where a "job" and a "quote" are 100% different entities.

 

[johncrofts]

thats exactly what it is to be, any ideas on layout

 

[johncrofts]

hi guys i have been working on my registry again and its kickin my ass, i decided to make it one table and now all i need to do is continue with the original numbers of my quoting system. lets say i am upto quote number 9256, i want my new access table to continue with this number sequentually but i cant seem to append the auto numbering system as some online searching has instructed me to i dont know if im doinng it right could someone please give simple, step by step instructions on how to do this.


thanks in advance

 

[PNGBill]

Consider adding a new field as your Primary Key - Auto number.
Keep the field you use for your Document Reference as a Number Field but not Auto Number.

Auto Number can be a real head ache when you need sequential numbers as any mistake may well mean a lost number. If not auto number, you can reuse the number.

There is quite a bit of discussion on how to have the number be the next in a certain order/pattern.

You have far greater control over your reference number when it is not an auto number.

 

[johncrofts]

ok so i finished most of it off sorted out my auto numbering but now i realise i can only have 1 autonumber which is good in a way but i do need another automated column for my job numbers as they are considerably lower lets its something like q9200 and jo3251 i dont want to have to remember the job number to input a new can i use a calculate column or expression builder to find the highest number in a column and just add 1.

again guys you have been really helpful on this i really do appreciated

 

[johncrofts]

So scrap that last 1 i figured that with a dmax but now i only want to show up in the table if i click on the yes button because i dont want a job number for a quote we dont win how wopuld i code this up

 

[PNGBill]

I would create a Function to get the next number.

Use code to find the last number and then add 1 to same and you have your next number.

Call the function in the code for the "Yes" Command Button

 

[johncrofts]

I would create a Function to get the next number.

Use code to find the last number and then add 1 to same and you have your next number.

Call the function in the code for the "Yes" Command Button

i dont quite understand what you mean, i will have a form for input which loads up when register opens and another for find records, so when we get an acception of a job i will find that record and click the yes/no checkbox on the form and viola i want to see an automated number i dont know how to code this up at all.

i know im trying to use basic functions for somewhat of a grand idea (or atleast i think so lol)

 

[PNGBill]

If you could post a copy of the database, without any sensitive data on, it may be easier to do this for you.

I see two seperate tasks to do.

1. Create the Public Function to get the next reference to use

2. have code on your form (event of the Yes/No check box is a lilely place) to create the new record and use the function supplied number for the reference field.

 

[johncrofts]

can i email u a copy instead there is no data in it yet but as im starting from scratch but need to continue the numbers i have forms and everything setup i have codes written for it at work that will do my numbers its just the acceptd field a dont want to it create a job number until i do i check the box plz let me know your email

 

[PNGBill]

Best to post on the forum. Others may view and critique the database and or my code.

The work is doen on a copy. You would then replicate/copy the code onto your original database so no need for the number sequence to be an issue as your numbers will start from where ever they should.

 

[johncrofts]

ok all i have is a copy so far with no coding just physical table without data should i input the codes i have first and then post it up and what exactly should i post just the codes or some pics of the stuff created