Related:
- Auto populate when changing from quoted to sold?
- Grand theft auto 1 download - Download - Action and adventure
- Grand theft auto v free download no verification for pc - Download - Action and adventure
- How to stop facebook auto refresh - Guide
- Nvidia drivers auto detect - Guide
- Auto redial - Guide
4 responses
Use a database, and report on the different cases! Excel is for calculations, not for data storage and manipulation. You can still do calculations on a Database!
Understand, you would need a button or something. A user has to initiate any sort of CHANGES to a sheet, a program cannot. You would need to place a button on the sheet, and press it after all rows have been changed. Almost like an "end of Data entry Process". Make sense?
OK, a database a set of tables linked by Index's (typically unique). I will try to explain as short and sweet as I can.
So you are in a business that sells cars. Your database may be comprised of four tables:
SalesPeople (table #1) with Columns of: EmployeeID, Name, StartDate, Bonus
VehicleType (table #2) with columns of: VehicleType, Description, FuelType
Vehicles (table #3) with columns of: VehicleID, VehicleType, SalesPrice, VehicleStatus, VIN, COLOR, soldby
VehicleStatus (table#4) with columns of: StatusID, Description
Now the data on each table could be like:
Table-SalesPeople
EmployeeID--------------------Name--------------------Startdate--------------------Bonus
______________________________________________________________________
E100----------Billy----------7/1/2013----------5%
E200----------Joe----------2/14/2015----------4%
Table-VehicleType
VehicleType--------------------Description-----------------FuelType
______________________________________________________________________
10----------1/2 ton Truck----------Petrol
11----------1/2 ton Truck----------Deisel
12----------Sedan----------Petrol
13----------Coupe----------Petrol
14----------Coupe----------Deisel
Table-Vehicles
VehicleID----------VehicleType---------SalesPrice------------ VehicleStatus------------VIN-----------COLOR----soldby
9999---------- 14----------14000----------10 ----------1234----------Silver
9900 ----------10 ---------- 30000----------99 ---------- 4321----------White----------E100
8899----------12 ---------- 8100----------100 ----------9876----------Red ----------E200
Table-VehicleStatus
StatusID-----------------------Description
1 ----------Recvd/not detailed
2 ---------- Detailed
10 ---------- ready for sale
90 ----------Pending sale
99 ---------- Sold
100 ---------- Sold/delivered
So now, you would need to build entry forms, and link the TEXTBOXs to the appropriate tables and columns. This allows you to open up a data entry form, and enter in and manipulate the date in the tables. NOTE--You do not need entry forms, and you can manipulate the data directly in the tables. This is not ideal, but it can be done this way!
Now that you have a way to move and add data, you can now write a report that says:
Give me all of the cars from the vehicle table that are red (vehicle color=RED), or...
Give me all of the cars from the vehicle table that are sold but not delivered(vehicle status=99), or...
Show me all of the cars in Vehicles that are't sold (soldby=NULL/BLANK), or...
Show me all of cars that Billy sold (Vehicles.soldby=E100 and Vehicle.Status = 99 or 100), or...
Show me all of the diesel coupes over 10000 (Vehicles.VehicleType=14 and SalesPrice>10000) would return vehicle 9999.
The data will never move from the table, it will just CHANGE status for reporting purposes.
I hope this helps, and I can assist with making the forms. Now you need to pick a Database program and start to learn it. Beleive me, it is easier than trying to learn VBA! I suggest you start with a Free one like https://www.openoffice.org/product/base.html. Get the version 4.0.1. The latest and greatest didn't seem to run right on windows 10 (at least not on my machine!).
Let us know if you need a more detailed description, or if any of this doen't make sense, because we need to understand how these table are RELATED before we can move forward with a dynamic solution without having to redesign after you start using it!
Please note- most of these tables are value storage and will never need to be changed, or altered. In the above example, once you enter in the Employee, the Vehicle Type, and the Status, the only table you will manipulate will be the Vehicles table!
So you are in a business that sells cars. Your database may be comprised of four tables:
SalesPeople (table #1) with Columns of: EmployeeID, Name, StartDate, Bonus
VehicleType (table #2) with columns of: VehicleType, Description, FuelType
Vehicles (table #3) with columns of: VehicleID, VehicleType, SalesPrice, VehicleStatus, VIN, COLOR, soldby
VehicleStatus (table#4) with columns of: StatusID, Description
Now the data on each table could be like:
Table-SalesPeople
EmployeeID--------------------Name--------------------Startdate--------------------Bonus
______________________________________________________________________
E100----------Billy----------7/1/2013----------5%
E200----------Joe----------2/14/2015----------4%
Table-VehicleType
VehicleType--------------------Description-----------------FuelType
______________________________________________________________________
10----------1/2 ton Truck----------Petrol
11----------1/2 ton Truck----------Deisel
12----------Sedan----------Petrol
13----------Coupe----------Petrol
14----------Coupe----------Deisel
Table-Vehicles
VehicleID----------VehicleType---------SalesPrice------------ VehicleStatus------------VIN-----------COLOR----soldby
9999---------- 14----------14000----------10 ----------1234----------Silver
9900 ----------10 ---------- 30000----------99 ---------- 4321----------White----------E100
8899----------12 ---------- 8100----------100 ----------9876----------Red ----------E200
Table-VehicleStatus
StatusID-----------------------Description
1 ----------Recvd/not detailed
2 ---------- Detailed
10 ---------- ready for sale
90 ----------Pending sale
99 ---------- Sold
100 ---------- Sold/delivered
So now, you would need to build entry forms, and link the TEXTBOXs to the appropriate tables and columns. This allows you to open up a data entry form, and enter in and manipulate the date in the tables. NOTE--You do not need entry forms, and you can manipulate the data directly in the tables. This is not ideal, but it can be done this way!
Now that you have a way to move and add data, you can now write a report that says:
Give me all of the cars from the vehicle table that are red (vehicle color=RED), or...
Give me all of the cars from the vehicle table that are sold but not delivered(vehicle status=99), or...
Show me all of the cars in Vehicles that are't sold (soldby=NULL/BLANK), or...
Show me all of cars that Billy sold (Vehicles.soldby=E100 and Vehicle.Status = 99 or 100), or...
Show me all of the diesel coupes over 10000 (Vehicles.VehicleType=14 and SalesPrice>10000) would return vehicle 9999.
The data will never move from the table, it will just CHANGE status for reporting purposes.
I hope this helps, and I can assist with making the forms. Now you need to pick a Database program and start to learn it. Beleive me, it is easier than trying to learn VBA! I suggest you start with a Free one like https://www.openoffice.org/product/base.html. Get the version 4.0.1. The latest and greatest didn't seem to run right on windows 10 (at least not on my machine!).
Let us know if you need a more detailed description, or if any of this doen't make sense, because we need to understand how these table are RELATED before we can move forward with a dynamic solution without having to redesign after you start using it!
Please note- most of these tables are value storage and will never need to be changed, or altered. In the above example, once you enter in the Employee, the Vehicle Type, and the Status, the only table you will manipulate will be the Vehicles table!
Jun 8, 2018 at 11:15 AM
I love the idea!!! Just not sure how to create one.
Thanks!
Nick