469,127 Members | 1,425 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,127 developers. It's quick & easy.

How do i confirm sequential numbers over multiple tables

33
Hi,
Using MS Access, '97, we have 4 tables in scope, the content of which is provided by a customer. I need to validate the data in all four tables and one validation routine is focused on a field named ORNO which exists in each of the tables.

The following examples show the content of the ORNO field
Table1.ORNO value '0012769'
Table 1 will only ever have one single record and the ORNO value must be numeric.

Table2.ORNO value '0012770','0012771','0012772'
Table 2 values must increment the value from table 1 by 1

Table3.ORNO value '0012770', '0012770','0012771','0012772'
Table 3 values must exist with Table 2 and there can be multiple instances of Table 2 ORNO values within Table 3.

Table4.ORNO value '0012772'
Table 4 will only have one record and it should be the highest ORNO value that exists in Table 2 and Table 3.


Appreciate any help with this,
Tks.


fields holds an order numberof which within each table a numeric field named ORNO exists.
Aug 7 '12 #1

✓ answered by twinnyfo

Quizzed,

It just depends on how you are adding data to your database. If you access your Excel Spreadsheet as a linked table, you could create a recordset based on that spreadsheet, then access those records sequentially. The easiest way to go sequentially for ORNO would be to maintain that variable, and add one to it every time you append a new record.

Is the value for ORNO already establishe din the Excel spreadsheet or is it generated by the DB? The "necessity" of having sequential records is not really a necessity unless it is out of preference. As long as the tables are related by that common field, things whould work well with an autonumber field, which is commonly used as a Primary Key. Please explain if there is something I am missing.

7 1504
twinnyfo
3,653 Expert Mod 2GB
Quizzed,

I have some questions first. The way you display your "numeric" values, with leading zeroes, it appears that your values are of text data type, rather than a number (like an integer or long integer). If this is the case, this could get quite challenging when "adding a number" to a text field. If you need to maintain a certain number of characters for a serial number, then there are ways to display the number with leading zeroes, without actually changing the content of the field.

Second, from a technical aspect, it is highly irregular that you would have two tables with only one record each in them, especially since they appear to be only place holders. This can easily be done with global variables. For example, Table4 could be replaced by a global vaiable MaxORNO, which would be determined whenever necessary by finding the maximum value of ORNO in Table2:

Expand|Select|Wrap|Line Numbers
  1. MaxORNO = DMax("[ORNO]", "Table2")
  2.  
I can only presume that the value of ORNO in Table1 is a similarly calculated value. This will clean up your DB.

However, please don't take offense to this, but concerning this post, I found no real "question" being asked, so I am not sure what help you need from this forum. I will be glad to address any specific issues and try to get you on the right track with this.
Aug 7 '12 #2
Quizzed
33
Tks Twinnyfo,
The data is provided in xls format for loading into the db, assuming i can get the source data format for ORNO to be Integer/Long, what would solution would you suggest to ensure that the ORNO value increments by a value of 1 for each record in Table2?
Aug 7 '12 #3
twinnyfo
3,653 Expert Mod 2GB
Quizzed,

It just depends on how you are adding data to your database. If you access your Excel Spreadsheet as a linked table, you could create a recordset based on that spreadsheet, then access those records sequentially. The easiest way to go sequentially for ORNO would be to maintain that variable, and add one to it every time you append a new record.

Is the value for ORNO already establishe din the Excel spreadsheet or is it generated by the DB? The "necessity" of having sequential records is not really a necessity unless it is out of preference. As long as the tables are related by that common field, things whould work well with an autonumber field, which is commonly used as a Primary Key. Please explain if there is something I am missing.
Aug 7 '12 #4
Quizzed
33
Yes the ORNO value is already established in the xls and yes it must be sequential.
These are order header references in Table 2, the order lines are related by the corresponding ORNO presented in Table 3.
Cheers for your time.
Aug 7 '12 #5
Quizzed
33
Twinnyfo, Yes the ORNO value is already established in the xls and yes it must be sequential.
These are order header references in Table 2, the order lines are related by the corresponding ORNO presented in Table 3.
Cheers for your time.
Aug 7 '12 #6
twinnyfo
3,653 Expert Mod 2GB
Well, if the ORNO is already in the spreasheet, what happens if they are NOT sequential? Do you have to establish an ORNO that is sequential? I'm not sure I follow how the database will have to modify the data....

I'm still not sure what you are trying to do. It sounds like a straight import.....
Aug 7 '12 #7
zmbd
5,400 Expert Mod 4TB
@Quizzed

This sounds like a compsci homework project...

Can you provide us with the code you've already developed to solve the issue? (what have you tried)

-z
Aug 7 '12 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by E Arredondo | last post: by
2 posts views Thread by Tony Williams | last post: by
1 post views Thread by Ahmet Karaca | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.