Check and Copy

[nasonlt]

I have a table with three fields of charges among other fileds. I need to check and see if there is a charge in more than one of the fields per record. If there is I need to copy the record, paste it into the table, delete one of the charges and delete one of the charges from the copied record. I can only have one charge per record. FYI I am less than a VB novice.

Thanks for any help.
Nason

 

[jeremie_ingram]

There are several ways of going about this. I personally would use (seperate) queries with the criteria of IS NOT NULL for the second and third charge field. Doing this would seperate only those records that have something within these fields. Once you develope a way to filter them out, deleting the records you want and saving what you want is fairly simple.
If you could post a small sample, I could take a look, and develope a few queries for you. Then you could easily trigger any series of them that you want via a cmd button or event.

 

[trucktime]

Check and copy

It seems to me that you are making things too complicated.
If you can have only one charge per record than why have three fields?
May be it would be better to have just one field and make it a lookup field determining which type of charge it is.
That will guarantee you will have only one charge per record, and it will probably save you a lot of work along the way.

 

[WayneRyan]

Nason,

I agree with TruckTime. If you have a repeating field in a table, you will
end up with a lot of queries that essentially say:

Where Field1 > 0 Or Field2 > 0 Or Field3 > 0 ...

This gets clumsy after a while and makes it difficult to add a new charge
type.

tblCharges
==========
ChargeID - AutoNumber
ChargeWho FK - (Who made the charge? Points to a people or business table)
ChargeType - FK (to tblChargeTypes)
ChargeAmount - (Amount of charge)
ChargeDate - (Date of charge)

tblChargeTypes
==============
ChargeTypeID - AutoNumber
ChargeType - (Description)

tblWho
======
WhoID - AutoNumber
WhoName - Name of business/person or whatever

That will give you a lot more flexibility.

Wayne

 

[nasonlt]

This is part of the type of record that I am dealing with. All three charge fields(item charge, processing fee, billing fee) are in the same record. The specific problem is that when someone is charge a item charge they get a billing fee in the same record. I need to put each of those charges in a different record.

The first proble for me is to identify a record with two charges. Then copy the record into the table and remove one of the charges and remove the other charge from the original record.

This is kind of what I am looking at in my Database.
Charge Type Item Charg Processing Fee Billing Fee
OVERDUEX $70 $0 $5

Thanks for all the help!
Nason

 

[Jon K]

Probably what you need is a Union Query (Type/Paste in the SQL View of a new query, replacing with the correct TableName):-

SELECT 1 AS OrdNum, [Charge Type], "Item Charge" AS Type, [Item Charge] AS Amount
FROM [TableName] Where [Item Charge]<>0
UNION
SELECT 2, [Charge Type], "Processing Fee" , [Processing Fee]
FROM [TableName] Where [Processing Fee]<>0
UNION
SELECT 3, [Charge Type], "Billing Fee" , [Billing Fee]
FROM [TableName] Where [Billing Fee]<>0
ORDER BY [Charge Type], OrdNum;


If you want to show the $0 charges in the query results, you can remove all the Where Clauses from the Union Query.

To create a new table, just build a Make-Table query based on the Union Query.
.