Jump to content
Oblong

Microsoft Access Question

Recommended Posts

I know we have some database gurus here and I have, what I think, is a simple database question. I am building a new table from other tables that I link fields from. The issue I have is multiple rows for items with more than one records from teh same field.

For example, I build a new table from others and end up with this:

Some Unique Identifier, Address, City, State, Zip, Name

If I have 3 "Names" for that unique identifier, I get 3 rows, one for each name. I want to figure out a way to make it look like this:

Primary Key, Address, City, State, Zip, Name1,Name2,Name3.

Is that possible using Design View? Could I use the Group By feature?

Share this post


Link to post
Share on other sites
FanDuel Michigan Sports Betting

FanDuel Michigan $100 Launch Offer

Michigan online sports betting is launching shortly ( December 2020 or January 2021). Pre-register at FanDuel Sportsbook and get $50 free sports bets + $50 free online casino bets with no deposit necessary. Claim $100 at FanDuel Michigan Now

I know we have some database gurus here and I have, what I think, is a simple database question. I am building a new table from other tables that I link fields from. The issue I have is multiple rows for items with more than one records from teh same field.

For example, I build a new table from others and end up with this:

Some Unique Identifier, Address, City, State, Zip, Name

If I have 3 "Names" for that unique identifier, I get 3 rows, one for each name. I want to figure out a way to make it look like this:

Primary Key, Address, City, State, Zip, Name1,Name2,Name3.

Is that possible using Design View? Could I use the Group By feature?

You are probably like me. I fiddle with access, excel etc for work a lot. I can usually figure things out. I am visual though and need to work through things, but it sounds like something you could fix with Design view.

The best way to figure these out is to know the terminology well enough to perform searches in the help function that actually help.

Share this post


Link to post
Share on other sites

I think you need to transpose, which I think is only doable via code in Access. If you can, you can do a paste transpose from Access to Excel.

ETA: Transform query?

https://social.msdn.microsoft.com/Forums/office/en-US/36f851d5-470e-46a1-aa8d-61013b133a8a/how-do-i-convert-rows-to-columns-in-ms-access-using-msaccess-queryselect-statment?forum=accessdev

Share this post


Link to post
Share on other sites

Twilight Zone moment. I was just scouring the internet looking for help with Access.

Share this post


Link to post
Share on other sites
I know we have some database gurus here and I have, what I think, is a simple database question. I am building a new table from other tables that I link fields from. The issue I have is multiple rows for items with more than one records from teh same field.

For example, I build a new table from others and end up with this:

Some Unique Identifier, Address, City, State, Zip, Name

If I have 3 "Names" for that unique identifier, I get 3 rows, one for each name. I want to figure out a way to make it look like this:

Primary Key, Address, City, State, Zip, Name1,Name2,Name3.

Is that possible using Design View? Could I use the Group By feature?

guess I don't see what is happening clearly. You have an option of 3 name fields to identify each person? If that is the case then your 'person' table should not use name as it's primary key. Add a record number field there as a truly unique key and doesn't that solve your problem? You only get one record per 'person key' - or probably I'm just missing what you are describing.

Share this post


Link to post
Share on other sites

Name is not a primary key its just a value I got from another table. I only listed the data this way to make it understandable. Thats not my real data.

In essence I have, for example, 5 rows of data where all the columns - in my case theres 14- are the same except one. Call that field 14. I want one row and the contents of field 14 to be in one cell, delimited somehow. I currently have 33000 rows and outside of my problem field probably only 13000 unique records. I cant modify the source data. Its not critical. Just somethin I come across a lot and wondered if it could be done.

Share this post


Link to post
Share on other sites

I'm aging myself, but I was a FoxPro guy.

Select()

From()

Where()

I'll be following to see if I can keep up with the new stuff.

Share this post


Link to post
Share on other sites
Name is not a primary key its just a value I got from another table. I only listed the data this way to make it understandable. Thats not my real data.

In essence I have, for example, 5 rows of data where all the columns - in my case theres 14- are the same except one. Call that field 14. I want one row and the contents of field 14 to be in one cell, delimited somehow. I currently have 33000 rows and outside of my problem field probably only 13000 unique records. I cant modify the source data. Its not critical. Just somethin I come across a lot and wondered if it could be done.

Hmmm. Over my head I think. Even you you requeried the the table to collapse the records that only vary by field 14, it seems you'd then lose the data in field 14 for all the discarded redundant records. But beyond that I have no idea how to rebuild a single field in Access by merging other ones. I can think of how it could be done with a VB script if you exported the table to XL, and maybe the same VB technique would work directly on a table in Access but I've never programmed VB in Access so I don't know if XL's range of cell addressing and string manipulation functions are available in Access VB. None of which is any help in terms of what you can do from design view. :dead:

Share this post


Link to post
Share on other sites
Hmmm. Over my head I think. Even you you requeried the the table to collapse the records that only vary by field 14, it seems you'd then lose the data in field 14 for all the discarded redundant records. But beyond that I have no idea how to rebuild a single field in Access by merging other ones. I can think of how it could be done with a VB script if you exported the table to XL, and maybe the same VB technique would work directly on a table in Access but I've never programmed VB in Access so I don't know if XL's range of cell addressing and string manipulation functions are available in Access VB. None of which is any help in terms of what you can do from design view. :dead:

Access VBA, as far as I know, doesn't include the ability to do the offset thing where move the cursor around by X and Y, but you can read everything into a record set and address the fields using their names.

Share this post


Link to post
Share on other sites

The report's not for me and I already told him that this would happen. He's going to have to filter it anyhow. But then invariably they ask "Well can you do this...." and now I get to tell him "No... I asked around with some experts and it's beyond my knowledge". It's not my core job, just something I do to help. But like most beauracracies when Manager A tells his direct report, another manager that he needs something, they come to me and then go back to their boss "Rob's working on it..." even though I have told him it can't be done. Now it's my fault. Luckily my boss outranks him and has my back. "Do your own work"

Share this post


Link to post
Share on other sites

Anyone want to try to make an Excel random calendar? I need a tool where I can put in 2 to 10 names and have it put them on a calendar randomly each month, Monday through Friday. We are trying to make our teller audits very random each month and would love to have a 12 month schedule to insure the randomness carries though out the year.

Share this post


Link to post
Share on other sites
Anyone want to try to make an Excel random calendar? I need a tool where I can put in 2 to 10 names and have it put them on a calendar randomly each month, Monday through Friday. We are trying to make our teller audits very random each month and would love to have a 12 month schedule to insure the randomness carries though out the year.

I start with the "Randbetween" function. Put the names in a column list and use COUNT to get the number of names. Then NNames is the upper value for the RandBetween function in each day cell. Round off the number to an integer (or modulo(NNames) function would also work), each integer indexes back to one name from a lookup. Each Day cell in the month will return a random integer that will pull one name into the cell for that day.

Edited by Gehringer_2

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


Michigan Sports Betting Offer

FanDuel Sportsbook Michigan - Sports Betting is launching in Michigan shortly (December 2020 or January 2021). If you register before it launches you will recieve $50 dollars at their online sportsbook and online casino!

Click Here to claim the FanDuel Michigan for $50 at Online Sportsbook & Casino Pre-registration Bonus Now

Motown Sports Blog



×
×
  • Create New...