Linking 2 tables

[aftabn10]

I have two tables where one is a master table that gives me the name of the queue, who it belongs to, and where its located. What i am looking to do is link it to another table of results e.g. WE30.10.09 that has the name of the queue, volume, and time.

MASTER
Queue Name
Owner
Location

WE30.10.09
Queue Name
Volume
Time

What i would like to do is combine these two where if the queue names between the master and weekly table match up then add the volume and time value alongside. This needs to be updated on a weekly basis so eventually, i could have several weekly files but just the one master. Also need to make sure that if queue name is not found on weekly file than need to leave this blank or with a 0.

Thanks in advance.

 

[Brianwarnock]

Also need to make sure that if queue name is not found on weekly file than need to leave this blank or with a 0.

This sentence makes no sense to me.

Brian

 

[aftabn10]

Thanks for your reply Brian. Basically what i mean here is that master table will have some queues that might not appear within the weekly table:

For example: WE30.10.09 table their was no data for Queue 3

so when linking the two tables Queue 3 should show up with no data e.g.

Queue Name | Volume | Time
Queue 3 | 0 |00:00

I am guessing this will have something to do with join relationships, but apart from that i dont know how to basically link it all up.

Thanks once again and hope that makes sense.

 

[Brianwarnock]

Ok , it read as tho you wanted the q number to show as 0.

Your join will be an outer join , Left or Right, so that you select all records from the master table and where there is no record in the weekly table use functions like Nz or IsNull tests to enter the values you want.

To select the relationship click on the Join line which will open up a local menu and select from there.

Brian

 

[aftabn10]

Thanks Brian that works perfectly. Just out of curiosity what would happen if a new Queue Name appeared in the Weekly Table that didnt exist in the Master Table? Would this be automatically added into the Master?

Thanks once again for all your help.

 

[Brianwarnock]

Thanks Brian that works perfectly. Just out of curiosity what would happen if a new Queue Name appeared in the Weekly Table that didnt exist in the Master Table? Would this be automatically added into the Master?

Thanks once again for all your help.

No it wouldn't, wouldn't that be an error situation?

You would only find this out by running a different query.

Brian

 

[aftabn10]

Unfortunately the master table does not consist all the queues. So will need to workout how to add a queue name if their is no match with the master. Is their a possibility to write some sort of code or query?

Sorry to be a pain by the way, thanks for all your help.

aftabn10

 

[Brianwarnock]

you will need a query something like

INSERT INTO master ( [queue name] )
SELECT weeklytable.[queue name]
FROM tblesub LEFT JOIN master ON weeklytable.[queue name] = master.[queue name]
WHERE (((master.[queue name]) Is Null));

Basically a query similar to the previous but this time selecting all the weekly records but only when the master is null , and converting this into an append query.

Brian

 

[aftabn10]

Thanks Brian,

I managed to write the following query (all due to your help)

INSERT INTO Sample ( [Friendly Queue Name] )
SELECT SampleWeek.[Friendly Queue Name]
FROM tblesub LEFT JOIN Sample ON SampleWeek.[Friendly Queue Name] = Sample.[Friendly Queue Name] WHERE (((Sample.[Friendly Queue Name]) Is Null))

but for some reason I get the following error:

Syntax Error in join operation.

The only part that i didnt understand in the example you gave was after the FROM text, you have stated "tblesub". I was trying to work out if this was supposed to be FROM Sample but i still get the same error.

Any ideas why?

 

[Brianwarnock]

Sorry Tblsub should have been the weeklytable

I had done a little test and then converted the names to be more meaningful for you, I missed that one. In your example it will be SAMPLEWEEK

Brian