Creating a relationship between two uncommon tables.

[Mr. Southern]

I may be overthinking this but I have two tables without a common link. Eventually, I am trying to create a report that compares two different years of data. The two tables were created from append queries. Is there any way I can create an additional column that shows the row number? I would like to find the difference between workdays. For example (see picture), some days will match up but others will not (day 4).

 

[theDBguy]

I don't see why not... At least, it's worth a try, right?

 

[Deleted member 147267]

If there's no linking field, why would anyone presume that ID 1 in table 1 has anything to do with ID 1 in table 2? If those are autonumber fields you're thinking of joining together the idea violates a basic tenet, which is that autonumber PK's should never be thought of as meaningful data. If it produces realistic records, it's a major fluke, and a lucky one at that.

 

[Mr. Southern]

I do not have Autonumbers in this table. I am trying to figure out a way that I can link the two tables. I have seen a few things on RowNumber but haven't been successful with it.

 

[June7]

It may be the only practical way to accomplish what you want. How do you want to populate these two row number fields? Manual data entry or VBA procedure or calculate in query?

BTW, they could actually be 1 table. Do you have a table for each year? How many years?

Date and Day are reserved words and advise not to use reserved words as names for anything.

Day field is not needed as this can be calculated from Date.

Should Accounts field name actually be Amount?

 

[Mr. Southern]

It may be the only practical way to accomplish what you want. How do you want to populate these two row number fields? Manual data entry or VBA procedure or calculate in query?

I do not want to manually add. It will be roughly right around 200 rows of data. What do you recommend?

BTW, they could actually be 1 table. Do you have a table for each year? How many years?

Yes, I have two tables. I will only be looking at the previous year and the current year.

Date and Day are reserved words and advise not to use reserved words as names for anything.

Day field is not needed as this can be calculated from Date.

I will be changing these field names eventually.

 

[June7]

Consider this query approach:

SELECT Q1.*, Q2.*, Q1.Amount-Q2.Amount AS Diff
FROM (SELECT EntryDate, Amount, DCount("*","Table1","EntryDate<=#" & [EntryDate] & "#") AS T1Seq FROM Table1) AS Q1
INNER JOIN (SELECT EntryDate, Amount, DCount("*","Table2","EntryDate<=#" & [EntryDate] & "#") AS T2Seq FROM Table2) AS Q2
ON Q1.T1Seq=Q2.T2Seq;

 

[Mr. Southern]

Consider this query approach:

SELECT Q1.*, Q2.*, Q1.Amount-Q2.Amount AS Diff
FROM (SELECT EntryDate, Amount, DCount("*","Table1","EntryDate<=#" & [EntryDate] & "#") AS T1Seq FROM Table1) AS Q1
INNER JOIN (SELECT EntryDate, Amount, DCount("*","Table2","EntryDate<=#" & [EntryDate] & "#") AS T2Seq FROM Table2) AS Q2
ON Q1.T1Seq=Q2.T2Seq;

From what I can tell, it seems to be working BUT access freezes up after I run the query lol.

 

[June7]

It worked for me with sample data provided. If you want to provide db for analysis, follow instructions at bottom of my post.

 

[Mr. Southern]

It worked for me with sample data provided. If you want to provide db for analysis, follow instructions at bottom of my post.

It works if I change it to a make query..... Link doesn't work

 

[June7]

You mean a MAKE TABLE query? Not sure what you mean by 'Link doesn't work'.

 

[Mr. Southern]

Yes, a make table query. The cpearson debugging link doesn't work.

 

[June7]

Too bad. I will remove the link, however, it has nothing to do with suggestion to provide db for analysis.

 

[Mr. Southern]

Sorry for responding so late. A few things came up. I was able to get it to work based on your suggestion. I appreciate your help.

 

[theDBguy]

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

 

[jdraw]

The cpearson debugging link doesn't work.

Yes that has been identified just recently --too bad. I have a link in my signature to a youtube by Steve Bishop that may be helpful.
Good luck