Suggestions? How should I approach this DB problem? 2

[Faded]

Hello,

I am working with a SQL Server database that has poorly normalized tables, and trying to create an Access front end to add functionality to the existing DB.

Here's the details:

There is a table called "in_grp" which holds information (address, contact name, phone nuber, etc..) on tour groups visiting our hotel. The primary key is "gcode". The problem here is that the same tour company may send 20 groups to our hotel, and this information has to be entered 20 times because there is no reference to a "Master Group" table which can hold the tour company info and the in_grp table just holds the info relevant to each of the 20 groups.

I want to add a Master Group table, but when a salesperson books a new group with the existing interface it will have no way of linking to my table. I would like to import/link to the in_grp table in Access and create a front end that will locate groups that aren't yet linked to my Master Group table and be able to input new master group information and link that group to it.

I'm not sure how to go about doing this. Any suggestions?

Sorry if my explantion is missing anything important. I'll be happy to elaborate if anyone can help out.

Thanks in advance,
Faded

 

[allanon]

Well, not quite sure what you are asking here but the stuff I do understand here aint too difficult.

1) You wish to create a new Group Master table.

Ok. This part I'm thinking you will have no problem with.

2) In the existing front end application there is no Group Lookup.

Change the interface form to either a) on exiting a group id text box grab the id entered, read the GroupMaster table, populate any group info that is retrieved from the GroupMaster table; or b) if the group id is blank change the group name to a dropdown combo box created from the GroupMaster table and populate based on their selection. If they enter a new name then create a new GroupMaster record. I might even suggest bringing up a group master form in modal an forcing them to put in relevent data.

3) Groups are not yet in the new GroupMaster table.

Do a select DISTINCT in_grp.name, address etc from the in_grp table and update the new table with this info. Next step I would do is remove ALL of the info save on the GroupMaster record from the in_grp and in it's place I would place a group id from the GroupMaster. Make this a 2 step, one time process and you should not have too many problem.

If your front end was written by assigning queries to forms rather than tables this change of yours should not be too difficult. If, on the other hand you connect directly to table then you may have a challange.

 

[Faded]

I think the DISTINCT query is on the right track.

The front end application that the group information is initially entered is proprietary software so I can't change or delete information from those tables because it is needed for reporting etc. from their application. Also, the interface cannot be changed.

So, for my application I need to import new information from the unalterable in_grp table in the proprietary app on a daily basis, and append it to a table that I can add a fk to GroupMaster in my app. How do I create a query that will be run day-to-day and will only grab the stuff that does not currently exist in my db table? and then append it to my table.

Thanks, Faded

 

[allanon2]

SELECT DISTINCT in_grp.TheData
FROM in_grp
WHERE (((in_grp.TheData) Not In (SELECT DISTINCT GrpMaster.GroupName FROM GrpMaster )));

This selects all of the distinct records from in_grp where they do not already exist in GrpMaster

 

[Faded]

Thanks, I got it working.

Faded