Create a NEW calculated field that counts the query records

[pbDudley]

Hello
I am trying to create a calculated field using the expression builder if this is possible, that would create new field and add a number for each record.

So if my query has 4 records, this new field would have would have a number to represent each, 1, 2, 3, 4

So not counting the totals but actually adding the number to the field for each


I dont know if this is possible using the query tool.

Thank you

[Gicu]

What you want is called a ranking query in Access, many returns in a Google search, here are a couple:
http://allenbrowne.com/ranking.html
https://answers.microsoft.com/en-us/...4-eb4a1c82bc26
Cheers,

[pbDudley]

Alright I didn't know that. It looks like this has to be performed on a number field? I was reading over the examples

But if I just want to use this ranking feature just to add a number for the records can it be done on a text field? I assume I could pick any field if I only want it to add a number, not technically ranking any of my records though.

[Gicu]

What is your unique ID\primary key for the table? Where do you need this numbering, if in a report you can use the built-in feature: https://www.techrepublic.com/article...rds-in-access/

https://accessexperts.com/blog/2015/...ing-in-access/

Give us some better sample data to understand what you need.

Cheers,

[pbDudley]

Its not for a report, I know how to add the numbers for a report..
My works database is split into 2 with some repeating data in each, but unique for each of these databases.

I'm trying to create a custom database based off these existing tables. But when I join all of the fields needed(its a ton of tables) I don't get all of the records.
One of the tables has a field for this, where a number is given for each record stored in one of the tables that I need.

So if one unique record, X, has 5 records, than X is repeated 5 times but another field has 1, 2, 3, 4, 5 stored in the table. So to go around my issue with the records not coming out right, this numbered 1,2,3,4,5 field would work to join the 2 databases. There are probably a bunch of other ways, but no query that I build to join the 2 gets around this. The reports generated for each of these databases are separate. I'm trying to create this merged type of report but the records wont total correctly or only 95% of the time

Since I'm probably not explaining this the best way I really just want a number in descending order for each record. Is there a way to just do that or does it have to be based on a numbered field(to rank) if so I can try that

For sample data Id have to try to come up with some fake data and add that.

[Gicu]

Please show us your tables (hide any sensitive info), there is no "absolute" descending order as that depends on which field to sort on. Are you saying that you want to join two tables and one has a record number field and the other doesn't? How do you relate the records now?

[pbDudley]

There are unique records for each of the tables that I use to join, to upload the tables wouldn't work for me, just wanted to see if there was a way to add this number field. I have a fair amount of experience now I'm just not explaining it correctly to you so the easiest question was: how to add a number for each record, I can try a descending field to use, probably the Unique Id for the table


thanks

[Gicu]

Yes, look at the subquery example in the first link, you would use the unique I'd from the table and sort on that.

Cheers,

[pbDudley]

Thank you. The ranking query method seems to have worked for me

[pbDudley]

Hello
Ok, I made a sample database, with the issue I get when I narrow all of the table data into 2 queries. The 2 queries are of the same data, but in this example it has Plant type, one plant repeats itself in 2 queries, TREE_1 but the rest of the data is all related, its just based on 2 reviews. Since its the same unique plan, I'm trying to combine the 2 queries into one report but the way my data is now its separated out into 2 reviews and I cant get the 4 records that are shown. The goal would be to have the 2 queries with the same plan but different data collected added into one report but still just the 4 records with the 2 reviews data added. The table tbl_TREE_BIO_CHEM_COMBINED is what I want, but I manually added the fields to show it this way.

Table tbl_TREE_CHEM has a TREE_NUMB field, 1,2,3,4 but TREE_BIO table doesn't have this. So when I was trying to generate the record numbers it was because I was trying to join the records based on this and what I want would work. Tjis tree number field is in every tbl_TREE_CHEM plan, so plan N-9876 would have tree_number, 1,2,3,4.. for each record. I could try to have the oracle developers add this field to the TREE BIO table but that would take a lot longer, I dont see another field that I can join to give me all of the records as seen in the combined table
I was looking at a subform method for the 2. But 90% of my records, the 2 queries joined works, its when I have TREE1, TREE1, repeated types, where it doesn't.

This is a very simplified version but the 2 queries with the dilemma is the same. Perhaps there is an easy way and I don't have as much knowledge as I thought.

If I could create one report, report based on qry_tree_bio, then add a sub report for qry_tree_chem, perhaps that would work. I dont technically need 2 join the 2 queries but I do need the data to be on one report, one page for each, TREE_1 on one page with BIO and CHEM data for that record, then page 2 TREE_2, with that data and so on

The query would have criteria as [ENTER PLAN] the user would enter the plan, N-1234 and the report would generate. I also have one other query to add to the report but that one is set up better.
My works database is just broken out into 2 similar review types as BIO & CHEM but the plan is the same, the plant names are all the same, this example just has the repeating TREE_1. If I didn't have this TREE_1 repeating, joining the 2 queries works. But this type of example can occur.

The table tbl_TREE_BIO_CHEM_COMBINED would be the goal to create the report. To somehow get the 2 reviews into one.

thank you

[Gicu]

What is TREE_ID and why is the same for all for records?

[Gicu]

How do you get these tables? Are they linked table from an Oracle back-end or do you import them from some extract files? You would need to add some unique ID for the BIO table to be able to do what you want.

Cheers,

[pbDudley]

Yes, its from an oracle database that my work created and I join to the tables via the ODBC connection in access.
The TREE_ID would be the equivalent to what is the unique ID for the data, then it splits into the 2, BIO and CHEM, still has the TREE_ID but now each one of those gets a Unique review ID, that's where the data separates
Ok I thought that's what I needed, and why I was trying to generate that record number field in my initial question, then I could join that
I could try the sub report method, I can have these 2 separated but if possible the final would be on one report.

Thank you for the help

[Gicu]

But in your sample all four records have the same TREE_ID. Unless you have a unique id in BIO you cannot avoid the problem you are having, regardless of the approach you take (query, report\subreport, etc.) as there is no reliable way to identify which of the two TREE_1 records is which and how it relates to the Tree_Number value in CHEM.
One approach would be to use a local Access temporary table that has an autonumber ID field; you would empty it, append the BIO table to it then use that for your query report.

Cheers,

[pbDudley]

The temporary table was the other idea that I think might work.
The TREE_ID would be equivalent to the plan name that repeats. The database has a plan number that is unique, than another numerical Unique ID that is equivalent to the plan, but the plan name is what the users know and are assigned, the TREE_ID is how the oracle database is joining, I just prefer the plan name as its what the users are assigned.
I think the temporary table method could work. That's what I was researching after the ranking method, which would work but the numbered fields that I was trying to use,(one of the ID's) wouldn't sort in a correct order, maybe bc they are stored as text I think