Combining fields in a report

[catlady]

I have 4 tables all with a primery key which is the reference number and to distinguish between them I have also given each table an ID (A, B, C, and D). What I want to know is if there is any possibility of combining the ref number with the ID on a form thus making a number, for example 63B, or A327. Each table has different sorts of products so I don't want 2 with the same ref. number. If this can't be done, is there any other way I can make the reference numbers different on each table? Any help appreciated.

 

[Fornatian]

This can be done in a report by adding an unbound text field to report and using this as the control source:

=[RefFieldName]&[RefNo]

If your A is stored in a table.

Or if you only use the report for a certain table you could just as easily use:

="A"&[RefNo]

How similar is the information in the tables? Are they truly different or different because of a single/a few factors or properties. I ask because I think from your post that the tables should be merged with lookup tables for characteristics.
Do you have to do the same analysis across numerous tables, if so you should really consider merging them

 

[catlady]

Thanks for your reply,

The tables consist of different customers buying 4 different services, but we get them from the same supplier so the info needs to be kept separate to make things easier for us to control them. If there is an easier way to do things I would like to know - all my experience with Access has been through trial and error.

Cheers.

 

[doss]

I think I understand what you are saying. Here is what I would do instead of having 4 separate tables for each product.

Table 1 = tblProductType. It would have 2 fields, Type and Description. For instance the Type could be the key field and you could have the letters A, B, C, D with a description of the product type.

Table 2 = tblProducts. This tables primary field would be named something like "Product_ID" and would be an autonumber field so there would be no duplicates. This table would also have a field for product type which would hold A, B, C, or D and link to Table 1.

Then your reference number would be a letter and then a number and the number would never be duplicated since they are an auto generated field.

Then you can do the thing that Fornatian listed as far as combining the fields.

Hope this helps.

 

[Fornatian]

Post the table structures for your four tables so we can judge if they need merging, sounds possible.

I'd also advise buying a good Access book for reference(or coming here more often )

 

[catlady]

Fornatian, how do I post a copy of the table structures- I don't understand what u mean.

I do have a book but no time to read it - spend all my spare time experimenting with the pc and having to reinstall!!!!!!!!!!!!!!!!!!

 

[doss]

I think what Fornatian is saying is that we need to know what your tables look like in design mode and to see how they work together to get an idea of what you have and what you are trying to do.

Can you zip you database and attach it? That would help....

 

[catlady]

Here is the file - any help to make my life easier would be greatly appreciated.

 

[doss]

CatLady,

It looks like you are having trouble posting your database.....

You can email it to me at kdoss@precweb.com

 

[catlady]

Try again with the file although I have also sent it by e-mail as suggested. C

 

[doss]

I've attached a muchly modified database. I'm unsure on what some of your fields are and what they contain as far as information.

Origianally, the 4 tables you had were quite similar and contained a lot of the same type of information. You can see by my version that I pretty much combined the tables (I didn't put all of the fields back in), and added some lookup and ref tables.

I made a form for data entry and took one of your reports and modified it so you could see how the Ref ID shows up.

I have a couple of sugguestions as far as database structure.

1. Don't use spaces when you name your tables, forms, queries, etc. Use an underscore such as Table_Ref. I know it seems like a simple thing, but it will save you a lot of time and trouble later on.

2. Read up on database normalization and relationships between tables. I know sometimes it's hard to find time to pick up a book when you are trying to get things done. But it will also save you a lot of time and trouble in the end.

I am not an Access Expert, so if anyone else has any other suggestions.......