Appending From Unrelated Queries/Tables

[Bobby9779]

I know it is strictly not acceptable from a purists point of view...(Here comes the BIG BUT!).

BUT

I have two queries, that work fully...

[FONT=Century Gothic][FONT=Century Gothic]SELECT (DateSerial(Year(Now()),(Month(Now())+1),1)+week) AS [Date][/FONT]
[FONT=Century Gothic]FROM Week;[/FONT]
[/FONT]

Week.week just specifies a list of "increments" not including weekends.

[FONT=Century Gothic][FONT=Century Gothic]SELECT "2", Service_ID, "Week"[/FONT]
[FONT=Century Gothic]FROM 2IC_Week;[/FONT]
[/FONT]

"2" will be a foreign key in the following table, as will service_ID and "week" is just a text input.

I understand Cartesian Products and why they happen and could be useful but I simply want to take A, B and D from one query and slide C in from another. They are completely unrelated though and do not share similar fields, so sorry no UNION SELECT either!

The table I need to fill from the gathered data has [Allocation_ID] (AutoNo), [Duty_ID] (from 2nd query), [Service_ID] (from 2nd query), [Date] (from 1st query) and [Day] (from 2nd query).

Last bit of info is that I have never used VB (Visual Basic) and would like to avoid it if at all possible. I am doing this as a project/degree thing so altering tables and stuff is fine as long as I can back my reasons.

Please help.

 

[Guus2005]

did you already try this?

SELECT (DateSerial(Year(Now()),(Month(Now())+1),1)+week) AS [Date],2, Service_ID, "Week"
FROM Week, 2IC_Week;

It's a cartesian product of the two queries you presented.
Now you have to filter out the records that are meaningfull to you.

I can't figure out what the meaning is of the resulting query.

 

[Bobby9779]

I am after the 2nd querys results with the 1st's results bolted on.

So record A from the 2nd query has record A's date from the 1st query.

Same for record B, C, D etc.

It is based on several queries, based on several factors, so it needs to be able to handle any personnel.

My only idea is to be able to select the 1st, nth and mth records etc from the resultant Cartesian, but is it possible to select certain records based solely on where they place in an output?

 

[Bobby9779]

Can anyone help me with this please? It is driving me insane!

 

[Bobby9779]

Sorry to keep posting, but I am thinking more and more about it and I think I can live with a Cartesian Product, IF I can select from it's outcome, the 1st record and then every subsequent nth record.

Any ideas on this?

 

[Guus2005]

Still don't know what it is that you want.
Creating a query with some bogus fields isn't a logical thing to do.

If "2" is a foreign key in another table why add it as an extra field to the query and not use a where clause to filter it out?

I am after the 2nd querys results with the 1st's results bolted on.

What does that mean? English is not my native language and i am not familiair with this expression.

Please post a sample database describing the problem.

HTH

 

[Bobby9779]

It is too complicated to try and explain my full database on here...


Is there any way you can add unrelated data in access?

I want to run 1 query to get my personnel and then add dates to them.


For Week duties there will be 22 people and these need to be partnered up with 22 week day dates.


Likewise for Weekends, but 8 and 8.


I have a query that produces these dates and I have a query that produces the list of Personnel, but I need to match them up!

 

[Guus2005]

You don't have to explain your com,plete database.
As i suggested in post #2 you should create a cartesian product to connect the days with the personnel.

Can you create a sample database with two tables, personnel and dates only. That's all there is needed.

HTH