Using acces to manage change control records. Table management help

[jambonjamasb]

Hi all,

I am new to this and am looking for some direction I have the ideas, but am having trouble putting it into practice. Any help would be greatly appreciated.

I have set up three tables.

Table one

this contains 20 fields. This data is uploaded to access by a macro in access from excel.

This table contains the information from the change record from our suplliers. It has a unique identifier, the ocp (Change number) and details from the change. I am only really interested in around 5 of these fields.

Table 2 contains 2 fields. The OCP number and also the Post Office owner, which is a drop down table listing names, i.e my name.

Table 3 contains 7 fields. The OCP number and all action Post Office need to carry out. for example excepting the change, communicating, actioning and reviewing. All of these fields need to be completed by the POL staff and apart from the OCP number are manually populated.

I have linked all three tables by the OCP number.

The OCP number is uploaded via the macro to all 3 tables at the same time.

I next went into reports and created a report showing the fields from all 3 tables that I needed to view, but this contains no fields.

I want to complete the POL actions to show which changes we have accepted rejected etc, but there are no records, apart from in my first table?

What have I done wrong and should I actually be doing this an easier way? any suggestions greatly received. I have used databases before but this is the first one I am trying to build. I can supply any data required thank you in advance.

[jambonjamasb]

BTW I am using access 2003 and microsft excel 2003. I convert the word doc to excel, and this uploaded by a macro to Access, i only use certain fields from this data.

[MMcCarthy]

Essentially I think you just need to change your joins to left joins starting with the imported tables. This will show all records in the imported table and only those records from the other two tables that are matching.

[jambonjamasb]

Essentially I think you just need to change your joins to left joins starting with the imported tables. This will show all records in the imported table and only those records from the other two tables that are matching.

Ok will give this a try thank you.

[jambonjamasb]

I am showing my obvious i experience here. I assumed you meant that the left join to be set up as a query. when I did this it returned a syntax error for the expression used. I have looked on the microsoft help section and am unsure what is whrong I will try again. Cheers

[jambonjamasb]

Right,

in my query I have joined POL Operations Owner table to OCP Base table. I then Joined the POL actions Table to the POL Operations Owner table. All three tables are joined by the OCP ref which should be identical in each. but now I am getting a mismatch error.

I set the query up using the query wizard and selected only some of the fields from OCP Base table and all the fields from POL Actions and POL Owners.

I have been using my access book and I am confused as to what I have done wrong.

I linked the tables earlier through their relationships earlier and am wondering if this is having any impact.

Am I explaining this well enough?

[jambonjamasb]

The mismatch error I am getting is: -

Type mismatch in JOIN expression. (Error 3615)
A JOIN expression is attempting to join two tables on fields of incompatible data types. For example, you will get this error if you attempt to join a Memo field with a Text field.

As far as I am aware their are no memo fields but I will recheck.

[jambonjamasb]

I worked out the mismatch and it was an obvious text and number error, sorry all.

This has now returned exactly what I wanted all the records are joined with the fields I want.

The problem I have is while this is returning the tables and fields I want, I wanted a report type screen that I could complete the POL Actions in rather than the standard excel workbook layout.

When I create this format in forms there is no records. I wanted to set up queries to show uncompleted change request, changes with no responses, changes actioned and changes reviewed which is easy enough to do, but I don't seem to be getting the format I want. I also will be trying to set this up with a front screen with buttons to return the above queries.

Thanks again for explaing how to join. Hopefully I am clearly explaining what I am trying to do, if not I will try again please let me know.

[MMcCarthy]

In this case you would set up the main form from the main table (POL Operations Owner table). Then use subforms for the other two tables.

[jambonjamasb]

In this case you would set up the main form from the main table (POL Operations Owner table). Then use subforms for the other two tables.

Thank You that has worked perfectly.

[MMcCarthy]

Thank You that has worked perfectly.

You're welcome.