UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Table Indexing, Any Version    
 
   
as2solutions
post May 12 2019, 07:56 AM
Post#1



Posts: 109
Joined: 8-June 04



Running into a little problem and need assistance from the experts.

Have a table with four fields (id, checknum, vendornum, checkdate).

I need to create indexing on the table that will allow checknum to be duplicated as long as the vendornum is the same. If the checknum and the vendornum are different, then the error would pop up that you are creating a duplicate entry.

For example:

id - checknum -vendornum
1 - 123 - 68 - ok
2 - 123 - 68 - ok
3 - 123 - 68 - ok
4 - 123 - 76 - duplicate

I've been wrestling with the table design indexes but can't seem to figure it out.

Attached is the table I am trying to index.

Thanks in advance.



Attached File(s)
Attached File  transactions.zip ( 16.8K )Number of downloads: 4
 
Go to the top of the page
 
Jeff B.
post May 12 2019, 08:02 AM
Post#2


UtterAccess VIP
Posts: 10,259
Joined: 30-April 10
From: Pacific NorthWet


?Are you saying that your records can have duplicate checknum values if the vendornum is the same? Assuming the fieldnames reflect check numbers and vendors, why would the same vendor have more than one check with the same number?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
GroverParkGeorge
post May 12 2019, 08:15 AM
Post#3


UA Admin
Posts: 35,314
Joined: 20-June 02
From: Newcastle, WA


Hm. In this example, if you delete the first three records, which have the 123 - 68 combination, could you then add the fourth record, as 123 - 76, and go to to duplicate that one?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
projecttoday
post May 12 2019, 10:10 AM
Post#4


UtterAccess VIP
Posts: 10,779
Joined: 10-February 04
From: South Charleston, WV


From your description, you can't implement that with indexes. You have to add code to search the table whenever a new record is created.

--------------------
Robert Crouser
Go to the top of the page
 
as2solutions
post May 12 2019, 12:30 PM
Post#5



Posts: 109
Joined: 8-June 04



Robert,

Would you help me develop the code that would search the table to see if it would create a duplicate?
Go to the top of the page
 
as2solutions
post May 12 2019, 12:38 PM
Post#6



Posts: 109
Joined: 8-June 04



Jeff, thanks for the response.

Yes, your assumption is correct in that I can have duplicate checknum values if the vendornum is the same.

In this situation, the check amount can be derived from more than one fund account. The table needs to be able to log the transaction showing multiple fund accounts if necessary.

Go to the top of the page
 
gemmathehusky
post May 12 2019, 01:20 PM
Post#7


UtterAccess VIP
Posts: 4,725
Joined: 5-June 07
From: UK


what does the checknum mean?

why do you (ie your system) not generate a different checknum when the vendornum changes?

In which case, why can you not have separate table for permitted vendors (maybe including the checksum in that table), and manage the data entry that way?

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
projecttoday
post May 12 2019, 01:26 PM
Post#8


UtterAccess VIP
Posts: 10,779
Joined: 10-February 04
From: South Charleston, WV


If exact duplicates of all 3 values are not allowed, then you would still want a composite index. But if there are additional requirements, it looks like you're going to need code.

QUOTE
id - checknum -vendornum
1 - 123 - 68 - ok
2 - 123 - 68 - ok
3 - 123 - 68 - ok
4 - 123 - 76 - duplicate


What if they enter
4 -124 -68
?

1 - 124 - 68
?

1 - 123 - 76
?

Will more than 1 user be making entries at the same time?

--------------------
Robert Crouser
Go to the top of the page
 
as2solutions
post May 12 2019, 01:41 PM
Post#9



Posts: 109
Joined: 8-June 04



What if they enter
4 -124 -68
This would be allowed.

1 - 124 - 68
This would be allowed.

1 - 123 - 76
This would be allowed.

Will more than 1 user be making entries at the same time?
No.

