not sure how to solve this it is like a transaction

[Tailgun]

I have developed several apps in VB6 but still a newbie with this type of app.

A user puts some data in a grid and I save it in a table. The grid has a fixed number of columns but the rows vary depending on his input. That part works just fine.

Now I want to for want of a better term do a transaction. So the user opens another form showing the data he originally input. He makes changes in the grid in certain columns and I want to now save it as let's say trans1. I have the original grid that he pulls up bound to a control to show the data he originally input. But I want to save the changes somehow to be trans1 probably in a new table.

Then he makes another transaction making more changes to the grid and I want to save it as trans2 (BTW I have a textbox showing the next transaction which is now 2 shown on the form) and I need to save it as 2 for each row. So hopefully if the user pulls up trans2 it will show only that information pertaining to trans2.

My problem is I don't know how in the db to have all the rows associated with trans1 have the same ID number and of course the next trans2 with an ID number of 2 since there are several rows (and they vary) involved in each transaction.

Thanks for any and all help.

 

[johnwm]

You may need to do a quick read-up on the nature of relational databases. Google will provide some good basic reading, including:

http://www.15seconds.com/issue/020522.htm

You can also download MSSQL Books on Line from:

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

which gives a comprehensive look at relational databases and a version of Structured Query Language to access the database

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[BiggerBrother]

Is this not just a reworded version of thread709-772439

I see that you still have queries about the last post, so lets start there.

Create a table called transaction.

TransID Customer Date InvNo
T1001 Bob 01/01/03 INV0001
T1002 Bill 02/01/03 INV0002

Create another table called Items

ItemID ItemName ItemCost
I0001 Planks 10.50
I0002 Nails 0.05
I0003 Screws 0.10

Create another table called TransItems

ID TransID ItemID Quantity Cost

00001 T1001 I0001 4 20.00
00002 T1001 I0003 1000 89.00
etc

Now, you can link the items to a table full of all of your products, a transaction to all of your customers, and all of your sales items to each transaction.

If you want to populate a grid with all items for a transaction, you now simply use :
SELECT * FROM TRANSITEMS WHERE TRANSID = [red] myCustomerCode[/red]

It's a tried and tested method, and by far one of the most versatile. It allows for far more expantion in the future, should your client/boss like it, you can add alot more functions to it, rather than simply a table for all your transactions.

BB

 

[Tailgun]

Thanks johnwm and BiggerBrother I guess I just can't explain what I'm trying to do or at least what the client wants.

Will do some research that johnwm suggests and see if that sheds any light on my problem.

Or as it seems I may need to create some kinda item# for each row that the user originally creates. This is NOT actually a transaction as you would normally see in a business item cost etc type transaction. I don't mean to be a pain just trying to solve his needs.

I might be able to adapt BiggerBrother suggestion also.

Thank you both for your help.

 

[BiggerBrother]

The user never sees the item number. This is needed so that you can search your database at a later date, and also because Access or your db needs a unique identifier for each row in the db table. Why not simply post an example of your existing code, and db so that we might have a better understanding of what it is that you are doing.

BB

 

[ESquared]

Tell us more actual data: give a real example with real data values. I.E., Joe creates the record

ID:24, Name:"Frog", Day:"Tuesday", Flavor:"ExtraSpicy"

then he does X and then he does Y and needs to see Z.

I think you are asking for an answer instead of explaining the problem you want to solve. Sometimes, asking for an answer gets you what you ask, but you still don't have what you need to solve the problem (because if you'd known what to ask, you'd know how to solve it).

 

[Tailgun]

Ok hopefully I can explain this better. You have received a contract that has a number of items to do each with a fixed amount. You need to periodicly bill for partial payment of these items. So I have a tblItems that he inputs each item and the fixed amount and the table also contains the itemID. There is also a second table tblAdditions that is like the first except it contains items and amounts for additional items he contracted to do later during the contract.

Now he wants to submit lets say a monthly bill for partial payment of items he has worked on.

I have a form with a grid than shows the original table tblItems with some additional columns. ie previously completed, completed. So the user inputs in the completed column whatever he has completed in currency towards the original amount for each item. It also contains a From Date and To Date. Now I need to save this to a table and ref it somehow so I can recall it later.

Now he wants to bill the second month so I use the same form to call up the original billing (1)except now a textbox will say it is the second one (2)so it can be saved to the same table as (2) However now what he input into (1) as completed needs to be shown in the previously completed column in the grid. He then inputs what he is billing for each item for the second month in the completed column.

Then for the third month call up the previous data from(2) then the previously completed column needs to be the total of (1) and(2) completed.

So I guess my first question is how do you have the transID show up in each row for the items in that transaction?

And if anyone knows how I can do what I need to do with the previously completed, and completed columns problem.

Thanks for all your help I really do appreciate it. I hope this makes what I'm trying to do a little easier to understand.

 

[BiggerBrother]

Firstly, lets get a few things sorted.

