How do i confirm sequential numbers over multiple tables

[Quizzed]

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.

[twinnyfo]

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:

MaxORNO = DMax("[ORNO]", "Table2")
 

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.

[Quizzed]

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?

[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.

[Quizzed]

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.

[Quizzed]

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.

[twinnyfo]

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.....

[zmbd]

@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