Access 2016 Build a orders System Related to a project or a Firstrequest (1 Viewer)

[snirben]

Hi Guys,
Need your help.
I build in access Program for my parents small company.
And they want it to be in the way they works.

So first let me explain a little bit,
i have 3 tables

1. FirstRequest(Before it became a project it need to be a FirstRequest)
2. Project
3. Items

i need to build System with Orders/Invoice/Shipment certificate/Request a Bid , for items from suppliers.
So let me know if I build it right.
I created 4 Tables:
Invoice
Shipment certificate
Orders
Request a Bid


I need to create Request a Bid Form with the items and the info i want.
If it's approved,
I need to Create in Order Form (Based on the bid with the items and the info i want),
after i get the order i get in Shipment certificate(Based on Order with the items)
After all of that i need to create in invoice from the Shipment certificate.(Based on the Shipment Certifcate Number)


and i need them all to be conected to a project or first request.
and for each type i need diffrent autonumber, lets say Bid is "B1500" Order is "O3500"(this is easy part)


so like i said i Created the 4 tables
now i created a form for the request bid. but i am unsuccefull connecting it to the Project or first request.
i have a form with Project and all the info about the project, and i have tabcontrol there. i want to make in option to click New RequestBID and a popup will open and then the form will know its related only to the Project number Opend. and the new RequestBid Get the number B1501 and so on.
now in the new popup i can enter items and Print it or pdf it (this i know how to do)
also how can i let him know that items that i enter need to connect only for the RequestBID Number.


hope i explain my self right. Please try to help me

Some Quesions i was asked from ohter fourm:

First, it would help to understand what your parent's business does.
It sounds like you may have a lot of redundancy here. Does Project have common fields with First Request (other than a key field)?
I would have a single Projects table with a Status to indicate that it is a first request or converted to a Project.
As for Orders, youu should have an Orders and Order Details table also a Customers table. Those tables can feed a query to create invoices.
We need to know more about how the Bid process works to advise further.

My Answer:
The business is about Steel Buildings and factory.
yes it has a common fields but thay are not Related,
let me explain about the tables:

FirstRequest table have:

FirstRequestNumber (AutoNumber)
ExternalRequestNumber(We get it from the customer)
CustumerNumber(I have a table with Customers)
Reminder( Date Field)
Files(File Field)
Project Table have:
ID(Autonumber) do i need that?
ProjectNumber( i want it to take it from the FirstRequestNumber or my own Number that i choose)
CustomerNumber(i want it to take it from the FirstRequest Table)
Files(File Filed)
Date Start( Date Field)
Date Estimated(Date Field)
so its better to have 1 table and not this 2?
About the bid process
so this is how the Order go:
First i send a BID Doucment(Word or PDF) with my items i need to the Customer from the form in access, (Access Form)
i get back the file with the Prices of the Customer near my items.
then i make in order based on the BID,
after that i get the items from the customer with the shipment certificate
So i mark the order as Arrived and it became shipment certificate (But i still need to see the order in history)
after that i create invoice based on the Shipment certificate ( i dont need the items in the form only she Shipment Certificate number)
hope that help.


Question 2:

Ok, that gives me a better idea, but there are still more questions.
1) Can a project be created without a First Request record?
2) Can there only be ONE Project for a First Request?
3) What is the Files field?
4) So you are requesting a Bid from a supplier for materials to make buildings? The bid would request prices for materials from a supplier? There could be multiple Bids for an order or for a project? An order is created to a specific supplier after the bid is returned completed?

My Answer:

1) Yes Project can be Created without a first Request Record
2) Yes only one
3) Files filed is to add PDF files or word files related to the project to store them.
4)Yes i request a bid form multiplay suplliers for materials, yes the bid is a pdf file with the list of items i created with the logo of my company,Date,etc
There could be multiple Bids for an order or for a project YES
An order is created to a specific supplier after the bid is returned completed? yes

 

[Ranman256]

Wouldn't you just have 1 table, tProject, with a status field.
It would change from: First Request, then change to a Project., etc.
This way it is 1 table, and the keys stay the same.

???

 

[snirben]

Wouldn't you just have 1 table, tProject, with a status field.
It would change from: First Request, then change to a Project., etc.
This way it is 1 table, and the keys stay the same.

???

you right,

Thanks for the idea.

about the system of orders and idea?