The ID is an autonumber field so it will never be repeated in the table. I need to allow a duplicate checknum as long as the vendornum is the same value. If the checknum is the same, but the vendornum is different, then I need to stop the user from proceeding.

checknum vendornum
123 5 ok
123 5 ok
123 5 ok
123 4 NOT ok

I will be pleased to compensate you for your time in this matter.
Go to the top of the page
 
projecttoday
post May 12 2019, 01:55 PM
Post#10


UtterAccess VIP
Posts: 10,779
Joined: 10-February 04
From: South Charleston, WV


I see. So I don't believe id is really involved. We can just remove id from the matter. Does that make sense? And as gemma hinted at, there appears to be a relationship between checknum and vendornum. If 123 = 68 then why store both of them? (I'm not saying you're wrong. Just please clarify. Does this counting process reset at some point?)

I think we're almost there, really.

I'll be gone for a couple of hours.

--------------------
Robert Crouser
Go to the top of the page
 
as2solutions
post May 12 2019, 02:39 PM
Post#11



Posts: 109
Joined: 8-June 04



Thanks for the continued assistance.

Yes, ID is not involved. However, I think I need to clarify. This is an accounting system specific to a niche group of users.

123 is simply a check Number. This is a user key-in.

68 (vendornum) is pulled from a lookup table that contains a unique set vendor names that the check can be made out to.

