Number Jumble (1 Viewer)

[TheSafetyGuy86]

Good day access forums!

I have a minor issue here. I have a set of tables. One has a set of 6 numbers, each number being in its own field, and a field with the date that those numbers were generated.

I have another table that has over 500 rows of dates and numbers in it, with dates that are different. I am trying to make a query for a report that will show all the of the numbers with a matching dates and numbers from table 2 to the specific date and number from table 1.

Example:

Table 1

Date Field: 10/31/2014
Number Field 1: 1
Number Field 2: 2
Number Field 3: 3
Number Field 4: 4
Number Field 5: 5
Number Field 6: 6


Find from table 2 all numbers that match fields above out of over 500 records. As long as the date matches, I want to know if there is only 1 number out of the 6 or all 6 of the numbers out of the 6 fields match.

Please help! Thanks!

TheSafetyGuy

 

[llkhoutx]

Matchng date fields can be problematic based on how the date filed is populated. Dates populated with Now() seldom if every match because of the decimal value of the date.

I'd use ADO or DAO to cycle through the records to see what fields match. This is very fast, even for 50K records.

 

[plog]

You're data isn't properly structured. Whenever you start numerating field names ([Number Field 1], [Number Field 2], etc.) it means you're data isn't set up right. You should always aim to get your data more vertical rather than more horizontal.


Instead of your sample data let's use this set:

YourBadTableNameHere
[DateField], [NumberField1], [NumberField2], [NumberField3], [NumberField4], [NumberField5], [NumberField6]
10/31/2014, 17, 38, 91, 12, 9, 41

The above is your structure with some sample data. Below is that same sample data, but in the proper structure:

GoodTableNameHere
[DateField], [NumberPosition], [NumberField]
10/31/2014, 1, 17
10/31/2014, 2, 38
10/31/2014, 3, 91
10/31/2014, 4, 12
10/31/2014, 5, 9
10/31/2014, 6, 41

Once your data is in that structure, you can use an aggregate query to determine what you want. You didn't give the structure of table 2, so I can't tell you if that's good or bad or how to produce your results. If you want to show a sample of Table 2 that would be helpful.

Also, it would benefit you the most if you didn't genericize your table/field names and provided the actual ones. If I write code, you won't be able to paste it directly in, you'll have to convert generic names to your specific ones. If I know the correct names, my code would work with a straight paste into your database.