Why are you using two separate columns for items and additional items. Why not just use ONE table as previously mentioned for all the items and then use an order date to add additional items, and record the date when the order is placed by the customer? This is how most large systems work, and there is good reason for it. If you look into SQL statements, you will be able to achieve far more if these are in one table. For example, if the customer places one order, adds to it, and then adds to it again, do you put both additions in the second table, or do you start the second addition as a new order????

Next - if the customer is ordering a number of the same item, like 5 hammers, and you are going to invoice for 3 and then later invoice the other 2, then you need to have a despatched column, where you can put the number you have sent. If the user is going to invoice for completed items only then you need to have a boolean column, 'COMPLETED'.

You also need to have a column for 'invoiced', then you can use SQL to return all items for a customer where it has not been invoiced, but it has been despatched/completed. This is easily done using SQL, and then the returned items can be added to the next invoice you create. You then simply iterate backl through the list, and make all the items from completed to invoiced.

If you need help with this then post your code, and we will try and help you.

BB

 

[Tailgun]

I guess my problem is I'm using the word transaction. It is more like I want to invoice periodicly for work done but save each as I stated above. The customer never orders more than one of any item he is paying based on what the user has billed him for a period in time and not always being the full amount for each item. I guess I could combine the original with the add on items using date as a ref. in the original table for the new contract items. However how do I put in a table the items with the same transID shown in every row of that transaction?

Sorry if I'm stupid in explaining my problem.
And again I really appreciate you trying to help me BiggerBrother.

 

[BiggerBrother]

Right, you have a table, called items:

Columns

ItemID - A unique number for this item ( ie hammer Item001)
Quantity - Number of items
UnitPrice - The price the user has quoted for this item, not a standard price.
Completed - Boolean column, yes/no
Invoiced - Boolean column, yes/no
Date - when the user places the order
CustomerID - unique ID for customer.
POSSIBLE ADDITIONAL COLUMN
User - if mour ethan one user, have a column to record the user so that you can check who took the order.

Now, on the invoice you can you can invoice for all items completed but not invoiced, using the following

SELECT * FROM ITEMS WHERE COMPLETED = 'TRUE' AND INVOICED = 'FALSE'

This will return a recordset, and you can then place all of tese items into a grid and print the invoice.

EASY!

Good luck

BB

 

[Tailgun]

Thanks again for your help BiggerBrother. I'm just not explaining it very well.

OK I have a table that contains items and prices and a hidden item#. I pull that up in a grid that now has the items and prices and a hidden item# plus new fields previously completed and completed both of those columns are $0.00. The user enters a currency amount in completed column which is NOT = to the price column from the table that contains items and prices as an invoice 1.
Then he enters invoice 2 I then pull that up in a grid that shows invoice one info but the invoice 1 completed column now shows in the previously completed column and the completed column is back to $0.00. So now he enters more completed $ and it is saved as invoice 2. Now he does an invoice 3 the previously completed column should be invoice 2 previously completed column plus invoice 2 $ completed column and completed column set back to $0.00. In each case the grid is first shown on the form with the completed column set to $0.00 which is the default.

When I save it as an invoice I still don't know how to have that table show lets say invoice001 for each item in that grid.

This is just some added info-When previously completed column = the original price I intend to set the completed cell for that row to disabled and change it's background color. I have figured out how to use the MSHFlexgrid so I'm working with TrueGrid 8.0 from Componentone.

Thanks again for any and all help.

 

[BiggerBrother]

I have given you alot of advice, and pointers as to how I would achieve, and have achieved the same thing. Your display of the invoices needs to be looked at, maybe using tabs or a dropdown list of all the invoices for that client, and then use the same table, but alter the heading.

Also, I feel that your database stuctural knowledge needs some work, as the way you want to design your database limits your application considerably and will also lead to you having to type alot more code, and will also mean that there is more room for error, and more scope for the appearance of bugs.

Good luck

BB

 

[johnwm]

May be time to review my first answer in this thread.....

No matter how much work goes into the code, you won't get really satisfactory answers without understanding how relational databases work.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[Tailgun]

Thanks BiggerBrother and johnwm. I wll do research on it.

Thanks again.

 

[ESquared]

I know from personal ongoing unpleasant experience that a poorly-designed database is a gigantic pain.

Sometimes I want to shoot the person who designed one of the databases at work. To give an analogy, imagine a bank database that did not keep each customer's current account balance in one place! In order to know the current balance, one has to process the withdrawal and deposit transaction history file all the way through every time. Pure garbage. And that's just for starters. :-(

 

[Tailgun]

Thanks ESquared

Your right my app is more like a bank account transaction but has many items more like having several accounts for the same person.

Everyone has tired to be very helpful and I really appreciate it.

Being 65 years old I'm not as sharp as all the others in this forum.

Are there any tutorials or books that relate to my problem?

Thanks to all again for your help.

 

[johnwm]

Tailgun
You're not the only 'mature' one round here!

Did you try the references in my first reply?

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[Tailgun]

I'm looking at them now johnwm

Glad to meet another ummm mature Programmer