Hi,

I am new to this and need a bit of help please.

There are three fields:
Field 1
Field 2
Field 3

Field 1 is always constant and = X
Field 2 is always dynamic = Y+1 (new number for every new record)
Field 3 - this is where I need help

Question. Can I combine Field 1 and Field 2 to get Field 3 to show X+(Y+1). In other words can I have whatever is in Field1 added to Field 2 and have the result listed in Field 3. There are no reports. This needs to be done inside the table. Access 2000.

Thanks in advance.

Don't store calculated fields. They're easily computed when needed.

Just wanted to add to what llkhoutx said.

If Field1 is truly static and the same for all records you don't need to store it at all. If you mean it is fixed for that particular record, then you would store it. If Field3 is the sum of the other fields, then you can perform this sum in a query in a calculated field. You can then use this query in any situation where you would use the table. There is no reason to hold this in the table, and the database users shouldn't be seeing the tables, anyway.

To concatenate the fields in a query:
Select fld1 & fld2 As fld3
From YourTable;

Thanks everybody.

The Fields are actually Columns in my table. Sorry if I messlead you.

The info in the Column 1 is static text that is set as default for each record. The Coumn 2 is numeric and different and unique for each record. I believe I would need to concatenate Column 1 and 2 and result would be entered into Column 3. But how do I write the query?


Thanks.

molsonbubba,

Here is what the good people are telling you.


The Fields are actually Columns in my table. Sorry if I messlead you.
Believe me when I tell you that they understand that column=field for this purpose.


The info in the Column 1 is static text that is set as default for each record.

If the info in column 1 is ALWAYS EXACTLY the same, unchanging value (e.g. "Apple" or 45, as opposed to the date the record was entered (which could be set up to be a default value for the field)) for each and every record in the table, and is NEVER different OR absent for any record, there is no (good) reason to store this data (whatever it is) repeatedly (for every row in your table). You would achieve the same result by writing a query to provide this effect.

e.g:-
SELECT "Apple" AS Field1, YourTableName.Field2, YourTableName.Field3
FROM YourTableName;

This query would create the appearance that "Apple" was stored with each and every record, without actually having to store it (which would unnecessarily take up database space so is not good practice).


The Coumn 2 is numeric and different and unique for each record. I believe I would need to concatenate Column 1 and 2


Yes, you can concatenate the fields, as Pat said, in a query - refer Pat's post for how to do that.

Assuming you are happy to eliminate the first field, field3 really becomes:- somethingstatic & Field2

Your query would then look like

SELECT "Apple" & [Field2] AS YourConcatenatedFieldName
FROM YourTableName;


...and result would be entered into Column 3. But how do I write the query?

If column 3 is "a function of" columns 1 and 2, (or static data and column2) good database design practice would discourage you from storing this "result" in field 3 (in the table), again as it would unnecessarily take up space. Instead, simply create a query that performs this calculation (as above), and use it in place of using the table, when you want that result. This would give you your field3 without "entering" it (storing it) in the table.

Generally speaking, the people here have BUCKET loads of experience and could very simply tell you how to do exactly what you have asked, but instead they are offering you the help that you aren't asking for, because they know there are good reasons NOT to do what you say you want to do, and they are helping you to "do it right" in the first instance. This will serve you better in the long run.

Be brave and strong. Benefit from their knowledge and wisdom.

Hope this explains things.

Regards

John.

I figured out SQL for this and this is what it looks like. But I need help with the database. How do I make the table perform the quiery automaticaly upon creation of a new record? The idea here is to have Access update Coulmn 3 automaticaly.

(Column 1 is AE100)
(Column 2 is ID)
(Column 3 is BookingNumber)
SELECT [AE100] & [ID] AS BookingNumber
FROM iBookingAE;

Did you read my post, or did our posts "cross in the mail" ?

I think our posts got crossed in the mail. Thanks for your advise. I am not trying to upset any1 and really uppreciate the help. I am really new to this. I got books galore here and learing this stuff as I go.

The BookingNumber column has unique info. It is needed to be there as it will be used by my website users to search their orders.

So the quiery now does what it should but how do I make Access update the BookingNumber column automaticaly when a new record is created? Can this be done?

Thanks.

I figured out SQL for this and this is what it looks like. But I need help with the database. How do I make the table perform the quiery automaticaly upon creation of a new record? The idea here is to have Access update Coulmn 3 automaticaly.

I hope you read John's post carefully as it is an excellent summary of your situation.