So, when the user wants to write a check (#123) to a certain vendor (Steve - #68) then they simply select the vendor name from a combo box. The vendor number (#68) which is associated with that vendor name is then stored in the table along with the check number.

Everything works quite well as long as there is only one record for each transaction in the table.

However, in some cases, and this is where I'm stuck, the check can be drawn from more than one funding source. A portion of the check may be $100 from the food fund. Another $200 may be drawn from the utility fund. Since this is a single check, to a single vendor, but contains more than one fund account, I need to store two records in the table - one for the food fund and one for the utilities fund - both records storing the same check# and Vendor#.

The problem is when a user starts to enter a new transaction but enters the same Check# (123) by accident and then enters a different Vendor Name (Vendor#). The check should have been assigned a new number by the user. I'm trying to find a good way to notify the user that they are duplicating the check# already assigned to a different transaction.

Go to the top of the page
 
nvogel
post May 12 2019, 03:43 PM
Post#12



Posts: 966
Joined: 26-January 14
From: London, UK


No one has mentioned normalization yet. From what you have said it seems that checknum determines vendornum, which is called a functional dependency: {checknum}→{vendornum}. In this case it's a non-key dependency which is the cause of your problem. It's usually best to avoid having non-key dependencies in your tables. The best thing to do is eliminate the problem by creating a new table where checknum is the key and putting vendornum in that table instead of your present table:

CREATE TABLE check (checknum INT NOT NULL PRIMARY KEY, vendornum INT NOT NULL);
Go to the top of the page
 
projecttoday
post May 12 2019, 08:05 PM
Post#13


UtterAccess VIP
Posts: 10,779
Joined: 10-February 04
From: South Charleston, WV


Okay, so how about this:

tblChecks

CheckID - Autonumber primary key
Checknum - Long integer
VendorID - FK to tblVendors

tblVendors -

VendorID - Autonumber primary key
Vendorname - Text
Vendornum - int (if needed)

In your transaction table, checknum and vendornum are replaced by CheckID. I would put a popup screen or a combo box on the form where the transactions are entered that displays a join between tblChecks and tblVendors which shows the available checks and each check's vendor next to it. You could filter it by vendor.

There is another screen bound to tblChecks which is for entering a check for the first time.




--------------------
Robert Crouser
Go to the top of the page
 
nvogel
post May 12 2019, 11:20 PM
Post#14



Posts: 966
Joined: 26-January 14
From: London, UK


Robert, Isn't the important point here that there's a dependency on Checknum and therefore Checknum ought to be a key (i.e. BCNF)? I don't see how your suggestion answers the question.

Go to the top of the page
 
projecttoday
post May 12 2019, 11:36 PM
Post#15


UtterAccess VIP
Posts: 10,779
Joined: 10-February 04
From: South Charleston, WV


Isn't the question about preventing the user from making an invalid entry which would be the case if the user selects instead of entering? I believe I would make checknum and vendorID a unique composite index. I'm no expert on normal-form theory, I'm afraid.

--------------------
Robert Crouser
Go to the top of the page
 
tina t
post May 12 2019, 11:40 PM
Post#16



Posts: 5,983
Joined: 11-November 10
From: SoCal, USA


QUOTE
However, in some cases, and this is where I'm stuck, the check can be drawn from more than one funding source. A portion of the check may be $100 from the food fund. Another $200 may be drawn from the utility fund. Since this is a single check, to a single vendor, but contains more than one fund account, I need to store two records in the table - one for the food fund and one for the utilities fund - both records storing the same check# and Vendor#.

well, let me start by saying that i have no accounting experience at all. so maybe i'm way off base. but you're a business (or whatever) and i'm a vendor, and you're giving money to me. your hand to mine, that's a single transaction. one record in a table. where you're getting the money to hand to me, that's only relevant to you, not to me - in other words not a part of the transaction itself, and so not part of the transaction record. seems like source funding for each transaction belongs in one or more child records - one record for each fund, with the specific amount from that fund as part of the child record. so a single transaction may be funded by one or more source funds, with the total from all the funds equaling the amount of the transaction.

so a vendors table to list vendors. a source funds table to list all sources of funding. a transactions table to list each check written to each vendor, date written, check number, amount. a transaction sources table, to list the fund(s) for each check written, and the amount from each fund toward the amount in the parent transaction record.

tblVendors 1:n tblTransactions
tblTransactions 1:n tblTransactionFunding
tblSourceFunds 1:n tblTransactionFunding

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
nvogel
post May 13 2019, 12:38 AM
Post#17



Posts: 966
Joined: 26-January 14
From: London, UK


The question is how to make sure that the same checknum can't be wrongly assigned to different vendors.

Robert suggested a unique composite index on checknum and vendorid but that would not prevent the same checknum being used for two different vendors. Tina suggested putting checknumber in the transaction table although it isn't clear to me what keys she is proposing.

The point of Normalization is that every functional dependency should be enforced by a key. In this case, if I'm reading the question correctly, the dependency is {checknum}→{vendornum}. This seems like a great example of how dependency analysis can help solve database design problems because the fact that checknum appears on the left-hand side of a (non-trivial) dependency immediately says that checknum ought to be a key.

CREATE TABLE [check] (checknum INT NOT NULL PRIMARY KEY, vendornum INT NOT NULL);

CREATE TABLE [transaction] (id INT NOT NULL PRIMARY KEY, checkum INT NOT NULL REFERENCES [check] (checknum));

(untested code)

Just my suggestion.
Go to the top of the page
 
projecttoday
post May 13 2019, 01:02 AM
Post#18


UtterAccess VIP
Posts: 10,779
Joined: 10-February 04
From: South Charleston, WV


I guess my thinking is partly in the vain of receiving checks instead instead of writing them. Sorry about that. But if we just have a simple unique index on checknum in tblChecks doesn't that answer the question?

--------------------
Robert Crouser
Go to the top of the page
 
nvogel
post May 13 2019, 01:41 AM
Post#19



Posts: 966
Joined: 26-January 14
From: London, UK


Of course checknum should have a unique index on it. From the point of view of enforcing this dependency it doesn't make any difference whether checknum is implemented as PRIMARY KEY or UNIQUE key. What matters is that checknum should be a key.

However, inventing a new attribute called CheckID is not going to help. It is irrelevant to the problem here and very possibly would do more harm than good.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th July 2019 - 06:41 PM