I suspect you are used to using a spreadsheet rather than a database. In a spreadsheet the data is usually displayed in the same place that it is entered and stored. In a database, tables store the data, queries manipulate the data, forms are for entering and displaying the data and reports are for printing it out.

The automatic display of the BookingNumber is easilly achieved in a form, which would be bound to a query which will be based on a table that stores your data. See the attached.

I think our posts got crossed in the mail. Thanks for your advise. I am not trying to upset any1 and really uppreciate the help. I am really new to this. I got books galore here and learing this stuff as I go.


You are welcome. We (mostly) just want to help. I did not think you were "trying to upset any1", and wasn't intending to imply that. You said you were "new to this", and I extrapolated that to mean you haven't spent weeks/months/years reading this forum either so might not know much about the people who "hang around" here - so I thought I might spell it out in full for you. I didn't mean to cause offence either.


The BookingNumber column has unique info. It is needed to be there as it will be used by my website users to search their orders.

So the quiery now does what it should but how do I make Access update the BookingNumber column automaticaly when a new record is created? Can this be done?

Thanks.

Excellent. Yes, it can, and should. Set the field type to "AutoNumber" in table design. Sounds like you should then also make this field your "primary Key" for this table. - read up on that if you have to.

HOWEVER : Don't expect that numbers will always be consecutive. This is far from guaranteed. The DOC Man explains it well in this post (http://www.access-programmers.co.uk/forums/showpost.php?p=337136&postcount=6)

Hope this helps.

John.

Hi John,

Thanks. The problem is the Autonumber is already set to the ID column. The ID is also the Primary Key. Once a user submits the booking request, www updates Access, autmoticaly pulls the booking number from Access and displays it to the user. The booking number given to the user is based on unique ID column. AE100 is just a text prefix that needs to be added to the ID and stored togther (as AE1005005 for example) in the BookingNumber so that the user may come back later and trace the status of the order using the booking number.

Alex

How many times/ways do we have to tell you that it is just plain wrong to store the concatenated value?

I dont understand guys - why cant we give this guy the solution he is after, rather than just questioning his logic - we dont know precisely why and what he does with the thing he is asking for - Basic rule is everyone has got their own way of doing things and it necessarily does not have to be the most ideal-efficient logic/programming-politics correct !!

Guess you will agree with me.....if so please post a solution, as I dont know how to do it and I want this solution for myself as well

Field one - eg. "FP"
Field two - [auto number] - eg. "001"
Field three - Field one + Field two - eg. "FP001" - I want access to populate this value automatically in Field Three

how do we do that ?

In simple terms, I just want to setup an autogenerated column/field, that will produce values in the following sequence

"FP001"
"FP002"
"FP003"
...
...

That's it.........HELP HELP

Thanks

Karthik

Most of the people that post answers on this forum are either professional programmers or aspiring to achieve professional standards. Becasue of this, it is not sensible to ask for help to do something that does not measure up.

What you are wanting to do is not the best way of achieving your goal. It's not a question of there being two equal choices, there is one efficient and future proof way, and one way that is inefficient and prone to producing inconsistent data.

allright then...

so how about the best solution for the problem below...!

In simple terms, I just want to setup an autogenerated column/field, that will produce values in the following sequence

"FP001"
"FP002"
"FP003"
...
...

I'm not using any forms, queries or macros etc....simply because I dont know how to use them.

Just want my table to be populated automatically with the kind of data mentioned above......

any solutions anyone

cheers

Karthik

You can't. Tables don't have that functionality. An Access table is not a spreadsheet.

This is not a native Access ability. What you're asking to do can be done if you have a good amount of programming skill, which I think you said you don't have. I totally recommend against doing it since it creates a "smart key" which always comes back to bite you.

Many thanks guys for the prompt response, very helpful and saved me a lot of time from trying out unwanted and unworking things in the field properties !!

Figured out to do it using forms(after learning forms...god it is not that difficult as I initially thought it was going to be).

Thanks again dudes, you are all invaluable, will see around with new problems next week I guess

Cheers

Karthik

I'm not using any forms, queries or macros etc....simply because I dont know how to use them.



If you restrict yourself to tables the data base that results would be extremely limited.

Think of the tables as the foundations of a house. If you only had the foundations then you would not have much of house to live in. If you wanted to restrict yourself to a table or tables then Excel will offer more. Tables in Access are basically dead or lifeless. Kind of like the warehouse at the back of the retail outlet.

Tables in Access are basically dead or lifeless. Kind of like the warehouse at the back of the retail outlet.I like the analogy. I hope you don't mind if I use it at work!